Thread: 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=>

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.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.