Re: Default Privilege Table ANY ROLE

Greetings,

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Maybe I'm missing something, but doesn't this solve your problem
> as stated?
>
> ALTER DEFAULT PRIVILEGES IN SCHEMA myschema GRANT SELECT ON TABLES TO public;

That just means that the 'ROLE' in the result is the current role, as
per the docs:

-----
The name of an existing role of which the current role is a member. If
FOR ROLE is omitted, the current role is assumed.
-----

There was much discussion of being able to have 'FOR ALL ROLES' or
similar for ALTER DEFAULT PRIVILEGES when it went in, but there was a
lot of concern about one user getting to define the default privileges
for objects created by some other user.

The thought mentioned up-thread of having a 'group role' which can be
assigned to a role and then used for the default privileges when a role
creates an object seems like a neat idea, but I'm not sure how we'd deal
with overlaps. Specifically:

User u1, member of role r1.

Role r1 has CREATE rights on schema s1.

DEFAULT PRIVILEGES on schema s1 for r1 say GRANT SELECT ON TABLES TO r2.

User u1 runs CREATE TABLE t1 in s1.

Table t1 has GRANT SELECT ON t1 TO r2 applied.

On that vein, I'd love it if table t1 was then also OWNED by r1, as that
is the role which allows the CREATE to happen.

The issue here though is that a given user 'u1' could have access to the
schema 's1' through multiple other roles and there's no way to say which
role to use, and obviously we'd want it to somehow be deterministic.

If we did allow the 'FOR ALL ROLES' as suggested- who would be allowed
to set that? I certainly don't want more superuser-only things, but
currently a schema owner isn't allowed to set the privileges on objects
in their schema on a per-object level, at least not explicitly, and we
don't allow them to set DEFAULT PRIVILEGES for some other role:

=*> alter default privileges for role r2 in schema ts grant select on tables to r1;
ERROR: must be member of role "r2"

I do think there's an argument to be made for having a default role who
is explicitly allowed to set/change the privileges on objects in the
system, but in an ideal world that role wouldn't be allowed to access
any of the data in the system. Such a role could have this right,
perhaps.

Thanks!

Stephen