The following bug has been logged online:

Bug reference: 1150
Logged by: Fabien Coelho

Email address: coelho (AT) cri (DOT) 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.






---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

"PostgreSQL Bugs List" <pgsql-bugs (AT) postgresql (DOT) org> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Fabien COELHO <coelho (AT) cri (DOT) ensmp.fr> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Fabien COELHO <coelho (AT) cri (DOT) ensmp.fr> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Dear Tom,

I did not had time to go to the source code, but I thought it was likely
to be a generic bug.

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 (AT) cri (DOT) ensmp.fr

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Dear Tom,

This is exactly "General Rule 17 a) and b)" in SQL99/2003.


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 (AT) cri (DOT) ensmp.fr

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Dear Tom,

Yep.

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 (AT) cri (DOT) ensmp.fr

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Fabien COELHO <coelho (AT) cri (DOT) ensmp.fr> writes:
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Tom Lane wrote:
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.


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Dear Tom,

Ok. "identified" privilege seems to be defined in *Syntax* rule 4 and it
includes "with grant option" (section 12.2 <grant privilege statement>).

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.

Ok, but "identified" means there "those that are currently granted
by A" (12.7 <revoke statement> 9 case a).

Sorry. I sometimes tend to read what I expect instead of what is written
when reading a specs...

Well, we agree on that;-)

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 (AT) cri (DOT) ensmp.fr

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org