I just noticed the following peculiar behavior.
By default, a table's owner has all access rights for it:
play=> create table foo(f1 int);CREATEplay=> insert into foo values(1);INSERT 328713 1
But let him grant some rights to someone else:
play=> grant select on foo to public;CHANGE
and all of a sudden he's not got all rights anymore.
play=> insert into foo values(1);ERROR: foo: Permission denied.
It seems this is because the ACL code has two different ideas of
"default access rights". If the table's pg_class row has a NULL acl
field, as it does initially, then the code uses a default acl
setting that isWORLD: no accessOWNER: all access
But, when you issue a GRANT and the ChangeAcl routine goes to modify
the table's ACL, if the old value is NULL then it substitutes a
*different* default as the starting point for the ACL change:WORLD: no access
so what you end up with after a grant such as the above isWORLD: read
and the poor owner has no rights except what he gave to WORLD.
Fortunately he's still the owner and can do "GRANT ALL TO himself".
But IMHO it's absolutely brain-dead that this command has to be
issued explicitly as soon as one's done any other granting or
revoking.
I think there should be just one default ACL and it should beWORLD: no accessOWNER: all access
If you do any explicit granting then that should be the starting
point.
Comments?
regards, tom lane
