Date: Thu, 29 Sep 2016 20:28:54 -0400
From: Stephen Frost <sfrost@snowman.net>
To: atiris@gmail.com
Cc: pgsql-docs@postgresql.org
Subject: Re: Default privileges not working
Message-ID: <20160930002854.GO5148@tamriel.snowman.net>
References: <20160929170830.16874.96462@wrigleys.postgresql.org>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="YZHBoLCvxQT2MhEB"
Content-Disposition: inline
In-Reply-To: <20160929170830.16874.96462@wrigleys.postgresql.org>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-docs-owner@postgresql.org


--YZHBoLCvxQT2MhEB
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Greetings,

* atiris@gmail.com (atiris@gmail.com) wrote:
> Thanks for the reply.
[...]
> alter default privileges in schema public grant select on tables to
> &quot;test_readonly&quot;, &quot;test_readwrite&quot;, &quot;test_power&quot;;
> alter default privileges in schema public grant insert, update, delete on
> tables to &quot;test_readwrite&quot;, &quot;test_power&quot;;
> alter default privileges in schema public grant all on tables to
> &quot;test_power&quot;;

Default privileges are assigned to roles.  In other words, you can only
say "tables created by user X have default privileges Y."  If you omit
the user from the ALTER DEFAULT PRIVILEGES command, then the
CURRENT_USER is used.

Use: \ddp
in psql to see the default privileges created and which user they are
associated with.  My guess is that in the above scenario, default
privileges were only set up for the 'postgres' user.

> -- CONNECT AS USER: user_power
> select * from a;
> create table b (x numeric); -- ok, created
> insert into a values (3);
> insert into b values (4); -- ok, everything like expected

> -- CONNECT AS USER: user_readwrite
> select * from b; -- SQL Error [42501]: ERROR: permission denied for relation
> b
> -- why? according to grant default privileges on tables for insert update
> delete and select this user can do any selection insertion or deletion from
> tables in public schema
> insert into b values (5); -- SQL Error [42501]: ERROR: permission denied for
> relation b

There were no default privileges set up for the "user_power" role and,
therefore, when that role created a table, no privileges were set for
it.  That's why the query by user_readwrite failed.

Try doing this first:

ALTER DEFAULT PRIVILEGES FOR user_power IN SCHEMA PUBLIC GRANT ...

And then creating a table as the "user_power" role.

> -- So no one except user who create table b can read from it.
> -- But with tables created as USER: postgres, everything is ok.
> -- 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?

Assign default privileges for all roles which will be creating objects.

Thanks!

Stephen

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

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

iQIcBAEBCgAGBQJX7bHGAAoJEO1sijiDR2RVHBcQAMdqPl8L6DWk07tcNZS89UDP
Kw8e0UwuyjP98OcQybkuosGxLAuGVm9gzLyHA4v76Bm2r3lLt0TVSrO9+SuEK/Dt
Ut+Ayett6f1CYvy6h+tmu0mPuUY5kobp8Q9DnoIci8dHFEuMCu/prsdYvYGModZx
s0Zap32G99P6sskxuOzyfJl14YkNoWKbkgtN0dpCgd/t9NLlIrU4flV1jz3ZPwE7
SMlWnBsKkyhK/8PFUIcr5PQlezbFfdzDMA8NjHaEi9rPO5dzE4ueiBsMIaeqZpta
HkhuU5gJBVRS58zIjKKoCTgOeSaDKav8o5Ep43owCQzFr0qPs/A93w4WrYbIRcVk
7OUD7DtEdybRMal18ms9XEoEUD9UfkdBU5kkUJ7Ks4/ugMA3lb24VfmH368aQNCk
znDfG+wpwNCIrPZSy3AgBkXoGTFGHozpQYYE43Lv8QxwHKrlg8JsushBVQTYrFCV
wS/8Ovcut7XJRo9K9rkNzFKAoTn3pCITX9ZIJrNPGk3GVv123wsPAD9KS4KuyX6M
3OnNRMVFzHvDxYk2MVi212i9iBhj0Sn7vcfGdWrU8RNwg/pXMpXOXsAvvAGnyln8
Zt154AOzbqwC3+cg6NUFsgYsO8e9SNarojyxT/V/7ZVYDEsfW6ZbNCi6imGnL2WU
Jw7It8wHezjzizxOsOcw
=5DwO
-----END PGP SIGNATURE-----

--YZHBoLCvxQT2MhEB--