txt-game/migrations/provision/postgres/001-grant-permissions.ts

import pg from "pg";

const client = new pg.Client({
  host: process.env.PROVISION_HOST,
  port: parseInt(process.env.PROVISION_PORT ?? "5432"),
  user: process.env.PROVISION_USER,
  password: process.env.PROVISION_PASSWORD,
  database: process.env.PROVISION_DATABASE,
});

await client.connect();

try {
  // The HAL postgres provisioner names the app user identically to the database.
  // PROVISION_DATABASE = "txt_game_scores" = the app user that server.mjs connects as.
  const appUser = process.env.PROVISION_DATABASE as string;

  await client.query(
    `GRANT SELECT, INSERT, UPDATE ON TABLE players, games TO "${appUser}"`
  );

  await client.query(
    `GRANT USAGE, SELECT ON SEQUENCE games_id_seq TO "${appUser}"`
  );

  console.log(`[txt-game migration-001] permissions granted to ${appUser}`);
} finally {
  await client.end();
}