Re: Grant privileges in all schemas - Mailing list pgsql-novice

From Keith
Subject Re: Grant privileges in all schemas
Date
Msg-id CAHw75vsqiXeUMjm1ZfjoHFbzx-VmsTLnosJsiBg8jEsCXxFSJA@mail.gmail.com
Whole thread Raw
In response to Grant privileges in all schemas  (CN <cnliou9@fastmail.fm>)
List pgsql-novice


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 ...


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

pgsql-novice by date:

Previous
From: CN
Date:
Subject: Grant privileges in all schemas
Next
From: Chloe Dives
Date:
Subject: Monitoring Parallel Queries