Last active
July 17, 2020 11:39
-
-
Save satyaki1/523969b80f4fec6ddac141d14e3d12a2 to your computer and use it in GitHub Desktop.
Postgresql manage user role and permissions. Postgresql create readonly user. Revoke create table privilege from public schema.
This file contains hidden or 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
| -- NOTE : It considers the default postgresql schema `public`, it assumes the database name to satyaki | |
| -- Revoke privileges from 'public' role | |
| REVOKE CREATE ON SCHEMA public FROM PUBLIC; | |
| REVOKE ALL ON DATABASE satyaki FROM PUBLIC; | |
| -- Read-only role | |
| CREATE ROLE readonly; | |
| GRANT CONNECT ON DATABASE satyaki TO readonly; | |
| GRANT USAGE ON SCHEMA public TO readonly; | |
| GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; | |
| ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly; | |
| -- Read/write role | |
| CREATE ROLE readwrite; | |
| GRANT CONNECT ON DATABASE satyaki TO readwrite; | |
| GRANT USAGE, CREATE ON SCHEMA public TO readwrite; | |
| GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO readwrite; | |
| ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO readwrite; | |
| GRANT REFERENCES, TRIGGER ON ALL TABLES IN SCHEMA public TO readwrite; | |
| ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT REFERENCES, TRIGGER ON TABLES TO readwrite; | |
| GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO readwrite; | |
| ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO readwrite; | |
| -- Users creation | |
| CREATE USER satyaki_readonly WITH PASSWORD 'satyaki1234'; | |
| CREATE USER satyaki_write WITH PASSWORD 'satyaki1234'; | |
| -- Grant privileges to users | |
| GRANT readonly TO satyaki_readonly; | |
| GRANT readwrite TO satyaki_write; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment