Re: Setting default privs for a user doesn't seem to work.

From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Gauthier, Dave
Sent: Thursday, December 06, 2012 5:27 PM
To: pgsql-general
Subject: [GENERAL] Setting default privs for a user doesn't seem to work.

What's wrong with this picture. Trying (failing) to create a user called
"select" with default select privs and nothing else. Demo below. Comments
in red...

fcadsql7> psql sde

psql (9.1.5)

Type "help" for help.

sde=# \du

List of roles

Role name | Attributes | Member of

-----------+------------------------------------------------+-----------

insert | | {}

pgdbadm | Superuser, Create role, Create DB, Replication | {}

select | | {} <-- the
"select" user

sde=# alter default privileges for user "select" grant select on tables to
"select";

ALTER DEFAULT PRIVILEGES

sde=# alter default privileges for user "select" grant select on sequences
to "select";

ALTER DEFAULT PRIVILEGES

sde=# alter default privileges for user "select" grant execute on functions
to "select";

ALTER DEFAULT PRIVILEGES

"select" user should now get 'select' priv for all future tables and
sequences, and execute functions.

sde=#

sde=# create table foo (a text); <-- note, the "postgres" user is creating
the foo table, not "select"

CREATE TABLE

sde=# insert into foo (a) values ('aaa'), ('bbb');

INSERT 0 2

sde=# select * from foo;

a

-----

aaa

bbb

(2 rows)

sde=# \q

fcadsql7> psql --user=select sde <-- connect as "select" user and try to
select from the new "foo" table. This fails.

psql (9.1.5)

Type "help" for help.

sde=> select * from foo;

ERROR: permission denied for relation foo <--- Brrrrrt!

sde=>

Dave,

I believe the <FOR USER "select"> is messing you up. From the documentation
this clause causes the grant system to only apply the specified rules when a
member of the "select" role creates the object. Since "postgres" is not a
member of "select" when "postgres" creates a new object only the global
default permissions are added and not those specific to "select".

This should work in theory:

ALTER DEFAULT PRIVILEGES FOR "postgres" GRANT SELECT ON TABLES TO "select";

I interpreted this behavior from this sentence in the documentation of
(ALTER DEFAULT PRIVILEGES):

"You can change default privileges only for objects that will be created by
yourself or by roles that you are a member of"

I will concede that the wording and implications thereof are unclear - which
I have found to be the case of the GRANT documentation in general. I'm not
complaining that strongly since I'm also not volunteering to improve it. I
think I would consider it to be worthwhile to support someone else who does
want to take on that job.

Anyway, I've tested the and can confirm both your behavior and the fact that
changing <FOR USER "select"> to <FOR USER "postgres"> solve the issue.

While I want to say that aside from the global "PULIC" default grants all
other default grants are attached to the user performing the DDL. There may
be (are likely are) other various side-effects regarding role-inheritance
and "ALTER object SET OWNER ." that come into play here as well.

David J.