PostgreSQL: Grant by default SELECT rights to a role whenever a new table/view is created?



Problem: I have a read-only role for our database (Version Postgres 9.6). I am not able to fetch records from the newly created table, I have to run manually a grant select statement like below.

GRANT SELECT ON TABLE public.our_new_table TO read_only_role;
 I want automatic SELECT rights to a role whenever a new table/view is created?

Solution: Talk to you DBA which can set the default privileges for you, If You are DBA and want to set the default privileges to SELECT for a particular role then use the following query.


ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name GRANT SELECT ON TABLES TO user_name;


This query will help you to set the default SELECT rights to a role. Same like that you can set the default privileges for others like INSERT, UPDATE, DELETE, FUNCTION, TRIGGER, etc. For more details, you can follow the link ALTER DEFAULT PRIVILEGES.

Some time it may possible you gave the privileges to the wrong role, no issue you can revert back it using the following query.


ALTER DEFAULT PRIVILEGES IN SCHEMA schema_name REVOKE SELECT ON TABLES TO user_name;


No comments:

Post a Comment

Please do not enter any spam link in the comment box.

Related Posts

Fatal Error: Peer authentication failed for user "postgres", when trying to get pgsql working with rails.

Problem: Sometimes, we get the following error when trying to make a connection in Rails.  FATAL ERROR: Peer authentication failed for us...