From: Stephen Frost <sfrost@snowman.net>
To: Jozef Pažin <atiris@gmail.com>
Cc: pgsql-docs@postgresql.org
Subject: Re: Default privileges not working
Date: Fri, 30 Sep 2016 09:30:10 -0400
Message-ID: <20160930133010.GP5148@tamriel.snowman.net> (raw)
In-Reply-To: <CACzmonfQ9KRno5uTd2BYtoaycCh0tV4fycDK3QP6qbFBw8WvzA@mail.gmail.com>
References: <20160929170830.16874.96462@wrigleys.postgresql.org>
<20160930002854.GO5148@tamriel.snowman.net>
<CACzmonfQ9KRno5uTd2BYtoaycCh0tV4fycDK3QP6qbFBw8WvzA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-docs>
Jozef,
* Jozef Pažin (atiris@gmail.com) wrote:
> -- grant for new tables
> -- only users "postgres" and "test_power" can create tables;
> alter default privileges for role "test_power" in schema public grant
> select on tables to "test_readonly", "test_readwrite", "test_power";
> alter default privileges for role "test_power" in schema public grant
> insert, update, delete on tables to "test_readwrite", "test_power";
> alter default privileges for role "test_power" in schema public grant all
> on tables to "test_power";
>
> alter default privileges for user "postgres" in schema public grant select
> on tables to "test_readonly", "test_readwrite", "test_power";
> alter default privileges for user "postgres" in schema public grant insert,
> update, delete on tables to "test_readwrite", "test_power";
> alter default privileges for user "postgres" in schema public grant all on
> tables to "test_power";
Above, you set default privileges for the 'postgres' and the
'test_power' roles, however...
> -- CONNECT AS USER: user_power
Here, you are connecting as the 'user_power' role, for which no default
privileges were set.
> select * from a;
> create table b (x numeric); -- ok
This table is created as the 'user_power' role and, since there were no
default privileges set for this role, it is created with no privileges
granted.
Leading to...
> -- CONNECT AS USER: user_readwrite
> select * from b; -- wrong -- SQL Error [42501]: ERROR: permission denied
> for relation b
> insert into b values (5); -- wrong -- SQL Error [42501]: ERROR: permission
> denied for relation b
These permission denied errors, which are entirely correct because no
default privileges were set for the case where the 'user_power' role
creates objects in the 'public' schema.
Please use \dp to see what the privileges are after object creation, and
use \ddp to see what the default privileges will be for objects created
by which roles in which schemas.
> -- How can I use default privileges to grant read to any new tables
> -- created to USER readonly. And grant all CRUD operations
> -- to USER readwrite, and grant delete table by USER power?
You must set up default privileges for all roles which will be creating
objects. Above, you only set them for the 'postgres' role and the
'test_power' role, but then the 'user_power' role created objects.
One approach to dealing with this is to have fewer roles which can
create objects and then require users to do a 'SET ROLE' prior to
creating an object, eg:
CONNECT AS USER: user_power
SET ROLE test_power;
CREATE TABLE b (a int);
The above action creates the table as the 'test_power' role and
therefore the default privileges for the 'test_power' role will be
applied to all newly created objects.
Thanks!
Stephen
Attachments:
signature.asc (application/pgp-signature, 818B) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: sfrost@snowman.net, atiris@gmail.com
Subject: Re: Default privileges not working
In-Reply-To: <20160930133010.GP5148@tamriel.snowman.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox