Skip to content

Instantly share code, notes, and snippets.

@oropesa
Created May 17, 2023 11:00
Show Gist options
  • Save oropesa/27bbd242cae38e260948383e1f93071e to your computer and use it in GitHub Desktop.
Save oropesa/27bbd242cae38e260948383e1f93071e to your computer and use it in GitHub Desktop.
Postgresql: create users w/ readonly (SELECT only)
> CREATE ROLE name_readonly WITH
NOLOGIN
NOSUPERUSER
NOCREATEDB
NOCREATEROLE
INHERIT
NOREPLICATION
CONNECTION LIMIT -1;
> GRANT CONNECT ON DATABASE db_name TO name_readonly;
> GRANT USAGE ON SCHEMA schema_name TO name_readonly;
> GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO name_readonly;
> ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT SELECT ON TABLES TO name_readonly;
> CREATE USER user_name WITH PASSWORD 'user_password';
> GRANT name_readonly TO user_name;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment