-
-
Save opan/a66bd9e4679250c76edd5f4569c5f90d to your computer and use it in GitHub Desktop.
How to create read only user in PostgreSQL
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Create a group with read-only access | |
CREATE ROLE readonly; | |
-- Grant access on public sheme to existing tables | |
GRANT USAGE ON SCHEMA public TO readonly; | |
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly; -- grant access to future tables | |
-- Grant access to specific database, repeat code below for each database | |
GRANT CONNECT ON DATABASE db_name to readonly; | |
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO readonly; -- grant access to future tables | |
GRANT USAGE ON SCHEMA public to readonly; | |
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly; | |
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; | |
-- Create a user with password | |
CREATE USER ro_user WITH ENCRYPTED PASSWORD 'ro_password'; | |
GRANT readonly TO ro_user; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment