Read-write 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>.4 5CREATE ROLE app_write NOLOGIN;6 7GRANT CONNECT ON DATABASE <db_name> TO app_write;8GRANT USAGE ON SCHEMA <schema> TO app_write;9 10-- Existing objects. Sequences need USAGE for inserts that use serial or identity columns.11GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA <schema> TO app_write;12GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA <schema> TO app_write;13 14-- Objects created later by <owner_role>15ALTER DEFAULT PRIVILEGES FOR ROLE <owner_role> IN SCHEMA <schema>16    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_write;17ALTER DEFAULT PRIVILEGES FOR ROLE <owner_role> IN SCHEMA <schema>18    GRANT USAGE, SELECT ON SEQUENCES TO app_write;19 20-- The login user21CREATE ROLE <user_name> LOGIN PASSWORD 'change-me';22GRANT app_write TO <user_name>;23 24-- To let the role also create tables in the schema (a migration user, not the app):25-- GRANT CREATE ON SCHEMA <schema> TO <user_name>;26 27-- To take access away again:28-- REVOKE app_write FROM <user_name>;