Users

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-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

PostgreSQL role:
    user and group are both roles
    LOGIN role can connect as a user
    NOLOGIN role is usually used as a group/owner role

least privilege:
    app user should not be database owner
    app user should not be superuser
    migrator owns schema objects
    runtime app role gets DML only
    readonly role gets SELECT only
    PUBLIC privileges should be reviewed

default access:
    newly created role does not automatically get SELECT on user tables
    role may still see catalog metadata such as database/schema/table names
    some default privileges come from PUBLIC and should be reviewed

recommended role model:
    dba_order:
        database owner / admin role

    order_migrator:
        creates schema, tables, indexes, functions

    order_app:
        runtime application user

    order_readonly:
        BI / support / dashboard read-only user

name mapping used in this page:
    orders:
        database name

    app:
        schema name inside orders database

    dba_order:
        owner/admin role for orders database

    order_migrator:
        role used by migration tool or CI job
        creates tables/indexes/functions in schema app

    order_app:
        role used by application runtime
        reads/writes business data
        should not create/drop schema objects

    order_readonly:
        role used by read-only dashboards/support/BI
        can read data but cannot write data

rule:
    owner/admin role can be NOLOGIN if humans assume another admin login role
    application role should be LOGIN but should not own objects
    migration role can own schema objects because migrations create them

connect to database:
    controlled by database CONNECT privilege
    many PostgreSQL databases grant CONNECT to PUBLIC by default

see metadata:
    PostgreSQL catalogs expose object metadata
    user may see database/schema/table names in catalogs
    seeing a table name does not mean SELECT is allowed

read table data:
    requires schema USAGE
    and table SELECT
    or ownership / superuser / inherited privileges

important:
    can connect != can read table
    can see table name != can read table data
    can use schema != can select table

CREATE ROLE test_no_grants LOGIN PASSWORD 'change-me';

psql "dbname=orders user=test_no_grants"

SELECT
  datname,
  datacl
FROM pg_database
WHERE datname = 'orders';

SELECT *
FROM app.orders
LIMIT 1;

permission denied for schema app
or
permission denied for table orders

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema = 'app';

metadata note:
    result may show object names depending on catalog visibility and privileges
    this is not the same as SELECT access to table data

public schema:
    schema name is public
    it exists inside each database
    orders.public and postgres.public are different schemas in different databases

default privileges:
    many PostgreSQL setups allow PUBLIC to use public schema
    older PostgreSQL / upgraded clusters may also allow PUBLIC to create in public schema
    PostgreSQL 15+ default setup is stricter for CREATE in public schema

important:
    USAGE on schema public does not mean SELECT on all tables in public
    CREATE on schema public means role can create objects there
    SELECT on a table still needs table-level SELECT privilege

SELECT
  nspname AS schema_name,
  nspacl AS privileges
FROM pg_namespace
WHERE nspname = 'public';

-- Remove object creation in public schema for every role.
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- Optional stricter baseline:
-- remove schema usage from every role, then grant only what is needed.
REVOKE USAGE ON SCHEMA public FROM PUBLIC;

when to revoke USAGE on public:
    use this when application objects live in app schema
    and you do not want roles to reference objects in public schema

warning:
    extensions or legacy objects may live in public
    test before revoking USAGE in an existing database

-- Remove default connection access for all roles.
REVOKE ALL ON DATABASE orders FROM PUBLIC;

-- Remove default object creation in public schema.
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

after baseline:
    grant CONNECT only to roles that need this database
    grant schema USAGE only to roles that need this schema
    grant table SELECT/DML only to roles that need data access

cannot do:
    REVOKE SELECT ON other_database.app.orders FROM order_app

must do:
    psql -d other_database
    REVOKE ... ON SCHEMA/TABLE ... FROM order_app

-- Prevent order_app from connecting to analytics database.
REVOKE CONNECT ON DATABASE analytics FROM order_app;

-- If CONNECT came from PUBLIC, revoke it from PUBLIC too.
REVOKE CONNECT ON DATABASE analytics FROM PUBLIC;

meaning:
    without CONNECT, the role cannot connect to that database
    this is the cleanest database-level boundary

psql -d analytics

-- Remove ability to reference objects in schema app.
REVOKE USAGE ON SCHEMA app FROM order_app;

-- Remove ability to create objects in schema app.
REVOKE CREATE ON SCHEMA app FROM order_app;

-- Remove PUBLIC schema access if the role inherits it through PUBLIC.
REVOKE USAGE ON SCHEMA app FROM PUBLIC;
REVOKE CREATE ON SCHEMA app FROM PUBLIC;

meaning:
    schema USAGE is required before table privileges are useful
    revoking schema USAGE blocks normal access to objects in that schema
    table grants may still exist, but role cannot use them without schema USAGE

-- Existing tables only.
REVOKE ALL PRIVILEGES
ON ALL TABLES IN SCHEMA app
FROM order_app;

-- Existing sequences only.
REVOKE ALL PRIVILEGES
ON ALL SEQUENCES IN SCHEMA app
FROM order_app;

-- Existing functions only.
REVOKE ALL PRIVILEGES
ON ALL FUNCTIONS IN SCHEMA app
FROM order_app;

ALTER DEFAULT PRIVILEGES
FOR ROLE order_migrator
IN SCHEMA app
REVOKE ALL PRIVILEGES
ON TABLES
FROM order_app;

ALTER DEFAULT PRIVILEGES
FOR ROLE order_migrator
IN SCHEMA app
REVOKE ALL PRIVILEGES
ON SEQUENCES
FROM order_app;

ALTER DEFAULT PRIVILEGES
FOR ROLE order_migrator
IN SCHEMA app
REVOKE ALL PRIVILEGES
ON FUNCTIONS
FROM order_app;

important:
    REVOKE ON ALL TABLES affects existing tables only
    ALTER DEFAULT PRIVILEGES affects future objects only
    if privileges come from another group role, revoke from that group role too
    owner and superuser privileges cannot be removed by normal REVOKE

psql "dbname=analytics user=order_app"

permission denied for database analytics

SELECT *
FROM app.orders
LIMIT 1;

permission denied for schema app
or
permission denied for table orders

-- dba_order represents database owner / DBA responsibility.
-- NOLOGIN means nobody can directly connect as this role.
CREATE ROLE dba_order NOLOGIN;

-- order_migrator represents migration pipeline or schema migration user.
-- It can login because migration tools need to connect as this role.
CREATE ROLE order_migrator
  LOGIN
  PASSWORD 'change-me-migrator'
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION;

-- order_app represents the application runtime database user.
-- It should be able to read/write app data, but not own database objects.
CREATE ROLE order_app
  LOGIN
  PASSWORD 'change-me-app'
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION;

-- order_readonly represents read-only dashboard/support/BI access.
-- It should only receive SELECT privileges.
CREATE ROLE order_readonly
  LOGIN
  PASSWORD 'change-me-readonly'
  NOSUPERUSER
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION;

-- orders is the application database.
-- dba_order owns the database, not order_app.
CREATE DATABASE orders
  OWNER dba_order
  ENCODING 'UTF8'
  CONNECTION LIMIT 200;

notes:
    use password manager / secret manager for real password
    do not put production password in migration SQL committed to Git
    managed PostgreSQL may restrict superuser operations

psql -d orders

SELECT current_database();

database context:
    PostgreSQL connection targets one database at a time
    schema names are resolved inside the current database

    GRANT USAGE ON SCHEMA app TO order_readonly:
        means schema app in the currently connected database
        if current_database() = orders, it grants access to orders.app

    PostgreSQL does not use:
        GRANT ... ON SCHEMA orders.app

    if you need to grant privileges in another database:
        connect to that database first

-- PUBLIC means every role.
-- Remove default database-level privileges from all roles.
REVOKE ALL ON DATABASE orders FROM PUBLIC;

-- public is PostgreSQL's default schema.
-- Remove CREATE so random roles cannot create objects in public schema.
REVOKE CREATE ON SCHEMA public FROM PUBLIC;

-- order_migrator must connect to run migrations.
GRANT CONNECT ON DATABASE orders TO order_migrator;

-- order_app must connect when the application starts.
GRANT CONNECT ON DATABASE orders TO order_app;

-- order_readonly must connect for dashboard/support queries.
GRANT CONNECT ON DATABASE orders TO order_readonly;

why:
    database CONNECT controls whether the role can connect to the database
    schema/table grants still need to be configured separately

-- app is the application schema.
-- order_migrator owns this schema because migrations create objects in it.
CREATE SCHEMA app AUTHORIZATION order_migrator;

-- order_migrator can use schema app and create tables/indexes/functions in it.
-- This affects schema app inside the current database, expected: orders.
GRANT USAGE, CREATE ON SCHEMA app TO order_migrator;

-- order_app can reference objects in schema app.
-- This does not grant table SELECT/INSERT/UPDATE/DELETE by itself.
-- This affects schema app inside the current database, expected: orders.
GRANT USAGE ON SCHEMA app TO order_app;

-- order_readonly can reference objects in schema app.
-- This does not grant table SELECT by itself.
-- This affects schema app inside the current database, expected: orders.
GRANT USAGE ON SCHEMA app TO order_readonly;

schema privileges:
    database context:
        schema app means current_database().app

    USAGE:
        can access objects in schema if object privileges also allow it

    CREATE:
        can create objects in schema

runtime rule:
    order_app usually needs USAGE only
    order_app should not need CREATE in production schema

-- Existing tables only:
-- order_app can read and write all current tables in schema app.
GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA app
TO order_app;

-- Existing tables only:
-- order_readonly can read all current tables in schema app.
GRANT SELECT
ON ALL TABLES IN SCHEMA app
TO order_readonly;

-- Existing sequences only:
-- order_app can use sequences for inserts into serial/identity columns.
GRANT USAGE, SELECT
ON ALL SEQUENCES IN SCHEMA app
TO order_app;

-- Existing sequences only:
-- order_readonly can inspect sequence values if needed.
GRANT SELECT
ON ALL SEQUENCES IN SCHEMA app
TO order_readonly;

sequence notes:
    INSERT into table with serial/identity may need sequence usage
    readonly usually does not need sequence USAGE

-- Future tables created by order_migrator in schema app:
-- automatically grant read/write privileges to order_app.
ALTER DEFAULT PRIVILEGES
FOR ROLE order_migrator
IN SCHEMA app
GRANT SELECT, INSERT, UPDATE, DELETE
ON TABLES
TO order_app;

-- Future tables created by order_migrator in schema app:
-- automatically grant read-only access to order_readonly.
ALTER DEFAULT PRIVILEGES
FOR ROLE order_migrator
IN SCHEMA app
GRANT SELECT
ON TABLES
TO order_readonly;

-- Future sequences created by order_migrator in schema app:
-- automatically allow order_app to use them for inserts.
ALTER DEFAULT PRIVILEGES
FOR ROLE order_migrator
IN SCHEMA app
GRANT USAGE, SELECT
ON SEQUENCES
TO order_app;

-- Future sequences created by order_migrator in schema app:
-- automatically allow order_readonly to inspect them.
ALTER DEFAULT PRIVILEGES
FOR ROLE order_migrator
IN SCHEMA app
GRANT SELECT
ON SEQUENCES
TO order_readonly;

-- Future functions created by order_migrator in schema app:
-- automatically allow order_app to execute them.
ALTER DEFAULT PRIVILEGES
FOR ROLE order_migrator
IN SCHEMA app
GRANT EXECUTE
ON FUNCTIONS
TO order_app;

important:
    default privileges are tied to object creator role
    if another role creates tables, these defaults may not apply
    keep migrations using one migrator role

how to read ALTER DEFAULT PRIVILEGES:
    FOR ROLE order_migrator:
        apply to future objects created by order_migrator

    IN SCHEMA app:
        only future objects created in schema app

    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES:
        privilege type for future tables

    TO order_app:
        recipient role that receives the privilege

    key point:
        this does not change existing tables
        it changes default grants for future tables

-- Run this as order_migrator.
-- order_migrator will own the table and index.
CREATE TABLE app.orders (
  id bigserial PRIMARY KEY,
  user_id text NOT NULL,
  amount numeric(12, 2) NOT NULL,
  status text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- Run this as order_migrator.
-- Index is created by the migration role, not by application runtime role.
CREATE INDEX orders_user_created_idx
  ON app.orders (user_id, created_at DESC);

psql "dbname=orders user=order_app"

-- order_app should be allowed to insert because it has INSERT on app.orders.
INSERT INTO app.orders (user_id, amount, status)
VALUES ('user_1', 10.00, 'created');

-- order_app should be allowed to select because it has SELECT on app.orders.
SELECT id, user_id, amount, status
FROM app.orders;

psql "dbname=orders user=order_readonly"

-- order_readonly should be allowed to select.
SELECT id, user_id, amount, status
FROM app.orders;

-- order_readonly should fail here because it does not have INSERT.
INSERT INTO app.orders (user_id, amount, status)
VALUES ('user_2', 20.00, 'created');

SELECT current_user, current_database();

\du

SELECT
  datname,
  pg_catalog.pg_get_userbyid(datdba) AS owner,
  datacl
FROM pg_database
WHERE datname = 'orders';

SELECT
  grantee,
  table_schema,
  table_name,
  privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'app'
ORDER BY table_name, grantee, privilege_type;

SELECT
  defaclrole::regrole AS owner_role,
  defaclnamespace::regnamespace AS schema_name,
  defaclobjtype AS object_type,
  defaclacl AS privileges
FROM pg_default_acl;

-- Existing tables only:
-- remove SELECT from order_readonly on current tables.
REVOKE SELECT
ON ALL TABLES IN SCHEMA app
FROM order_readonly;

-- Future tables:
-- stop automatically granting SELECT to order_readonly
-- for tables created by order_migrator in schema app.
ALTER DEFAULT PRIVILEGES
FOR ROLE order_migrator
IN SCHEMA app
REVOKE SELECT
ON TABLES
FROM order_readonly;

-- Keep the role but prevent new login sessions.
ALTER ROLE order_readonly NOLOGIN;

-- Change password for order_app.
-- Update application secret store before or during this rotation.
ALTER ROLE order_app PASSWORD 'new-secret-from-secret-manager';

incident rule:
    disable login first when responding to suspected credential leak
    rotate password after app secret store is ready
    verify active sessions and terminate if needed

-- Terminate existing sessions for leaked/rotated application credential.
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'order_app'
  AND pid <> pg_backend_pid();