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.

NUnit : Introduction

There are several unit test tools in the market, but one of them is NUnit, the most popular tool for doing unit testing. So in this tutorial...