Thread: Grant privileges in all schemas
Hello! What are the syntaxes to 1. grant SELECT on all tables in all existing schemas and all schemas that might be created in the future to a role? 2. grant executing all PL/PGSQL functions in all existing schemas and all schemas that might be created in the future to a role? Both aforementioned privileges are granted only on one specific database. Thank you in advance! Best regards, CN -- http://www.fastmail.com - Faster than the air-speed velocity of an unladen european swallow
On Thu, Nov 24, 2016 at 12:15 PM, CN <cnliou9@fastmail.fm> wrote:
Hello!
What are the syntaxes to
1. grant SELECT on all tables in all existing schemas and all schemas
that might be created in the future to a role?
2. grant executing all PL/PGSQL functions in all existing schemas and
all schemas that might be created in the future to a role?
Both aforementioned privileges are granted only on one specific
database.
Thank you in advance!
Best regards,
CN
--
http://www.fastmail.com - Faster than the air-speed velocity of an
unladen european swallow
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice
There's nothing that will grant everything in all schemas in a single command, but there are grants for all of a specific object in a given schema
GRANT ... ON ALL TABLES IN SCHEMA ... TO ...
GRANT ... ON ALL FUNCTIONS IN SCHEMA ... TO ...
See the GRANT docs for more https://www.postgresql.org/docs/9.6/static/sql-grant.html
To automatically have grants applied to newly created objects, you can use the ALTER DEFAULT PRIVILEGES command. Keep in mind that this does not change the default for any object created by any user in the entire database. It changes the default privileges for objects created by a specific role. If no role is given in the command, the default privileges of the currently logged in role are changed. If this is something you need, it's a good idea to choose a specific role that creates objects and manages DDL and change its default privileges.
Keith