Обсуждение: BUG #6116: Not able to drop user if S/he has permission on tablespace
The following bug has been logged online: Bug reference: 6116 Logged by: tushar Email address: tushar.qa@gmail.com PostgreSQL version: 9.0 Operating system: Fedora 14 Description: Not able to drop user if S/he has permission on tablespace Details: Steps to reproduce ================ \\create a directory postgres=# \! mkdir /tmp/g100 \\create a tablespace postgres=# CREATE TABLESPACE f location '/tmp/g100'; CREATE TABLESPACE \\create a User postgres=# CREATE user abc; CREATE ROLE \\grant all on tablespace to user postgres=# GRANT all on TABLESPACE f to abc; GRANT \\drop permission from user postgres=# drop owned by abc; DROP OWNED \\ Try to drop User postgres=# drop user abc ; ERROR: role "abc" cannot be dropped because some objects depend on it DETAIL: privileges for tablespace f postgres=#
On Wed, Jul 13, 2011 at 8:57 AM, tushar <tushar.qa@gmail.com> wrote: > > The following bug has been logged online: > > Bug reference: =A0 =A0 =A06116 > Logged by: =A0 =A0 =A0 =A0 =A0tushar > Email address: =A0 =A0 =A0tushar.qa@gmail.com > PostgreSQL version: 9.0 > Operating system: =A0 Fedora 14 > Description: =A0 =A0 =A0 =A0Not able to drop user if S/he has permission = on > tablespace > Details: > > Steps to reproduce > =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D > > \\create a directory > > postgres=3D# \! mkdir /tmp/g100 > > \\create a tablespace > postgres=3D# =A0CREATE TABLESPACE f location '/tmp/g100'; > CREATE TABLESPACE > > \\create a User > postgres=3D# CREATE user abc; > CREATE ROLE > > \\grant all on tablespace to user > > postgres=3D# GRANT all on TABLESPACE f to abc; > GRANT > > \\drop permission from user > > postgres=3D# drop owned by abc; > DROP OWNED > > \\ Try to drop User > postgres=3D# drop user abc ; > ERROR: =A0role "abc" cannot be dropped because some objects depend on it > DETAIL: =A0privileges for tablespace f > postgres=3D# The "DROP OWNED BY" command only drops objects that are owned by a user. It doesn't revoke privileges that user has granted: those aren't considered dropable objects. So technically speaking all of those commands are working just as expected. Nevertheless, I agree with you that the behavior here leaves a lot to be desired. Hunting down the privilege grant that is stopping you from dropping a user is pretty darn annoying. I am not sure what to do about that, though. --=20 Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Excerpts from Robert Haas's message of mié jul 20 15:11:47 -0400 2011: > The "DROP OWNED BY" command only drops objects that are owned by a > user. It doesn't revoke privileges that user has granted: those > aren't considered dropable objects. So technically speaking all of > those commands are working just as expected. > > Nevertheless, I agree with you that the behavior here leaves a lot to > be desired. Hunting down the privilege grant that is stopping you > from dropping a user is pretty darn annoying. I am not sure what to > do about that, though. The message detail indicates what's the grant that needs to be revoked. IIRC we discussed whether DROP OWNED should revoke privileges on tablespaces and databases just like it does for regular (non shared) objects, but that went nowhere and nothing got done about it. I think the expectation is that a combination of DROP OWNED and REASSIGN OWNED, when applied to sufficient databases, should be enough to let you drop a user. With that in mind, fixing this bug should be straightforward. -- Ãlvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support