Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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 SELECT ON ALL SEQUENCES 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;
@Systho

This comment has been minimized.

Copy link

@Systho Systho commented Jul 1, 2020

You may want to add :

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO postgres_ro_group;

for future sequences

@diego-ojeda-binbash

This comment has been minimized.

Copy link

@diego-ojeda-binbash diego-ojeda-binbash commented Feb 4, 2021

Are you sure this works? I mean you still seem to be using public schema which already grants too many privileges by default. I would say your postgres_ro user can actually do more than SELECT. Have you tried INSERTS, CREATEs, and so on with it?
Reference: https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/

@Systho

This comment has been minimized.

Copy link

@Systho Systho commented Feb 4, 2021

No I have not but this line only add SELECT privileges so I would not think it would allow me to run anything different than a SELECT

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment