Created
October 5, 2016 13:00
-
-
Save oinopion/4a207726edba8b99fd0be31cb28124d0 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 | |
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 a final user with password | |
CREATE USER tomek WITH PASSWORD 'secret'; | |
GRANT readaccess TO tomek; |
CREATE ROLE readaccess;
GRANT USAGE ON SCHEMA public TO readaccess;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess;
GRANT CONNECT ON DATABASE 'database_name' to readaccess;
CREATE USER user_name WITH PASSWORD ‘password’;
GRANT readaccess TO user_name;
GRANT REFERENCES ON ALL TABLES IN SCHEMA public TO readaccess;
To be able to see PrimaryKey constraint
Thanks it's useful
This is golden
Getting permission denied error on viewing list of data from any tables
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
tks man