Create a read-only user for a RDS DB instance running PostgreSQL

create_read_only_postgres_rds.md

Create read_only role

CREATE ROLE readonly;   

Grant access to all existing tables

GRANT CONNECT ON DATABASE <database> TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly;

Grant access to all tables created in the future

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO readonly;

Create user and grant role to this user

CREATE ROLE readonly_user WITH PASSWORD 'password' LOGIN;   
GRANT readonly TO readonly_user;