Re: Port Reports: UnixWare/Failure/Priviledge Test

In addition to the -g issue, I get the following failure:

*** ./expected/privileges.out Thu Oct 9 20:49:31 2003
--- ./results/privileges.out Fri Oct 24 14:07:18 2003
***************
*** 247,253 ****
(1 row)

CREATE FUNCTION testfunc3(int) RETURNS int AS 'select 2 * $1;' LANGUAGE
sql; -- fail
- ERROR: permission denied for language sql
SET SESSION AUTHORIZATION regressuser3;
SELECT testfunc1(5); -- fail
ERROR: permission denied for function testfunc1
--- 247,252 ----

======================================================================

with 7.4CVS.

$ uname -a
UnixWare lerami 5 7.1.3 i386 x86at SCO UNIX_SVR5
$

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Okay, the cause of the permissions regression failure is this:

Larry is running the regression tests as a superuser, but not as the
original postgres superuser. This means that when the privileges
regression test does

REVOKE ALL PRIVILEGES ON LANGUAGE sql FROM PUBLIC;

nothing happens, because the revoke is implicitly assumed to mean
"revoke whatever privileges I granted", and Larry's superuser hasn't
granted any. The public privileges on language SQL were granted by
user postgres, and they remain in force. So the later CREATE FUNCTION
that the test expects to fail, succeeds.

Is this a bug, or is it correct-per-spec behavior? It's surely likely
to confuse people. I wonder whether superusers shouldn't be allowed to
revoke privileges granted by other people. As the code stands, they
cannot.

If it isn't a bug, I think we'll have to document that the privileges
regression test fails when you run it as a non-original superuser. Ugh.

I've also found some corner-case bugs in ACL manipulation that arise
from the fact that Peter changed the code to allow zero-length ACL
arrays; seems he missed one or two consequences of that change. Will
fix these, but it doesn't affect the main issue.

regards, tom lane

--On Wednesday, October 29, 2003 15:26:39 -0500 Tom Lane
<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
[snip]
> Is this a bug, or is it correct-per-spec behavior? It's surely likely
> to confuse people. I wonder whether superusers shouldn't be allowed to
> revoke privileges granted by other people. As the code stands, they
> cannot.
It seems to me that a superuser SHOULD be able to affect ANY permissions on
ANY object in the DB.

[snip]

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Larry Rosenman <ler(at)lerctr(dot)org> writes:
> --On Wednesday, October 29, 2003 15:26:39 -0500 Tom Lane=20
> <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> [snip]
>> Is this a bug, or is it correct-per-spec behavior? It's surely likely
>> to confuse people. I wonder whether superusers shouldn't be allowed to
>> revoke privileges granted by other people. As the code stands, they
>> cannot.

> It seems to me that a superuser SHOULD be able to affect ANY permissions on
> ANY object in the DB.

Well, of course a superuser can do SET SESSION AUTHORIZATION to "become"
the other person, and then execute GRANT or REVOKE commands to update
the permissions as he wishes. This seems reasonable for the GRANT case
(otherwise we'd need to add a clause to GRANT to specify which userid to
grant the permissions as). For REVOKE, though, I'm wondering if a
superuser-issued REVOKE shouldn't revoke the specified permissions
regardless of who granted them.

An alternative, possibly cleaner approach is that a superuser-issued
GRANT or REVOKE should be executed as though it were issued by the
object owner. This would mean that all privileges ultimately flow from
the object owner, which seems reasonable intuitively. Right now, you
can have a situation where some privileges on an object are granted by
the owner and some are granted by various random superusers. Not sure
that that is a good idea.

regards, tom lane

--On Wednesday, October 29, 2003 15:49:53 -0500 Tom Lane
<tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Larry Rosenman <ler(at)lerctr(dot)org> writes:
>> --On Wednesday, October 29, 2003 15:26:39 -0500 Tom Lane=20
>> <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> [snip]
>>> Is this a bug, or is it correct-per-spec behavior? It's surely likely
>>> to confuse people. I wonder whether superusers shouldn't be allowed to
>>> revoke privileges granted by other people. As the code stands, they
>>> cannot.
>
>> It seems to me that a superuser SHOULD be able to affect ANY permissions
>> on ANY object in the DB.
>
> Well, of course a superuser can do SET SESSION AUTHORIZATION to "become"
> the other person, and then execute GRANT or REVOKE commands to update
> the permissions as he wishes. This seems reasonable for the GRANT case
> (otherwise we'd need to add a clause to GRANT to specify which userid to
> grant the permissions as). For REVOKE, though, I'm wondering if a
> superuser-issued REVOKE shouldn't revoke the specified permissions
> regardless of who granted them.
I like this idea....
>
> An alternative, possibly cleaner approach is that a superuser-issued
> GRANT or REVOKE should be executed as though it were issued by the
> object owner. This would mean that all privileges ultimately flow from
> the object owner, which seems reasonable intuitively. Right now, you
> can have a situation where some privileges on an object are granted by
> the owner and some are granted by various random superusers. Not sure
> that that is a good idea.
I like this even better. I don't like the fact that right now some
superusers are different from other superusers.

IMO, of course....

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: ler(at)lerctr(dot)org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Tom Lane writes:

> nothing happens, because the revoke is implicitly assumed to mean
> "revoke whatever privileges I granted", and Larry's superuser hasn't
> granted any. The public privileges on language SQL were granted by
> user postgres, and they remain in force. So the later CREATE FUNCTION
> that the test expects to fail, succeeds.
>
> Is this a bug, or is it correct-per-spec behavior?

It's correct.

--
Peter Eisentraut peter_e(at)gmx(dot)net

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> Tom Lane writes:
>> nothing happens, because the revoke is implicitly assumed to mean
>> "revoke whatever privileges I granted", and Larry's superuser hasn't
>> granted any. The public privileges on language SQL were granted by
>> user postgres, and they remain in force. So the later CREATE FUNCTION
>> that the test expects to fail, succeeds.
>>
>> Is this a bug, or is it correct-per-spec behavior?

> It's correct.

After chewing on it further, I decided that the spec is unable to
provide any useful guidance, because it hasn't got the concept of
superuser. It is however clear that having superusers generate their
own grants to someone else's object is not within the privilege model of
the spec. I think the solution I applied this afternoon (pretend that
superusers are the object owner for GRANT/REVOKE purposes) is a
reasonable answer.

regards, tom lane