Skip to content

Instantly share code, notes, and snippets.

@pplonski

pplonski/read_only_user_in_postgres.sql

Created March 4, 2019 10:42
Show Gist options
  • Star 1 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save pplonski/49044e4f93950c157013ad40d61254b8 to your computer and use it in GitHub Desktop.
Save pplonski/49044e4f93950c157013ad40d61254b8 to your computer and use it in GitHub Desktop.
Download ZIP
Read only user in postgres database
Raw
read_only_user_in_postgres.sql
-- create a group
CREATE ROLE readaccess;
-- grant access to existing tables
GRANT USAGE ON SCHEMA public TO readaccess;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;
-- grant access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess;
-- create user
CREATE USER user_name WITH PASSWORD 'secret_password';
GRANT readaccess TO user_name;
@pplonski
Copy link
Author

pplonski commented Mar 4, 2019 •
edited

The gist is based on:

The blog post:
https://pplonski.github.io/read-only-user-in-postgres/

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