ACLs versus ALTER OWNER - Mailing list pgsql-hackers

I've noticed yet another hole in our handling of object permissions,
which is that ALTER OWNER doesn't modify the object ACL list at all.
This leads to unpleasant results.  For example, in CVS tip:

regression=# \c - alice
You are now connected as new user "alice".
regression=> create table atable (f1 int);
CREATE TABLE
regression=> grant select on atable to public;
GRANT
regression=> \z atable      Access privileges for database "regression"Schema |  Name  | Type  |       Access
privileges
--------+--------+-------+--------------------------------public | atable | table | {alice=arwdRxt/alice,=r/alice}
(1 row)
regression=> \c - postgres
You are now connected as new user "postgres".
regression=# alter table atable owner to bob;
ALTER TABLE
regression=# \c - bob
You are now connected as new user "bob".
regression=> insert into atable values(1);
ERROR:  permission denied for relation atable

Bob hasn't got insert permissions on his own table ... the ACL says so.
Well, since Bob is now the owner he can fix that:

regression=> grant all on atable to bob;
GRANT
regression=> insert into atable values(1);
INSERT 154991 1

but he's not out of the woods yet.  The ACL now looks like this:

regression=> \z atable              Access privileges for database "regression"Schema |  Name  | Type  |
Accessprivileges
 
--------+--------+-------+------------------------------------------------public | atable | table |
{alice=arwdRxt/alice,=r/alice,bob=arwdRxt/bob}
(1 row)

Alice still has all permissions, and PUBLIC still has select
permissions, and there isn't a darn thing Bob can do about it
because he didn't grant those permissions:
regression=> revoke all on atable from alice;
REVOKE
regression=> revoke all on atable from public;
REVOKE
regression=> \z atable              Access privileges for database "regression"Schema |  Name  | Type  |
Accessprivileges
 
--------+--------+-------+------------------------------------------------public | atable | table |
{alice=arwdRxt/alice,=r/alice,bob=arwdRxt/bob}
(1 row)
Even more interesting, the superuser can't fix it either, at least not
without manual hacking of the ACL entry, because any GRANT/REVOKE the
superuser issues on the object will be treated as issued by Bob.
The *only* way to get rid of those rights is to persuade Alice to revoke
them.  (Or for the superuser to revert the ownership change, revoke the
rights as-if-Alice, and then give the table back to Bob.  Blech.)

ISTM that reasonable behavior for ALTER OWNER would include doing
surgery on the object's ACL to replace references to the old owner by
references to the new owner.  A simplistic approach would just be to do
that everywhere in both the grantor and grantee fields.  If there are
existing entries mentioning the new owner then this could produce
duplicate ACL entries, which would need to be merged together.

I think there are corner cases where the merging might produce
unintuitive results, but it couldn't be as spectacularly bad as
doing nothing is.

Comments?
        regards, tom lane