Thread: ALTER DEFAULT PRIVILEGES FOR ROLE is broken
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
> ... 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
Josh Berkus <josh@agliodbs.com> 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
> 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
On Sun, Apr 28, 2013 at 9:39 PM, Josh Berkus <josh@agliodbs.com> 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
> 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
Josh Berkus <josh@agliodbs.com> 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
On 04/29/2013 09:59 AM, Tom Lane wrote: > Josh Berkus <josh@agliodbs.com> 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
Josh Berkus <josh@agliodbs.com> 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 webDETAIL: This operation requires role X to have privilege Y.
Is there any scenario where this'd be exposing too much info?
regards, tom lane
> 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
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
On Mon, Apr 29, 2013 at 01:25:47PM -0400, Tom Lane wrote: > Josh Berkus <josh@agliodbs.com> 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
Noah Misch <noah@leadboat.com> 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
> 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
I wrote:
> Noah Misch <noah@leadboat.com> 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
* Tom Lane (tgl@sss.pgh.pa.us) 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
Stephen Frost <sfrost@snowman.net> writes:
> * Tom Lane (tgl@sss.pgh.pa.us) 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
* Tom Lane (tgl@sss.pgh.pa.us) 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
On Fri, Jun 7, 2013 at 12:44 PM, Stephen Frost <sfrost@snowman.net> wrote: > * Tom Lane (tgl@sss.pgh.pa.us) 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
On Fri, Jun 07, 2013 at 12:26:59PM -0400, Tom Lane wrote: > I wrote: > > Noah Misch <noah@leadboat.com> 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
Noah Misch <noah@leadboat.com> writes:
> On Fri, Jun 07, 2013 at 12:26:59PM -0400, Tom Lane 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".
> Seems fine. I might have instead changed it to a test of the caller's
> permissions.
I thought a bit about that, but it seems rather unrelated to the
eventual use of the privileges.
> 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.
There is also a check that the user doing the ALTER is a member of the
role being targeted, so it's not like unprivileged users would have free
rein to do anything at all here.
> 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.
True. The majority position seems to be for no NOTICE, and I'm fine
with that.
>> 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?
The issue is that the A.D.P. must come out after a grant of CREATE
privileges on the schema. After looking at the code, I think pg_dump
does get this right (assuming that there actually are relevant CREATE
privileges granted at the time of dump), but it still seems rather
fragile and surprising.
regards, tom lane
On Fri, Jun 07, 2013 at 02:49:37PM -0400, Tom Lane wrote: > Noah Misch <noah@leadboat.com> writes: > > On Fri, Jun 07, 2013 at 12:26:59PM -0400, Tom Lane 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". > > > Seems fine. I might have instead changed it to a test of the caller's > > permissions. > > I thought a bit about that, but it seems rather unrelated to the > eventual use of the privileges. Fair enough. > > 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? > > The issue is that the A.D.P. must come out after a grant of CREATE > privileges on the schema. Oh, true. The facts I called out there were inapplicable. -- Noah Misch EnterpriseDB http://www.enterprisedb.com
>> Agreed. > > Seems reasonable. Yaaaay! -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com
On 6/7/13 12:57 PM, Tom Lane 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. Seems like a can of worms to me. There are many other cases where you need to do something else in order to make the thing you just did useful.