Skip to content

Instantly share code, notes, and snippets.

@lucasbastianik
Forked from checco/rw_ro_access.sql
Created March 17, 2020 22:37
Show Gist options
  • Save lucasbastianik/592c15e19c10620b0d160b91863a5181 to your computer and use it in GitHub Desktop.
Save lucasbastianik/592c15e19c10620b0d160b91863a5181 to your computer and use it in GitHub Desktop.
How to create a read only user in AWS RDS PostgreSQL and a user with superuser privileges on AWS RDS PostgreSQL
--
-- Read only
--
-- Create a group
CREATE ROLE postgres_ro_group;
-- Grant access to existing tables
GRANT USAGE ON SCHEMA public TO postgres_ro_group;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO postgres_ro_group;
-- Grant access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO postgres_ro_group;
-- Create a final user with password
CREATE USER postgres_ro WITH PASSWORD 'secret';
GRANT postgres_ro_group TO postgres_ro;
--
-- Superuser
--
-- Create a final user with password
CREATE USER postgres_adm WITH PASSWORD 'secret';
GRANT rds_superuser to postgres_adm;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment