Обсуждение: BUG #1150: grant options not properly checked
The following bug has been logged online:
Bug reference: 1150
Logged by: Fabien Coelho
Email address: coelho@cri.ensmp.fr
PostgreSQL version: 7.5 Dev
Operating system: Linux
Description: grant options not properly checked
Details:
It seems that GRANT ALL ON SCHEMA does not properly
check for grantor rights. I haven't tested other
grantable stuff...
repeat:
psql> SELECT CURRENT_USER;
current_user
--------------
hobbes
psql> SELECT * FROM pg_namespace WHERE nspname='foo';
nspname | nspowner | nspacl
---------+----------+----------------------------------------
foo | 101 | {calvin=U*C*/calvin,hobbes=U*C/calvin}
As hobbes, I should only be able to grant usage.
However:
psql> GRANT ALL ON SCHEMA foo TO suzy;
psql> SELECT * FROM pg_namespace WHERE nspname='foo';
nspname | nspowner | nspacl
---------+----------+-------------------------------------------------------
foo | 101 | {calvin=U*C*/calvin,hobbes=U*C/calvin,suzy=UC/hobbes}
Suzy has both Usage and Create rights on schema foo,
although hobbes had only a grant option for usage.
"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> It seems that GRANT ALL ON SCHEMA does not properly
> check for grantor rights.
What's happening is that pg_namespace_aclcheck() allows the operation
if you have GRANT OPTION for *any* of the rights to be granted. The
same problem exists for all object types.
I am not sure whether we should refuse the operation or just narrow
the set of privileges to those that are grantable per GRANT OPTION.
Peter, any thoughts?
regards, tom lane
Fabien COELHO <coelho@cri.ensmp.fr> writes:
> I'm not Peter, but I have an answer anyway: the standard says it should be
> narrowed.
Hmm, and also 12.2 says:
8) For every combination of <grantee> and <action> on O specified
in <privileges>, if there is no corresponding privilege
descriptor in CPD, then a completion condition is raised:
warning - privilege not granted.
9) If ALL PRIVILEGES was specified, then for each grantee G, if
there is no privilege descriptor in CPD specifying grantee G,
then a completion condition is raised: warning - privilege not
granted.
Note that says WARNING, not ERROR. So I guess what we need to do is
narrow the privilege set and issue a warning message.
I think this also bears on the question that was raised before about
whether REVOKE should raise an error if you don't have the right to
revoke the privileges you're listing. We don't, and based on this
I think we shouldn't --- but maybe we should issue a warning.
regards, tom lane
Fabien COELHO <coelho@cri.ensmp.fr> writes:
> While browsing the Access Rules of <revoke statement>... it is unclear.
I generally find SQL92 easier to read than SQL99. In SQL92,
section 11.37 <revoke statement> says
8) For every combination of <grantee> and <action> on O specified
in <privileges>, if there is no corresponding privilege de-
scriptor in the set of identified privilege descriptors, then a
completion condition is raised: warning-privilege not revoked.
9) If ALL PRIVILEGES was specified, then for each <grantee>, if
no privilege descriptors were identified, then a completion
condition is raised: warning-privilege not revoked.
which seems parallel to the GRANT case: warning, but no error.
regards, tom lane
Dear Tom, > > It seems that GRANT ALL ON SCHEMA does not properly > > check for grantor rights. > > What's happening is that pg_namespace_aclcheck() allows the operation > if you have GRANT OPTION for *any* of the rights to be granted. The > same problem exists for all object types. I did not had time to go to the source code, but I thought it was likely to be a generic bug. > I am not sure whether we should refuse the operation or just narrow > the set of privileges to those that are grantable per GRANT OPTION. > Peter, any thoughts? I'm not Peter, but I have an answer anyway: the standard says it should be narrowed. ISO/IEC 9075-2:2003 (E) 12.3 <privileges> ... Syntax Rules 1) ALL PRIVILEGES is equivalent to the specification of all of the privileges on <object name> for which the <grantor> has grantable privilege descriptors. -- Fabien Coelho - coelho@cri.ensmp.fr
Dear Tom, > ... > Note that says WARNING, not ERROR. So I guess what we need to do is > narrow the privilege set and issue a warning message. Yep. > I think this also bears on the question that was raised before about > whether REVOKE should raise an error if you don't have the right to > revoke the privileges you're listing. We don't, and based on this > I think we shouldn't --- but maybe we should issue a warning. There are two close but different issues. (1) REVOKE ALL ON SCHEMA foo FROM calvin; I agree with you that it looks it is allowed, as narrow would mean empty. I really think a warning is desirable in such a case... (2) REVOKE USAGE ON SCHEMA foo FROM calvin; Where USAGE (or any specific right) is not grantable by the issuer. While browsing the Access Rules of <revoke statement>... it is unclear. I guess maybe a "grantable" word is missing in my version of the standard, because otherwise I cannot really extract a semantics from access rule 1 case a in 12.7. Case b is much more explicit in my version for <revoke role statement>, you need a "WITH ADMIN OPTION". If my guess is correct and that an access rule is violated, then this case should result in an error. -- Fabien Coelho - coelho@cri.ensmp.fr
Dear Tom,
> section 11.37 <revoke statement> says
>
> 8) For every combination of <grantee> and <action> on O specified
> in <privileges>, if there is no corresponding privilege de-
> scriptor in the set of identified privilege descriptors, then a
> completion condition is raised: warning-privilege not revoked.
>
> 9) If ALL PRIVILEGES was specified, then for each <grantee>, if
> no privilege descriptors were identified, then a completion
> condition is raised: warning-privilege not revoked.
This is exactly "General Rule 17 a) and b)" in SQL99/2003.
> which seems parallel to the GRANT case: warning, but no error.
I do not understand it that way.
(1) I think that the "General Rules" apply ONLY IF the "Access Rules" are
already fulfilled, that is I MUST have the grant option of the rights
before going there?!
(2) thus what I understand from the above extract is that if I revoke a
right that was not granted before, then I must issue a warning. Fine.
This is different from trying to revoke a right without having the
grant option, what is still an error because it should violate access
rules, IMHO.
However I think that the above warning would is useful, because it tells
you that something maybe get wrong in a REVOKE.
Only the empty case (GRANT ALL... although I have nothing grantable)
would "only" result in a warning, as It does not violates the access
rules directly, so the general rules would apply on an empty set.
--
Fabien Coelho - coelho@cri.ensmp.fr
Fabien COELHO <coelho@cri.ensmp.fr> writes:
> I do not understand it that way.
> (1) I think that the "General Rules" apply ONLY IF the "Access Rules" are
> already fulfilled, that is I MUST have the grant option of the rights
> before going there?!
I looked at this more carefully. In both SQL92 and SQL99, the only
Access Rule for GRANT is
1) The applicable privileges shall include a privilege identifying
O.
Here "O" is the target object, and "applicable privileges" is all the
privileges held by the current user.
Now, that says "a privilege", not "the privilege to be granted", nor
even "a privilege with grant option". As near as I can tell, what the
spec wants is that GRANT should raise error if the issuing user has no
privileges at all for the target object, but as long as he has at least
one privilege bit, he gets past the Access Rule --- whether or not that
bit has anything to do with the privilege bits to be granted.
After that, you get to the General Rules, which pretty clearly say that
trying to grant privileges you don't have grant option for is just a
warning and not an error condition. (Such privileges will not be in the
set of "identified privilege descriptors".)
AFAICS the specification for REVOKE is exactly parallel.
So the existing code is still wrong, but not in quite the way we thought.
I'd be the first to say that this aspect of the spec is a tad bizarre.
Does anyone want to argue for ignoring the spec and implementing "saner"
behavior? It's not like we are super close to spec compliance for
privileges otherwise ...
regards, tom lane
Tom Lane wrote: > After that, you get to the General Rules, which pretty clearly say > that trying to grant privileges you don't have grant option for is > just a warning and not an error condition. (Such privileges will not > be in the set of "identified privilege descriptors".) > > AFAICS the specification for REVOKE is exactly parallel. > > So the existing code is still wrong, but not in quite the way we > thought. > > I'd be the first to say that this aspect of the spec is a tad > bizarre. Does anyone want to argue for ignoring the spec and > implementing "saner" behavior? It's not like we are super close to > spec compliance for privileges otherwise ... My experience is that violating the spec in this area just leads to other problems. Does anyone know what IBM and Oracle do here? AFAICT, they're the ones writing the specs.
Dear Tom, > I looked at this more carefully. In both SQL92 and SQL99, the only > Access Rule for GRANT is > > 1) The applicable privileges shall include a privilege identifying > O. > > Here "O" is the target object, and "applicable privileges" is all the > privileges held by the current user. > > Now, that says "a privilege", not "the privilege to be granted", nor > even "a privilege with grant option". Ok. "identified" privilege seems to be defined in *Syntax* rule 4 and it includes "with grant option" (section 12.2 <grant privilege statement>). > As near as I can tell, what the spec wants is that GRANT should raise > error if the issuing user has no privileges at all for the target > object, but as long as he has at least one privilege bit, he gets past > the Access Rule --- whether or not that bit has anything to do with the > privilege bits to be granted. Ok, I understand it finally. If it is *empty*, then it is an error, but if it is not empty, then it proceeds with grantable privileges and warns about ungrantable ones. What you say above. > AFAICS the specification for REVOKE is exactly parallel. Ok, but "identified" means there "those that are currently granted by A" (12.7 <revoke statement> 9 case a). > So the existing code is still wrong, but not in quite the way we thought. Sorry. I sometimes tend to read what I expect instead of what is written when reading a specs... > I'd be the first to say that this aspect of the spec is a tad bizarre. Well, we agree on that;-) > Does anyone want to argue for ignoring the spec and implementing "saner" > behavior? It's not like we are super close to spec compliance for > privileges otherwise ... I think that if pg really sticks to the spec as I finally understand it with your help, it would not be bad. I mean with both error (empty set in grant or revoke) and warnings (non grantable parts for grant, non granted parts for revoke). Thanks for your help, -- Fabien Coelho - coelho@cri.ensmp.fr
Dear Peter, > > I'd be the first to say that this aspect of the spec is a tad > > bizarre. Does anyone want to argue for ignoring the spec and > > implementing "saner" behavior? It's not like we are super close to > > spec compliance for privileges otherwise ... > > My experience is that violating the spec in this area just leads to > other problems. Does anyone know what IBM and Oracle do here? > AFAICT, they're the ones writing the specs. If we agree about the last interpretation of the Access Rule, that is on the definition of an "identified" privilege (see my previous mail), I finally think that the specs is ok and it just need to be implemented. The bizarre would be more in the way that things are explained in the spec than what is specified. Have a nice day, -- Fabien Coelho - coelho@cri.ensmp.fr