Re: Grant privileges in all schemas

On Thu, Nov 24, 2016 at 12:15 PM, CN <cnliou9(at)fastmail(dot)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(at)postgresql(dot)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.

See the docs for it here

https://www.postgresql.org/docs/9.6/static/sql-alterdefaultprivileges.html

Keith