Date: Fri, 30 Sep 2016 09:30:10 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Jozef =?utf-8?B?UGHFvmlu?= <atiris@gmail.com>
Cc: pgsql-docs@postgresql.org
Subject: Re: Default privileges not working
Message-ID: <20160930133010.GP5148@tamriel.snowman.net>
References: <20160929170830.16874.96462@wrigleys.postgresql.org>
 <20160930002854.GO5148@tamriel.snowman.net>
 <CACzmonfQ9KRno5uTd2BYtoaycCh0tV4fycDK3QP6qbFBw8WvzA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="oOINc+Z9LTvKzseX"
Content-Disposition: inline
In-Reply-To: 
 <CACzmonfQ9KRno5uTd2BYtoaycCh0tV4fycDK3QP6qbFBw8WvzA@mail.gmail.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-docs-owner@postgresql.org


--oOINc+Z9LTvKzseX
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Jozef,

* Jozef Pa=C5=BEin (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";
>=20
> 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 inser=
t,
> 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

--oOINc+Z9LTvKzseX
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJX7mjiAAoJEO1sijiDR2RVp8MP/04XLUVsxuovUqlCaDn/OHni
SBZOHbIEHA/zdERFOw+TlpUjWNEpaycOix7FONmRcq7Cfza1On7D0D5MhoZj9YaR
ECT8Z0JzwTqAGvAy3PEK9sPSmTkgbSY0L2lnBZX8aujWR+rUZGitXg8004fNqK2P
pOVIfwel4Ljzwm0tN3PXVDht7C9KzkBgAQ9dBp/I/IZ/VqIvO5D64qCDwPM463b6
EYNM1Ev5SmUgrBvhBpcS4B5GrsB3O7Rr9erK5q4kKXSw9bPFOQ2AoW9M5nXfhIp0
6L3ctZGfjFW1CYWzVF790VWo2VKJlzTSdFB7LEpLTnoe8v/BrjGFuBM2GLjfVIUR
gi5tMG/peIUh2kzAh4wJw+r28exMNmmBd37ckCd3TwwolwuDBIkST6nkmEcFFdMu
IA/M7DZwcXYeG5muvKANWA+tpBEbLKB2yBSB9MXt0phhqXgMvS/jXXVmDe3S3HWl
pi/pVNa3nZQQ+2VP+d3Xg5gsWd0SxOk1vlaqAC8Hi9wCaPcgWzDiqz76pYP0phO9
zhn4gtn0jYV7tD5NyAuCIWP0DSaV/ewgHwOk3mfepb/cfHMt5vdMsx3o7tIBpySt
8vt6qxgO4FX6cOWL4blM/YXEM1NerAacS0+jdd1VY+R4hMn2f9ECk3qK+EGPeatc
wHG7Yk5Ip6ubuXxwtYgO
=ZeAq
-----END PGP SIGNATURE-----

--oOINc+Z9LTvKzseX--