Least-privilege Postgres roles for an app, the secure way

-- roles.sql: least-privilege roles for one application database. Run as a superuser.
-- Passwords here are test values; in production set them from a secret store.

-- 1. Nobody gets anything by default.
CREATE DATABASE app;
REVOKE ALL ON DATABASE app FROM PUBLIC;
-- Every role can connect to "postgres" and "template1" by default. Close that too.
REVOKE CONNECT ON DATABASE postgres FROM PUBLIC;
REVOKE CONNECT ON DATABASE template1 FROM PUBLIC;

-- 2. The owner: owns the schema and every table. Cannot log in.
CREATE ROLE app_owner NOLOGIN;
-- 3. Group roles that carry privileges. Cannot log in.
CREATE ROLE app_rw NOLOGIN;
CREATE ROLE app_ro NOLOGIN;
-- 4. Login roles: one per client, member of exactly one group.
CREATE ROLE app_migrator LOGIN PASSWORD 'test-migrator-pw' IN ROLE app_owner;
CREATE ROLE app_user     LOGIN PASSWORD 'test-user-pw'     IN ROLE app_rw CONNECTION LIMIT 50;
CREATE ROLE report_user  LOGIN PASSWORD 'test-report-pw'   IN ROLE app_ro CONNECTION LIMIT 5;
-- The app user cannot run away with the server.
ALTER ROLE app_user SET statement_timeout = '5s';
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '30s';

GRANT CONNECT ON DATABASE app TO app_owner, app_rw, app_ro;

\connect app
-- 5. One schema, owned by app_owner. Nobody else may create objects in it.
REVOKE ALL ON SCHEMA public FROM PUBLIC;
CREATE SCHEMA app AUTHORIZATION app_owner;
GRANT USAGE ON SCHEMA app TO app_rw, app_ro;
ALTER ROLE app_user    IN DATABASE app SET search_path = app;
ALTER ROLE report_user IN DATABASE app SET search_path = app;

-- 6. Privileges on tables created LATER by the owner (migrations).
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT USAGE, SELECT ON SEQUENCES TO app_rw;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO app_ro;

-- A migration, run by app_migrator as the owner role.
SET ROLE app_owner;
CREATE TABLE app.customers (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, email text NOT NULL, note text);
CREATE TABLE app.invoices  (id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY, customer_id bigint REFERENCES app.customers, amount numeric NOT NULL);
INSERT INTO app.customers (email) VALUES ('[email protected]'), ('[email protected]');

psql -U postgres -d app -c '\du app_*' -c '\du report_user'

         List of roles
  Role name   |   Attributes   
--------------+----------------
 app_migrator | 
 app_owner    | Cannot login
 app_ro       | Cannot login
 app_rw       | Cannot login
 app_user     | 50 connections

        List of roles
  Role name  |  Attributes   
-------------+---------------
 report_user | 5 connections

psql -U postgres -d app -c '\dp app.*'

                                         Access privileges
 Schema |       Name       |   Type   |      Access privileges       | Column privileges | Policies 
--------+------------------+----------+------------------------------+-------------------+----------
 app    | customers        | table    | app_owner=arwdDxtm/app_owner+|                   | 
        |                  |          | app_rw=arwd/app_owner       +|                   | 
        |                  |          | app_ro=r/app_owner           |                   | 
 app    | customers_id_seq | sequence | app_owner=rwU/app_owner     +|                   | 
        |                  |          | app_rw=rU/app_owner          |                   | 
 app    | invoices         | table    | app_owner=arwdDxtm/app_owner+|                   | 
        |                  |          | app_rw=arwd/app_owner       +|                   | 
        |                  |          | app_ro=r/app_owner           |                   | 
 app    | invoices_id_seq  | sequence | app_owner=rwU/app_owner     +|                   | 
        |                  |          | app_rw=rU/app_owner          |                   | 
(4 rows)

psql -h 127.0.0.1 -U app_user -d app -c "show search_path;" -c "insert into customers (email) values ('[email protected]');" -c "select count(*) from customers;"

 search_path 
-------------
 app
(1 row)

 count 
-------
     3
(1 row)

psql -h 127.0.0.1 -U app_user -d app -c "drop table customers;"
psql -h 127.0.0.1 -U app_user -d app -c "create table evil (x int);"
psql -h 127.0.0.1 -U app_user -d app -c "alter table customers add column is_admin bool;"
psql -h 127.0.0.1 -U app_user -d app -c "create table public.evil (x int);"
psql -h 127.0.0.1 -U app_user -d app -c "copy customers to program 'id';"
psql -h 127.0.0.1 -U app_user -d app -c "select pg_sleep(10);"
psql -h 127.0.0.1 -U app_user -d postgres -c "select 1"

ERROR:  must be owner of table customers
ERROR:  permission denied for schema app
LINE 1: create table evil (x int);
                     ^
ERROR:  must be owner of table customers
ERROR:  permission denied for schema public
LINE 1: create table public.evil (x int);
                     ^
ERROR:  permission denied to COPY to or from an external program
DETAIL:  Only roles with privileges of the "pg_execute_server_program" role may COPY to or from an external program.
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.
ERROR:  canceling statement due to statement timeout
psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  permission denied for database "postgres"
DETAIL:  User does not have CONNECT privilege.

 [email protected]

ERROR:  permission denied for table customers

psql -h 127.0.0.1 -U app_migrator -d app -c "create table app.audit (x int);"
psql -h 127.0.0.1 -U app_user -d app -c "select * from audit;"
psql -U postgres -d app -c "select tablename, tableowner from pg_tables where schemaname='app' order by 1;"

ERROR:  permission denied for table audit
 tablename |  tableowner  
-----------+--------------
 audit     | app_migrator
 customers | app_owner
 invoices  | app_owner
(3 rows)