Skip site navigation (1) Skip section navigation (2)

ACLs versus ALTER OWNER

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: ACLs versus ALTER OWNER
Date: 2004-06-01 22:07:06
Message-ID: 17112.1086127626@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: 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  |               Access privileges
--------+--------+-------+------------------------------------------------
 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  |               Access privileges
--------+--------+-------+------------------------------------------------
 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

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2004-06-01 22:40:07
Subject: Nested transactions and tuple header info
Previous:From: Tom LaneDate: 2004-06-01 21:33:47
Subject: Re: Converting postgresql.conf parameters to kilobytes

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group