Authorization

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/Appendix.PostgreSQL.CommonDBATasks.Roles.rds_superuser.html
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/database-authentication.html
https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/UsingWithRDS.IAMDBAuth.html
https://www.postgresql.org/docs/current/user-manag.html
https://www.postgresql.org/docs/current/sql-createrole.html
https://www.postgresql.org/docs/current/sql-alterrole.html
https://www.postgresql.org/docs/current/sql-grant.html
https://www.postgresql.org/docs/current/sql-revoke.html
https://www.postgresql.org/docs/current/sql-alterdefaultprivileges.html
https://www.postgresql.org/docs/current/ddl-priv.html

environment:
    AWS Aurora PostgreSQL cluster identifier:
        my-pg

    database:
        my-pg-db

    application schema:
        my-pg-db-schema

goal:
    create a production-grade role model for:
        database / schema ownership
        migration DDL
        application runtime DML
        firefighter readonly incident access
        firefighter readwrite incident access
        temporary 2-hour firefighter passwords issued by Lambda

important naming note:
    my-pg-db and my-pg-db-schema contain hyphen
    PostgreSQL SQL must quote them:
        "my-pg-db"
        "my-pg-db-schema"

    recommended production naming:
        my_pg_db
        my_pg_db_schema

why add my_pg_migrator:
    direct login as object owner is convenient but risky
    better model:
        owner role owns database/schema/tables/functions/indexes
        owner role is NOLOGIN
        CI/CD login role sets role to owner during migration

why add my_pg_password_broker:
    do not give Lambda broad CREATEROLE / rds_password if it only needs firefighter passwords
    Lambda should call one restricted SECURITY DEFINER function
    function only rotates my_pg_firefighter_ro and my_pg_firefighter_rw

admin role:
    CREATEDB
    CREATEROLE
    on Aurora: rds_password when password management is required
    on standard PostgreSQL: no built-in rds_password role exists
    should not be application runtime user
    should not get rds_superuser unless there is a real DBA break-glass reason

owner role:
    owns database, schema, and objects
    can perform DDL because ownership includes ALTER / DROP
    should be NOLOGIN

migrator role:
    login role used by Flyway / Liquibase / migration job / Jenkins / GitHub Actions
    member of owner role
    runs SET ROLE my_pg_owner before DDL

app role:
    CONNECT to database
    USAGE on schema
    SELECT / INSERT / UPDATE on tables
    USAGE / SELECT on sequences
    no CREATE on schema
    no ownership
    no DELETE / DROP / ALTER / TRUNCATE / CREATE INDEX

firefighter readonly:
    temporary incident login
    SELECT only
    no data modification

firefighter readwrite:
    temporary incident login
    SELECT / INSERT / UPDATE / DELETE
    no DDL
    no TRUNCATE
    no CREATE
    no ownership

Aurora PostgreSQL is managed PostgreSQL:
    the master user is NOSUPERUSER
    AWS provides rds_superuser for many DBA tasks
    do not assume real PostgreSQL SUPERUSER exists
    grant rds_superuser only to real DBA break-glass users

rds_password:
    Aurora predefined role for password management
    grant it carefully
    prefer a restricted function for Lambda instead of granting Lambda broad password power

CONNECT:
    newer Aurora PostgreSQL versions require explicit CONNECT privilege
    even highly privileged roles should be granted CONNECT to the target database

temporary password caveat:
    VALID UNTIL expires the password
    it does not disable the role itself
    it is not enforced for non-password authentication

if using IAM DB authentication:
    grant rds_iam to the database role
    use IAM auth token instead of storing a password
    do not mix rds_iam / rds_ad / password auth casually for the same role

connect to:
    postgres database or another existing maintenance database

do not run CREATE DATABASE inside a transaction block

CREATE ROLE my_pg_admin
  LOGIN
  CREATEDB
  CREATEROLE
  INHERIT
  PASSWORD NULL;

-- Aurora only: allow password management for non-rds_superuser users.
GRANT rds_password TO my_pg_admin;

-- Standard PostgreSQL:
-- no predefined rds_password role exists.
-- Use this admin role, or a SECURITY DEFINER helper owned by a trusted DBA,
-- to run ALTER ROLE ... PASSWORD ... for the target roles it is allowed to manage.

CREATE ROLE my_pg_owner
  NOLOGIN
  INHERIT;

-- Admin must be able to SET ROLE my_pg_owner
-- so it can create a database owned by my_pg_owner.
GRANT my_pg_owner TO my_pg_admin WITH ADMIN OPTION;

password note:
    PASSWORD NULL means password login fails
    set real admin authentication through IAM DB auth or a controlled secret workflow

CREATE ROLE my_pg_migrator
  LOGIN
  INHERIT
  PASSWORD NULL;

CREATE ROLE my_pg_app
  LOGIN
  INHERIT
  CONNECTION LIMIT 100
  PASSWORD NULL;

CREATE ROLE my_pg_firefighter_ro
  LOGIN
  INHERIT
  CONNECTION LIMIT 5
  PASSWORD NULL;

CREATE ROLE my_pg_firefighter_rw
  LOGIN
  INHERIT
  CONNECTION LIMIT 3
  PASSWORD NULL;

CREATE ROLE my_pg_password_broker
  LOGIN
  INHERIT
  CONNECTION LIMIT 3
  PASSWORD NULL;

GRANT rds_iam TO my_pg_password_broker;

GRANT rds_iam TO my_pg_app;
GRANT rds_iam TO my_pg_migrator;

do not grant rds_iam to firefighter roles if your emergency workflow is password-based:
    firefighter password expiry uses VALID UNTIL
    IAM auth token does not use the PostgreSQL password

GRANT my_pg_owner TO my_pg_migrator;

migration job pattern:
    connect as my_pg_migrator
    SET ROLE my_pg_owner
    run DDL
    RESET ROLE

CREATE DATABASE "my-pg-db"
  OWNER my_pg_owner
  TEMPLATE template0
  ENCODING 'UTF8'
  CONNECTION LIMIT 200;

REVOKE ALL ON DATABASE "my-pg-db" FROM PUBLIC;

GRANT CONNECT ON DATABASE "my-pg-db" TO my_pg_admin;
GRANT CONNECT ON DATABASE "my-pg-db" TO my_pg_owner;
GRANT CONNECT ON DATABASE "my-pg-db" TO my_pg_migrator;
GRANT CONNECT ON DATABASE "my-pg-db" TO my_pg_app;
GRANT CONNECT ON DATABASE "my-pg-db" TO my_pg_firefighter_ro;
GRANT CONNECT ON DATABASE "my-pg-db" TO my_pg_firefighter_rw;
GRANT CONNECT ON DATABASE "my-pg-db" TO my_pg_password_broker;

do not grant:
    CREATE ON DATABASE to app
    CREATE ON DATABASE to firefighter roles

database CREATE privilege means:
    can create schema in this database
    can create publications
    can install trusted extensions

SET ROLE my_pg_owner;

CREATE SCHEMA "my-pg-db-schema" AUTHORIZATION my_pg_owner;

RESET ROLE;

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Optional stricter baseline when application objects do not use public schema.
REVOKE USAGE ON SCHEMA public FROM PUBLIC;

REVOKE ALL ON SCHEMA "my-pg-db-schema" FROM PUBLIC;

GRANT USAGE ON SCHEMA "my-pg-db-schema" TO my_pg_app;
GRANT USAGE ON SCHEMA "my-pg-db-schema" TO my_pg_firefighter_ro;
GRANT USAGE ON SCHEMA "my-pg-db-schema" TO my_pg_firefighter_rw;

-- Password broker does not need access to application tables.
-- It will only use db_admin schema created later.

schema rule:
    USAGE lets a role look up objects in the schema
    CREATE lets a role create objects in the schema
    application and firefighter roles should not get CREATE

GRANT SELECT, INSERT, UPDATE
ON ALL TABLES IN SCHEMA "my-pg-db-schema"
TO my_pg_app;

GRANT USAGE, SELECT
ON ALL SEQUENCES IN SCHEMA "my-pg-db-schema"
TO my_pg_app;

GRANT EXECUTE
ON ALL FUNCTIONS IN SCHEMA "my-pg-db-schema"
TO my_pg_app;

app role intentionally does not get:
    CREATE on schema
    DELETE on tables
    TRUNCATE on tables
    REFERENCES unless the app creates foreign keys at runtime
    TRIGGER
    MAINTAIN
    ownership

GRANT SELECT
ON ALL TABLES IN SCHEMA "my-pg-db-schema"
TO my_pg_firefighter_ro;

GRANT SELECT
ON ALL SEQUENCES IN SCHEMA "my-pg-db-schema"
TO my_pg_firefighter_ro;

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA "my-pg-db-schema"
TO my_pg_firefighter_rw;

GRANT USAGE, SELECT
ON ALL SEQUENCES IN SCHEMA "my-pg-db-schema"
TO my_pg_firefighter_rw;

firefighter readwrite intentionally does not get:
    TRUNCATE
    CREATE
    ALTER
    DROP
    CREATE INDEX
    ownership

reason:
    production incident data fixes should be narrow DML
    DDL changes should go through migration process

ALTER DEFAULT PRIVILEGES
FOR ROLE my_pg_owner
IN SCHEMA "my-pg-db-schema"
GRANT SELECT, INSERT, UPDATE ON TABLES TO my_pg_app;

ALTER DEFAULT PRIVILEGES
FOR ROLE my_pg_owner
IN SCHEMA "my-pg-db-schema"
GRANT USAGE, SELECT ON SEQUENCES TO my_pg_app;

ALTER DEFAULT PRIVILEGES
FOR ROLE my_pg_owner
IN SCHEMA "my-pg-db-schema"
GRANT EXECUTE ON FUNCTIONS TO my_pg_app;

ALTER DEFAULT PRIVILEGES
FOR ROLE my_pg_owner
IN SCHEMA "my-pg-db-schema"
GRANT SELECT ON TABLES TO my_pg_firefighter_ro;

ALTER DEFAULT PRIVILEGES
FOR ROLE my_pg_owner
IN SCHEMA "my-pg-db-schema"
GRANT SELECT ON SEQUENCES TO my_pg_firefighter_ro;

ALTER DEFAULT PRIVILEGES
FOR ROLE my_pg_owner
IN SCHEMA "my-pg-db-schema"
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO my_pg_firefighter_rw;

ALTER DEFAULT PRIVILEGES
FOR ROLE my_pg_owner
IN SCHEMA "my-pg-db-schema"
GRANT USAGE, SELECT ON SEQUENCES TO my_pg_firefighter_rw;

ALTER DEFAULT PRIVILEGES
FOR ROLE my_pg_owner
IN SCHEMA "my-pg-db-schema"
REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC;

important:
    default privileges are based on the actual creating role
    if migration creates objects as my_pg_migrator without SET ROLE my_pg_owner,
    these FOR ROLE my_pg_owner defaults will not apply

SELECT current_user, current_role;

SET ROLE my_pg_owner;

CREATE TABLE "my-pg-db-schema".orders (
  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_no text NOT NULL UNIQUE,
  status text NOT NULL DEFAULT 'created',
  amount numeric(12, 2) NOT NULL CHECK (amount >= 0),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX orders_status_created_at_idx
ON "my-pg-db-schema".orders (status, created_at DESC);

RESET ROLE;

SELECT
  schemaname,
  tablename,
  tableowner
FROM pg_tables
WHERE schemaname = 'my-pg-db-schema'
ORDER BY tablename;

expected:
    tableowner should be my_pg_owner

Lambda should:
    authenticate as my_pg_password_broker
    generate a random 32+ character password
    call db_admin.set_firefighter_password(...)
    return/store the password through an approved secret channel
    write audit logs:
        requester
        ticket/incident id
        target role
        expiry timestamp
        approver if required

PostgreSQL should:
    allow broker to execute only one function
    function should only rotate:
        my_pg_firefighter_ro
        my_pg_firefighter_rw
    password validity should be capped at 2 hours

CREATE SCHEMA IF NOT EXISTS db_admin AUTHORIZATION my_pg_admin;

REVOKE ALL ON SCHEMA db_admin FROM PUBLIC;

GRANT USAGE ON SCHEMA db_admin TO my_pg_password_broker;

CREATE OR REPLACE FUNCTION db_admin.set_firefighter_password(
  p_role name,
  p_password text,
  p_hours integer DEFAULT 2
)
RETURNS timestamptz
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog
AS $$
DECLARE
  v_expires_at timestamptz;
BEGIN
  IF p_role NOT IN ('my_pg_firefighter_ro', 'my_pg_firefighter_rw') THEN
    RAISE EXCEPTION 'role % is not allowed for firefighter password rotation', p_role;
  END IF;

  IF p_password IS NULL OR length(p_password) < 32 THEN
    RAISE EXCEPTION 'temporary password must be at least 32 characters';
  END IF;

  IF p_hours IS NULL OR p_hours < 1 OR p_hours > 2 THEN
    RAISE EXCEPTION 'temporary password validity must be between 1 and 2 hours';
  END IF;

  v_expires_at := clock_timestamp() + (p_hours * interval '1 hour');

  EXECUTE format(
    'ALTER ROLE %I PASSWORD %L VALID UNTIL %L',
    p_role,
    p_password,
    v_expires_at
  );

  RETURN v_expires_at;
END;
$$;

ALTER FUNCTION db_admin.set_firefighter_password(name, text, integer)
OWNER TO my_pg_admin;

REVOKE ALL ON FUNCTION db_admin.set_firefighter_password(name, text, integer) FROM PUBLIC;

GRANT EXECUTE
ON FUNCTION db_admin.set_firefighter_password(name, text, integer)
TO my_pg_password_broker;

security definer rules:
    always set search_path
    do not use unqualified application objects
    validate target role allowlist
    validate max validity
    keep function owner highly controlled

SELECT db_admin.set_firefighter_password(
  'my_pg_firefighter_ro',
  'replace-with-lambda-generated-32-plus-char-password',
  2
) AS expires_at;

SELECT db_admin.set_firefighter_password(
  'my_pg_firefighter_rw',
  'replace-with-lambda-generated-32-plus-char-password',
  2
) AS expires_at;

ALTER ROLE my_pg_firefighter_ro PASSWORD NULL VALID UNTIL '1970-01-01 00:00:00+00';

ALTER ROLE my_pg_firefighter_rw PASSWORD NULL VALID UNTIL '1970-01-01 00:00:00+00';

operational note:
    password expiry blocks new password logins
    it does not kill existing sessions
    terminate active firefighter sessions separately if needed

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename IN ('my_pg_firefighter_ro', 'my_pg_firefighter_rw')
  AND pid <> pg_backend_pid();

ALTER ROLE my_pg_firefighter_ro IN DATABASE "my-pg-db"
  SET statement_timeout = '5min';

ALTER ROLE my_pg_firefighter_ro IN DATABASE "my-pg-db"
  SET idle_in_transaction_session_timeout = '2min';

ALTER ROLE my_pg_firefighter_rw IN DATABASE "my-pg-db"
  SET statement_timeout = '5min';

ALTER ROLE my_pg_firefighter_rw IN DATABASE "my-pg-db"
  SET lock_timeout = '5s';

ALTER ROLE my_pg_firefighter_rw IN DATABASE "my-pg-db"
  SET idle_in_transaction_session_timeout = '2min';

ALTER ROLE my_pg_app IN DATABASE "my-pg-db"
  SET search_path = '"my-pg-db-schema"', public;

ALTER ROLE my_pg_migrator IN DATABASE "my-pg-db"
  SET search_path = '"my-pg-db-schema"', public;

application recommendation:
    still qualify production SQL with schema name when possible
    search_path is convenience, not a security boundary

SELECT
  rolname,
  rolcanlogin,
  rolcreatedb,
  rolcreaterole,
  rolreplication,
  rolbypassrls,
  rolconnlimit,
  rolvaliduntil
FROM pg_roles
WHERE rolname LIKE 'my_pg_%'
ORDER BY rolname;

SELECT
  role_name,
  has_database_privilege(role_name, 'my-pg-db', 'CONNECT') AS can_connect,
  has_database_privilege(role_name, 'my-pg-db', 'CREATE') AS can_create_in_database
FROM (
  VALUES
    ('my_pg_admin'),
    ('my_pg_owner'),
    ('my_pg_migrator'),
    ('my_pg_app'),
    ('my_pg_firefighter_ro'),
    ('my_pg_firefighter_rw'),
    ('my_pg_password_broker')
) AS r(role_name)
ORDER BY role_name;

can_connect:
    true for all roles that need to connect

can_create_in_database:
    false for app / firefighter / password_broker

SELECT
  role_name,
  has_schema_privilege(role_name, '"my-pg-db-schema"', 'USAGE') AS schema_usage,
  has_schema_privilege(role_name, '"my-pg-db-schema"', 'CREATE') AS schema_create
FROM (
  VALUES
    ('my_pg_app'),
    ('my_pg_firefighter_ro'),
    ('my_pg_firefighter_rw'),
    ('my_pg_password_broker')
) AS r(role_name)
ORDER BY role_name;

my_pg_app:
    schema_usage = true
    schema_create = false

my_pg_firefighter_ro:
    schema_usage = true
    schema_create = false

my_pg_firefighter_rw:
    schema_usage = true
    schema_create = false

my_pg_password_broker:
    schema_usage = false
    schema_create = false

SELECT
  grantee,
  table_schema,
  table_name,
  privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'my-pg-db-schema'
  AND grantee IN (
    'my_pg_app',
    'my_pg_firefighter_ro',
    'my_pg_firefighter_rw'
  )
ORDER BY grantee, table_name, privilege_type;

SELECT
  defaclrole::regrole AS owner_role,
  defaclnamespace::regnamespace AS schema_name,
  defaclobjtype AS object_type,
  defaclacl AS acl
FROM pg_default_acl
WHERE defaclrole = 'my_pg_owner'::regrole
ORDER BY object_type;

CREATE TABLE "my-pg-db-schema".should_fail (id bigint);

permission denied for schema my-pg-db-schema

DELETE FROM "my-pg-db-schema".orders
WHERE id = 1;

permission denied for table orders

UPDATE "my-pg-db-schema".orders
SET status = 'cancelled'
WHERE id = 1;

permission denied for table orders

DROP TABLE "my-pg-db-schema".orders;

must be owner of table orders

admin role creates a new database
admin role creates a new owner role
admin grants itself membership in owner role
admin creates database owned by that owner role
admin connects to new database
admin creates schema owned by owner role
admin grants app / firefighter privileges

CREATE ROLE other_pg_owner NOLOGIN INHERIT;

GRANT other_pg_owner TO my_pg_admin WITH ADMIN OPTION;

CREATE DATABASE other_pg_db
  OWNER other_pg_owner
  TEMPLATE template0
  ENCODING 'UTF8';

SET ROLE other_pg_owner;

CREATE SCHEMA app AUTHORIZATION other_pg_owner;

RESET ROLE;

why membership matters:
    to create a database owned by another role,
    the executing role must be able to SET ROLE to that owner role

readonly firefighter:
    use first for diagnosis
    SELECT only
    no data changes

readwrite firefighter:
    use only after approved incident/ticket
    write exact SQL before execution
    run SELECT preview first
    run DML in transaction
    use RETURNING where possible
    record changed primary keys

DDL:
    do not use firefighter role for DDL
    use migration process with my_pg_migrator -> SET ROLE my_pg_owner

BEGIN;

SELECT id, order_no, status
FROM "my-pg-db-schema".orders
WHERE order_no = 'ORD-1001';

UPDATE "my-pg-db-schema".orders
SET status = 'cancelled',
    updated_at = now()
WHERE order_no = 'ORD-1001'
  AND status = 'created'
RETURNING id, order_no, status, updated_at;

COMMIT;

ROLLBACK;

role design:
    owner is NOLOGIN
    app is not owner
    firefighter roles are not owner
    Lambda broker is not admin

database / schema:
    PUBLIC database privileges reviewed
    public schema CREATE revoked
    app schema CREATE not granted to runtime roles

existing objects:
    app has SELECT/INSERT/UPDATE only
    firefighter_ro has SELECT only
    firefighter_rw has DML only
    sequences granted separately

future objects:
    ALTER DEFAULT PRIVILEGES configured for my_pg_owner
    migration job uses SET ROLE my_pg_owner

temporary access:
    firefighter passwords default to NULL or expired
    Lambda generated passwords are 32+ characters
    validity capped at 2 hours
    active sessions terminated after incident if needed

audit:
    log Lambda caller, target role, expiry, incident id
    enable database connection logs where appropriate
    keep CloudTrail / Lambda logs / database logs retained by policy