"Colin 't Hart" <colinthart@gmail.com> writes:
> Looking through our database I find some cases of grants where grantor
> = grantee.
> Is this ever a useful thing to do? I can't imagine why.
Revoke them and you'll find out ;-)
I suppose you are referring to the object owner's own default privileges,
which are treated as being granted by herself to herself. For example:
regression=# create user alice;
CREATE ROLE
regression=# create user bob;
CREATE ROLE
regression=# \c - alice
You are now connected to database "regression" as user "alice".
regression=> create table alicestab(f1 int);
CREATE TABLE
regression=> \z alicestab
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+-----------+-------+-------------------+--------------------------
public | alicestab | table | |
(1 row)
regression=> grant select on alicestab to bob;
GRANT
regression=> \z alicestab
Access privileges
Schema | Name | Type | Access privileges | Column access privileges
--------+-----------+-------+---------------------+--------------------------
public | alicestab | table | alice=arwdDxt/alice+|
| | | bob=r/alice |
(1 row)
We can now see that alice's own permissions on the table were all granted
by herself. (Those default privileges are not shown in the first \z
command because we don't instantiate them until there's another reason to
make the table's ACL entry non-null; as long as the entry is null, the
permissions machinery will simply assume that the only granted privileges
are to the object owner. But as soon as you do any GRANT/REVOKE, the
default privileges are instantiated explicitly and now you can see them.)
A better question to ask would be when would it be sensible to *revoke*
default privileges of this sort. The canonical example is making a table
read-only to yourself (I'm not sure if there are any other use-cases that
are as compelling). That application is why we do it like this: if those
privileges didn't appear to be granted by alice, she couldn't revoke them
either. The fine print in the SQL standard says that an object owner's
own privileges on the object are granted by an abstract entity "_SYSTEM",
but Postgres doesn't do it like that because then the owner could not
revoke her own privileges.
regards, tom lane
