Create Postgres Roles

README.md

Create Postgres Roles

read_access

Read all data (tables, views, sequences), as if having SELECT rights on those objects, and USAGE rights on all user-created schemas.

read_access.sql

1. Replace db_name with the relevant database name.
2. Run the Database section of the script.
3. Run the Schemas section of the script to generate a table of SQL strings to execute. Execute the SQL strings.
4. Run the Tables section of the script to generate a table of SQL strings to execute. Execute the SQL strings.
5. Run the Sequences section of the script to generate a table of SQL strings to execute. Execute the SQL strings.
6. GRANT this role to database users as needed.

write_access

Write all data (tables, views, sequences), as if having INSERT, UPDATE, and DELETE rights on those objects, and USAGE rights on all user-created schemas.

write_access.sql

1. Replace db_name with the relevant database name.
2. Run the Database section of the script.
3. Run the Schemas section of the script to generate a table of SQL strings to execute. Execute the SQL strings.
4. Run the Tables section of the script to generate a table of SQL strings to execute. Execute the SQL strings.
5. Run the Sequences section of the script to generate a table of SQL strings to execute. Execute the SQL strings.
6. GRANT this role to database users as needed.

read_access.sql

-- Section: Database - Create the read_access role
CREATE ROLE read_access;

GRANT CONNECT ON DATABASE <db_name> TO read_access;

-- Section: Schema - Grant usage to all schemas in the database
SELECT DISTINCT 'GRANT USAGE ON SCHEMA ' || schemaname || ' TO read_access;' AS schema_grant_sql_script
FROM pg_tables
WHERE schemaname NOT ILIKE 'pg_%';

-- Section: Tables - Grant read access to all tables/views in the database
SELECT DISTINCT 'GRANT SELECT ON ALL TABLES IN SCHEMA ' || schemaname || ' TO read_access;' AS table_grant_sql_script
FROM pg_tables
WHERE schemaname NOT ILIKE 'pg_%';

-- Section: Sequences - Grant read access to all sequences in the database
SELECT DISTINCT 'GRANT SELECT ON ALL SEQUENCES IN SCHEMA ' || schemaname || ' TO read_access;' AS sequence_grant_sql_script
FROM pg_tables
WHERE schemaname NOT ILIKE 'pg_%';

write_access.sql

-- Section: Database - Create the write_access role
CREATE ROLE write_access;

GRANT CONNECT ON DATABASE <db_name> TO write_access;

-- Section: Schema - Grant usuage to all scemas in the database
SELECT DISTINCT 'GRANT USAGE ON SCHEMA ' || schemaname || ' TO write_access;' AS schema_grant_sql_script
FROM pg_tables
WHERE schemaname NOT ILIKE 'pg_%';

-- Section: Tables - Grant read access to all tables/views in the database
SELECT DISTINCT 'GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA ' || schemaname || ' TO write_access;' AS table_grant_sql_script
FROM pg_tables
WHERE schemaname NOT ILIKE 'pg_%';

-- Section: Sequences - Grant read access to all sequences in the database
SELECT DISTINCT 'GRANT USAGE, UPDATE ON ALL SEQUENCES IN SCHEMA ' || schemaname || ' TO write_access;' AS sequence_grant_sql_script
FROM pg_tables
WHERE schemaname NOT ILIKE 'pg_%';