Skip to content

Instantly share code, notes, and snippets.

@c80609a
Forked from adevil5/README.md
Created July 14, 2023 15:39
Show Gist options
  • Save c80609a/d1744c2ff581a8806ca949ddb17fa3db to your computer and use it in GitHub Desktop.
Save c80609a/d1744c2ff581a8806ca949ddb17fa3db to your computer and use it in GitHub Desktop.
Create Postgres Roles

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.

  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.

  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.
-- 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_%';
-- 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_%';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment