Roles and Permissions

CREATE ROLE app_user LOGIN PASSWORD 'secret'
  CONNECTION LIMIT 20
  VALID UNTIL '2027-01-01';

CREATE ROLE readonly NOLOGIN;                  -- a group, nothing connects as it
CREATE ROLE app_admin LOGIN PASSWORD 'x' CREATEDB CREATEROLE;

ALTER ROLE app_user PASSWORD 'newsecret';
ALTER ROLE app_user WITH PASSWORD NULL;        -- remove the password, cannot log in
ALTER ROLE app_user RENAME TO api_user;        -- clears an md5 password, reset it
ALTER ROLE app_user SET statement_timeout = '10s';   -- per-role default GUC
DROP ROLE app_user;                            -- fails while it owns anything

SELECT rolname, rolsuper, rolcreatedb, rolcreaterole,
       rolcanlogin, rolreplication, rolbypassrls, rolconnlimit
FROM pg_roles ORDER BY rolname;

REASSIGN OWNED BY old_role TO new_role;
DROP OWNED BY old_role;      -- drops the rest, and its privileges, per database
DROP ROLE old_role;

GRANT readonly TO app_user;                       -- app_user gains readonly's rights
GRANT readonly TO lead WITH ADMIN OPTION;         -- lead may grant readonly onward
GRANT readonly TO app_user WITH INHERIT FALSE;    -- must SET ROLE to use it (16+)
REVOKE readonly FROM app_user;

SET ROLE readonly;      -- switch for this session
RESET ROLE;
SELECT current_user, session_user;   -- current_user changes, session_user does not

GRANT SELECT, INSERT, UPDATE ON orders TO app_user;
GRANT ALL PRIVILEGES ON orders TO app_owner;
GRANT SELECT (id, total) ON orders TO analyst;          -- column level
GRANT USAGE, SELECT ON SEQUENCE orders_id_seq TO app_user;
GRANT USAGE ON SCHEMA app TO app_user;
GRANT CONNECT ON DATABASE prod TO app_user;
GRANT EXECUTE ON FUNCTION recalc(bigint) TO app_user;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly WITH GRANT OPTION;

REVOKE INSERT, UPDATE, DELETE ON orders FROM app_user;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

-- Everything I create in public from now on is readable by readonly
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO readonly;

-- Same, but for objects created by the migration role, not by me
ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA public
  GRANT SELECT ON TABLES TO readonly;

ALTER DEFAULT PRIVILEGES FOR ROLE migrator IN SCHEMA public
  REVOKE ALL ON FUNCTIONS FROM PUBLIC;

SELECT defaclrole::regrole AS for_role, defaclnamespace::regnamespace AS schema,
       defaclobjtype, defaclacl
FROM pg_default_acl;

REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON DATABASE prod FROM PUBLIC;   -- PUBLIC still gets CONNECT and TEMP by default

CREATE ROLE readonly NOLOGIN;
CREATE ROLE analyst LOGIN PASSWORD 'x' IN ROLE readonly;

GRANT CONNECT ON DATABASE prod TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO readonly;

ALTER ROLE analyst SET statement_timeout = '60s';
ALTER ROLE analyst SET default_transaction_read_only = on;

GRANT pg_read_all_data TO readonly;

SELECT has_table_privilege('analyst', 'orders', 'SELECT')  AS can_read,
       has_table_privilege('analyst', 'orders', 'INSERT')  AS can_write,
       has_schema_privilege('analyst', 'public', 'USAGE')  AS can_use_schema;

SELECT grantee, privilege_type
FROM information_schema.table_privileges
WHERE table_schema = 'public' AND table_name = 'orders';

CREATE FUNCTION archive_old_orders() RETURNS bigint
LANGUAGE sql
SECURITY DEFINER
SET search_path = pg_catalog, pg_temp
AS $$
  WITH moved AS (
    DELETE FROM public.orders WHERE placed_at < now() - interval '2 years'
    RETURNING *
  )
  INSERT INTO public.orders_archive SELECT * FROM moved RETURNING 1;
$$;

REVOKE EXECUTE ON FUNCTION archive_old_orders() FROM PUBLIC;
GRANT  EXECUTE ON FUNCTION archive_old_orders() TO app_user;

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents FORCE ROW LEVEL SECURITY;   -- apply to the owner too

-- Multi-tenant isolation driven by a session variable
CREATE POLICY tenant_isolation ON documents
  FOR ALL
  USING      (tenant_id = current_setting('app.tenant_id', true)::bigint)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::bigint);

-- Owners write, everyone in the tenant reads
CREATE POLICY doc_read  ON documents FOR SELECT TO app_user USING (true);
CREATE POLICY doc_write ON documents FOR UPDATE TO app_user
  USING (owner_id = current_setting('app.user_id', true)::bigint);

-- A hard floor no permissive policy can widen
CREATE POLICY no_secrets ON documents AS RESTRICTIVE FOR SELECT
  USING (classification <> 'secret');

BEGIN;
SELECT set_config('app.tenant_id', $1, true);   -- true = local to this transaction
SELECT * FROM documents;                        -- policy applies automatically
COMMIT;

-- Inspect what is in force
SELECT polname, polcmd, polpermissive,
       pg_get_expr(polqual, polrelid)      AS using_expr,
       pg_get_expr(polwithcheck, polrelid) AS check_expr
FROM pg_policy WHERE polrelid = 'documents'::regclass;

# TYPE  DATABASE  USER      ADDRESS          METHOD
local   all       postgres                   peer
local   all       all                        scram-sha-256
host    prod      app_user  10.0.0.0/8       scram-sha-256
hostssl prod      analyst   0.0.0.0/0        scram-sha-256  clientcert=verify-full
host    replication repl    10.0.0.5/32      scram-sha-256
host    all       all       0.0.0.0/0        reject

SELECT pg_reload_conf();
SELECT type, database, user_name, address, auth_method, error
FROM pg_hba_file_rules ORDER BY rule_number;   -- shows parse errors too
SELECT name, setting FROM pg_settings
WHERE name IN ('hba_file', 'config_file', 'password_encryption', 'ssl');

# A connection URI, and sslmode is the flag that matters
psql "postgres://app_user@db.example.com:5432/prod?sslmode=verify-full"

# ~/.pgpass, mode 0600, one line per target
# hostname:port:database:username:password
echo 'db.example.com:5432:prod:app_user:secret' >> ~/.pgpass
chmod 600 ~/.pgpass