Quantcast

ALTER DEFAULT PRIVILEGES FOR ROLE is broken

classic Classic list List threaded Threaded
24 messages Options
12
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

ALTER DEFAULT PRIVILEGES FOR ROLE is broken

Josh Berkus
Folks,

The "FOR ROLE" syntax is completely broken, as of 9.2.4.  Not sure when
exactly this got broken; I remember it working sometime in the past:

[jberkus@pgx-test ~]$ psql -U postgres analytics2
psql (9.2.4)
Type "help" for help.

analytics2=# ALTER DEFAULT PRIVILEGES FOR ROLE webui IN SCHEMA web
GRANT SELECT ON TABLES TO dbreader;
ERROR:  permission denied for schema web

... in fact, there is no combination of actions which will make "FOR
ROLE" work.  Any invokation of "FOR ROLE" inevitably results in a
"permission denied" message:

analytics2=> \c - webui
You are now connected to database "analytics2" as user "webui".
analytics2=> ALTER DEFAULT PRIVILEGES FOR ROLE webui IN SCHEMA web
GRANT SELECT ON TABLES TO dbreader;
ERROR:  permission denied for schema web

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ALTER DEFAULT PRIVILEGES FOR ROLE is broken

Josh Berkus

> ... in fact, there is no combination of actions which will make "FOR
> ROLE" work.  Any invokation of "FOR ROLE" inevitably results in a
> "permission denied" message:
>
> analytics2=> \c - webui
> You are now connected to database "analytics2" as user "webui".
> analytics2=> ALTER DEFAULT PRIVILEGES FOR ROLE webui IN SCHEMA web
> GRANT SELECT ON TABLES TO dbreader;
> ERROR:  permission denied for schema web

Actually, the problem is worse than I thought.  It looks like I can't
set default privs for any role which is not the owner of the schema:

[jberkus@pgx-test ~]$ psql -U webui analytics2
psql (9.2.4)
Type "help" for help.

analytics2=> ALTER DEFAULT PRIVILEGES IN SCHEMA web GRANT SELECT ON
TABLES TO dbreader;
ERROR:  permission denied for schema web

In other words, ALTER DEFAULT PRIVs only works if you are the role
you're trying to grant, and that role is the owner of the schema.  It
doesn't work for any other role or any schema you don't own.

This means that I have NO WAY to set default privs for the majority of
users on my system.  WTF?  How did we break this so badly?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ALTER DEFAULT PRIVILEGES FOR ROLE is broken

Tom Lane-2
Josh Berkus <[hidden email]> writes:
> Actually, the problem is worse than I thought.  It looks like I can't
> set default privs for any role which is not the owner of the schema:

> analytics2=> ALTER DEFAULT PRIVILEGES IN SCHEMA web GRANT SELECT ON
> TABLES TO dbreader;
> ERROR:  permission denied for schema web

The fine manual notes that the target role has to already have CREATE
privileges on the target schema --- maybe that's what's biting you in
this case?  If so, I'd agree that this error message is insufficiently
specific, but I don't think the restriction is unreasonable.  Without
CREATE privs, there's no particular value in setting default privs for
to-be-created objects.

                        regards, tom lane


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ALTER DEFAULT PRIVILEGES FOR ROLE is broken

Josh Berkus

> The fine manual notes that the target role has to already have CREATE
> privileges on the target schema --- maybe that's what's biting you in
> this case?

Nope.  That was the first thing I thought of.  It really is that the
target role must *own* the schema.  So clearly a bug.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ALTER DEFAULT PRIVILEGES FOR ROLE is broken

Robert Haas
On Sun, Apr 28, 2013 at 9:39 PM, Josh Berkus <[hidden email]> wrote:
>> The fine manual notes that the target role has to already have CREATE
>> privileges on the target schema --- maybe that's what's biting you in
>> this case?
>
> Nope.  That was the first thing I thought of.  It really is that the
> target role must *own* the schema.  So clearly a bug.

wfm.

rhaas=# create user bob;
CREATE ROLE
rhaas=# create schema we_like_bob;
CREATE SCHEMA
rhaas=# alter default privileges for role bob in schema we_like_bob
grant select on tables to bob;
ERROR:  permission denied for schema we_like_bob
rhaas=# grant create on schema we_like_bob to bob;
GRANT
rhaas=# alter default privileges for role bob in schema we_like_bob
grant select on tables to bob;
ALTER DEFAULT PRIVILEGES

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ALTER DEFAULT PRIVILEGES FOR ROLE is broken

Josh Berkus

> rhaas=# create user bob;
> CREATE ROLE
> rhaas=# create schema we_like_bob;
> CREATE SCHEMA
> rhaas=# alter default privileges for role bob in schema we_like_bob
> grant select on tables to bob;
> ERROR:  permission denied for schema we_like_bob
> rhaas=# grant create on schema we_like_bob to bob;
> GRANT
> rhaas=# alter default privileges for role bob in schema we_like_bob
> grant select on tables to bob;
> ALTER DEFAULT PRIVILEGES

Hmmmm.  Must have got something tangled up there; starting over with a
clean database and new users I got it to work.  I'll see if I can
reproduce the issue I'm getting on my production schema.

This moves the general brokenness of this feature from a bug to (a) a
documentation issue and (b) unusably fussy.  For (a), I think we need
the following line in the docs:

DEFAULT PRIVILEGES may only be granted to a ROLE which already has
CREATE permission on the specified schema.

For (b), I'll take it up in the 9.4 dev cycle.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ALTER DEFAULT PRIVILEGES FOR ROLE is broken

Tom Lane-2
Josh Berkus <[hidden email]> writes:
> This moves the general brokenness of this feature from a bug to (a) a
> documentation issue and (b) unusably fussy.  For (a), I think we need
> the following line in the docs:

> DEFAULT PRIVILEGES may only be granted to a ROLE which already has
> CREATE permission on the specified schema.

As I pointed out to you last night, it does already say that.
I think the problem here is that we're just throwing a generic
permissions failure rather than identifying the particular permission
needed.

                        regards, tom lane


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ALTER DEFAULT PRIVILEGES FOR ROLE is broken

Josh Berkus
On 04/29/2013 09:59 AM, Tom Lane wrote:

> Josh Berkus <[hidden email]> writes:
>> This moves the general brokenness of this feature from a bug to (a) a
>> documentation issue and (b) unusably fussy.  For (a), I think we need
>> the following line in the docs:
>
>> DEFAULT PRIVILEGES may only be granted to a ROLE which already has
>> CREATE permission on the specified schema.
>
> As I pointed out to you last night, it does already say that.
> I think the problem here is that we're just throwing a generic
> permissions failure rather than identifying the particular permission
> needed.

Yeah, a better error message would help a lot.  My first thought was
"WTF?  I'm the superuser, whaddya mean, 'permission denied'"?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ALTER DEFAULT PRIVILEGES FOR ROLE is broken

Tom Lane-2
Josh Berkus <[hidden email]> writes:
> On 04/29/2013 09:59 AM, Tom Lane wrote:
>> As I pointed out to you last night, it does already say that.
>> I think the problem here is that we're just throwing a generic
>> permissions failure rather than identifying the particular permission
>> needed.

> Yeah, a better error message would help a lot.  My first thought was
> "WTF?  I'm the superuser, whaddya mean, 'permission denied'"?

Right.  I wonder if there's any good reason why we shouldn't extend
aclerror() to, in all cases, add a DETAIL line along the lines of

        ERROR:  permission denied for schema web
        DETAIL:  This operation requires role X to have privilege Y.

Is there any scenario where this'd be exposing too much info?

                        regards, tom lane


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ALTER DEFAULT PRIVILEGES FOR ROLE is broken

Josh Berkus

> Right.  I wonder if there's any good reason why we shouldn't extend
> aclerror() to, in all cases, add a DETAIL line along the lines of
>
> ERROR:  permission denied for schema web
> DETAIL:  This operation requires role X to have privilege Y.
>
> Is there any scenario where this'd be exposing too much info?

Not that I can think of.  The fact that role X doesn't have create on
schema Y isn't exactly privileged info.  Further, to make any use of
that information, you'd have to be able to SET ROLE X, in which case you
can just test for yourself if X has CREATE permission.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ALTER DEFAULT PRIVILEGES FOR ROLE is broken

Josh Berkus
In reply to this post by Tom Lane-2
Tom,

I'm also seeing that the DEFAULT PRIVs I get seem to be dependant on my
login role, not my current role.  That is, if role "dba" has default
privs set, and role "josh", which is a member of "dba" does not, and
"josh" does "set role dba" before creating some tables, those tables do
NOT get dba's default privs.  At least, that's what happens in my testing.

Is that expected?  If so, that should certainly be documented.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ALTER DEFAULT PRIVILEGES FOR ROLE is broken

Noah Misch-2
In reply to this post by Tom Lane-2
On Mon, Apr 29, 2013 at 01:25:47PM -0400, Tom Lane wrote:

> Josh Berkus <[hidden email]> writes:
> > On 04/29/2013 09:59 AM, Tom Lane wrote:
> >> As I pointed out to you last night, it does already say that.
> >> I think the problem here is that we're just throwing a generic
> >> permissions failure rather than identifying the particular permission
> >> needed.
>
> > Yeah, a better error message would help a lot.  My first thought was
> > "WTF?  I'm the superuser, whaddya mean, 'permission denied'"?
>
> Right.  I wonder if there's any good reason why we shouldn't extend
> aclerror() to, in all cases, add a DETAIL line along the lines of
>
> ERROR:  permission denied for schema web
> DETAIL:  This operation requires role X to have privilege Y.
>
> Is there any scenario where this'd be exposing too much info?

Can't think of one.  Seems safe and helpful.

The particular restriction at hand, namely that a role have CREATE rights on a
schema before assigning role-specific default privileges, seems like needless
paternalism.  It would be akin to forbidding ALTER ROLE ... PASSWORD on a
NOLOGIN role.  I'd support removing it when such a proposal arrives.  If
anything, require that the user executing the ALTER DEFAULT PRIVILEGES, not
the subject of the command, has CREATE rights on the schema.

--
Noah Misch
EnterpriseDB                                 http://www.enterprisedb.com


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ALTER DEFAULT PRIVILEGES FOR ROLE is broken

Tom Lane-2
Noah Misch <[hidden email]> writes:
> The particular restriction at hand, namely that a role have CREATE rights on a
> schema before assigning role-specific default privileges, seems like needless
> paternalism.  It would be akin to forbidding ALTER ROLE ... PASSWORD on a
> NOLOGIN role.  I'd support removing it when such a proposal arrives.

Hm.  I defended that restriction earlier, but it now occurs to me to
wonder if it doesn't create a dump/reload sequencing hazard.  I don't
recall that pg_dump is aware of any particular constraints on the order
in which it dumps privilege-grant commands.  If it gets this right,
that's mostly luck, I suspect.

> If
> anything, require that the user executing the ALTER DEFAULT PRIVILEGES, not
> the subject of the command, has CREATE rights on the schema.

That would be just as dangerous from this angle.

                        regards, tom lane


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ALTER DEFAULT PRIVILEGES FOR ROLE is broken

Josh Berkus

> Hm.  I defended that restriction earlier, but it now occurs to me to
> wonder if it doesn't create a dump/reload sequencing hazard.  I don't
> recall that pg_dump is aware of any particular constraints on the order
> in which it dumps privilege-grant commands.  If it gets this right,
> that's mostly luck, I suspect.

For that matter, it raises a serious practical obstacle to implementing
schema-specific default privs by script, if you have to first check
whether the user in question has create privs ... something we don't
make it at all easy to do.

For 9.4, I'm going to argue that the ALTER DEFAULT PRIVs feature has
completely failed in its goal to make database permissions easier to
manage.  Expect more detail on that after beta.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ALTER DEFAULT PRIVILEGES FOR ROLE is broken

Tom Lane-2
In reply to this post by Tom Lane-2
I wrote:
> Noah Misch <[hidden email]> writes:
>> The particular restriction at hand, namely that a role have CREATE rights on a
>> schema before assigning role-specific default privileges, seems like needless
>> paternalism.  It would be akin to forbidding ALTER ROLE ... PASSWORD on a
>> NOLOGIN role.  I'd support removing it when such a proposal arrives.

> Hm.  I defended that restriction earlier, but it now occurs to me to
> wonder if it doesn't create a dump/reload sequencing hazard.  I don't
> recall that pg_dump is aware of any particular constraints on the order
> in which it dumps privilege-grant commands.  If it gets this right,
> that's mostly luck, I suspect.

This issue just came up again on -general, in an even more annoying
form:

regression=# create role foo;
CREATE ROLE
regression=# create schema s1;
CREATE SCHEMA
regression=# grant create on schema s1 to public;
GRANT
regression=# alter default privileges for role foo in schema s1 grant select on tables to public;
ALTER DEFAULT PRIVILEGES
regression=# revoke create on schema s1 from public;
REVOKE
regression=# alter default privileges for role foo in schema s1 revoke select on tables from public;
ERROR:  permission denied for schema s1

That is, it complains about lack of create permissions for the target
role even if you're trying to remove default privileges not add them.
Even if you think such a check is sane for the GRANT case, it seems
pretty hard to defend for the REVOKE case.

At this point I'm prepared to support just removing the check
altogether, ie diking out this test in SetDefaultACLsInSchemas():

            aclresult = pg_namespace_aclcheck(iacls->nspid, iacls->roleid,
                                              ACL_CREATE);
            if (aclresult != ACLCHECK_OK)
                aclcheck_error(aclresult, ACL_KIND_NAMESPACE,
                               nspname);

Essentially the argument for allowing this without a permissions check
is "I'm not really doing anything to the schema, just preconfiguring the
rights that will be attached to a new object if I later (successfully)
create one in this schema".

Thoughts?  If we change this, should we back-patch it?  I'm inclined to
think it's a bug (especially if the restore-ordering hazard is real)
so we should back-patch.

                        regards, tom lane


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ALTER DEFAULT PRIVILEGES FOR ROLE is broken

Stephen Frost
* Tom Lane ([hidden email]) wrote:
> Essentially the argument for allowing this without a permissions check
> is "I'm not really doing anything to the schema, just preconfiguring the
> rights that will be attached to a new object if I later (successfully)
> create one in this schema".

Makes sense to me; if we were going to do something, I'd say a warning
would be better, but I'm alright with nothing too.

> Thoughts?  If we change this, should we back-patch it?  I'm inclined to
> think it's a bug (especially if the restore-ordering hazard is real)
> so we should back-patch.

Agreed.

        Thanks,

                Stephen

signature.asc (205 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ALTER DEFAULT PRIVILEGES FOR ROLE is broken

Tom Lane-2
Stephen Frost <[hidden email]> writes:
> * Tom Lane ([hidden email]) wrote:
>> Essentially the argument for allowing this without a permissions check
>> is "I'm not really doing anything to the schema, just preconfiguring the
>> rights that will be attached to a new object if I later (successfully)
>> create one in this schema".

> Makes sense to me; if we were going to do something, I'd say a warning
> would be better, but I'm alright with nothing too.

Hm.  Throwing a NOTICE saying "btw, this won't be of any value until the
user has CREATE rights in that schema" might be a reasonable compromise.

                        regards, tom lane


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ALTER DEFAULT PRIVILEGES FOR ROLE is broken

Stephen Frost
* Tom Lane ([hidden email]) wrote:
> Hm.  Throwing a NOTICE saying "btw, this won't be of any value until the
> user has CREATE rights in that schema" might be a reasonable compromise.

Yeah, a NOTICE makes more sense than a WARNING, so +1 from me.

        Thanks,

                Stephen

signature.asc (205 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ALTER DEFAULT PRIVILEGES FOR ROLE is broken

Robert Haas
In reply to this post by Stephen Frost
On Fri, Jun 7, 2013 at 12:44 PM, Stephen Frost <[hidden email]> wrote:
> * Tom Lane ([hidden email]) wrote:
>> Essentially the argument for allowing this without a permissions check
>> is "I'm not really doing anything to the schema, just preconfiguring the
>> rights that will be attached to a new object if I later (successfully)
>> create one in this schema".
>
> Makes sense to me; if we were going to do something, I'd say a warning
> would be better, but I'm alright with nothing too.

I vote for nothing.  I always thought that check was wrong-headed.

>> Thoughts?  If we change this, should we back-patch it?  I'm inclined to
>> think it's a bug (especially if the restore-ordering hazard is real)
>> so we should back-patch.
>
> Agreed.

Seems reasonable.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: ALTER DEFAULT PRIVILEGES FOR ROLE is broken

Noah Misch-2
In reply to this post by Tom Lane-2
On Fri, Jun 07, 2013 at 12:26:59PM -0400, Tom Lane wrote:

> I wrote:
> > Noah Misch <[hidden email]> writes:
> >> The particular restriction at hand, namely that a role have CREATE rights on a
> >> schema before assigning role-specific default privileges, seems like needless
> >> paternalism.  It would be akin to forbidding ALTER ROLE ... PASSWORD on a
> >> NOLOGIN role.  I'd support removing it when such a proposal arrives.
>
> > Hm.  I defended that restriction earlier, but it now occurs to me to
> > wonder if it doesn't create a dump/reload sequencing hazard.  I don't
> > recall that pg_dump is aware of any particular constraints on the order
> > in which it dumps privilege-grant commands.  If it gets this right,
> > that's mostly luck, I suspect.

> At this point I'm prepared to support just removing the check
> altogether, ie diking out this test in SetDefaultACLsInSchemas():
>
>             aclresult = pg_namespace_aclcheck(iacls->nspid, iacls->roleid,
>                                               ACL_CREATE);
>             if (aclresult != ACLCHECK_OK)
>                 aclcheck_error(aclresult, ACL_KIND_NAMESPACE,
>                                nspname);
>
> Essentially the argument for allowing this without a permissions check
> is "I'm not really doing anything to the schema, just preconfiguring the
> rights that will be attached to a new object if I later (successfully)
> create one in this schema".

Seems fine.  I might have instead changed it to a test of the caller's
permissions.  Though not a security risk, it's unnecessary and mildly strange
to let a user personally attach default privileges to a schema on which he has
no CREATE privilege.  A possible objection is that non-superuser pg_restore
operations could fail more than they otherwise would, but they could also fail
less often: a non-superuser schema owner is unable to restore default
privilege entries of unrelated users, so preventing mischievous users from
adding them is a slight help.

Concerning the proposal to emit a NOTICE, I wouldn't.  NOTICEs are good when
the difference between the user's probable intent and the actual outcome will
be easy to miss.  Here, the affected user will see clearly enough that he yet
lacks the CREATE privilege.

> Thoughts?  If we change this, should we back-patch it?  I'm inclined to
> think it's a bug (especially if the restore-ordering hazard is real)
> so we should back-patch.

Roles and their memberships will be dumped in the globals portion of
pg_dumpall, whereas ALTER DEFAULT PRIVILEGES will be dumped for individual
databases.  How might a restore-order hazard arise?  In the absence of such a
hazard, I would probably refrain from back-patching on the grounds that
there's an easily-tolerated workaround.

All that being said, each of the just-discussed variations would be valid.

Thanks,
nm

--
Noah Misch
EnterpriseDB                                 http://www.enterprisedb.com


--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
12
Loading...