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.