Read-only access to a schema

1-- Connect to the database that holds the schema, as its owner or a superuser.2-- Replace <db_name>, <schema>, <owner_role> (the role that creates the tables)3-- and <user_name>. Grants apply to the database you're connected to only.4 5-- 1. A group role that can't log in, holding the privileges6CREATE ROLE app_read NOLOGIN;7 8GRANT CONNECT ON DATABASE <db_name> TO app_read;9GRANT USAGE ON SCHEMA <schema> TO app_read;10 11-- 2. Existing tables, views and sequences12GRANT SELECT ON ALL TABLES IN SCHEMA <schema> TO app_read;13GRANT SELECT ON ALL SEQUENCES IN SCHEMA <schema> TO app_read;14 15-- 3. Tables created later. This only covers objects created by <owner_role>,16--    so name the role that actually creates them (often the app's migration user).17ALTER DEFAULT PRIVILEGES FOR ROLE <owner_role> IN SCHEMA <schema>18    GRANT SELECT ON TABLES TO app_read;19ALTER DEFAULT PRIVILEGES FOR ROLE <owner_role> IN SCHEMA <schema>20    GRANT SELECT ON SEQUENCES TO app_read;21 22-- 4. The person or application that logs in23CREATE ROLE <user_name> LOGIN PASSWORD 'change-me';24GRANT app_read TO <user_name>;25 26-- To read every table in every schema of the database instead (PostgreSQL 14+):27-- GRANT pg_read_all_data TO <user_name>;