Обсуждение: How was I able to drop a role even though objects depend on it?
How was I able to drop a role even though objects depend on it?
От
"Wojciechowski, Robert (GE Transportation)"
Дата:
I’ve noticed a weird situation on one of our development servers running PostgreSQL 9.1.3 where roles have been dropped eventhough objects depend on that user. I didn’t think that was possible due to the dependency checks on DROP USER/ROLE.For example, there is a database that shows this connect grant to a user that was known as foo1_userA (oid 3562339547):3562339547=c/postgres This happened after a nightly database import script was run that takes in new clone databasesfrom production. After the script imports new databases another script runs that drop all users matching a regex(/^foo1_user/ for example) to clear out stale users. This obviously should succeed dropping a user that does not havedependent objects and should fail on users that do. For some this is not holding true. Is this a bug? Am I relying toomuch on DROP USER/ROLE? Any alternative approaches to clearing out users that do not have dependent objects or guidanceon debugging this situation would be much appreciated! -- Robert
Wojciechowski, Robert (GE Transportation) wrote: > I’ve noticed a weird situation on one of our development servers running PostgreSQL 9.1.3 where roles have been droppedeven though objects depend on that user. I didn’t think that was possible due to the dependency checks on DROP USER/ROLE.For example, there is a database that shows this connect grant to a user that was known as foo1_userA (oid 3562339547):3562339547=c/postgres This happened after a nightly database import script was run that takes in new clone databasesfrom production. After the script imports new databases another script runs that drop all users matching a regex(/^foo1_user/ for example) to clear out stale users. This obviously should succeed dropping a user that does not havedependent objects and should fail on users that do. For some this is not holding true. Is this a bug? Am I relying toomuch on DROP USER/ROLE? Any alternative approaches to clearing out users that do not have dependent objects or guidanceon debugging this situation would be much appreciated! -- Robert It sounds like a bug to me. Can you provide a self-contained reproducer of this? It'd be useful to know what kind of objects (should have) depended on that user, for instance. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Wojciechowski, Robert (GE Transportation) wrote > [...] there is a database that shows this connect grant to a user that was > known as foo1_userA (oid 3562339547): 3562339547=c/postgres The main user dependent situation is having said user OWNER on a database object. Simply giving a user connect privileges on a database does not make the database dependent upon said user and so removing said user remains possible. Either (or both) a more explicit description or a self-contained test case is needed to figure out if you are simply mis-understanding the situation or if there truly is a bug. The sentence I quoted above leads me to think operator-error. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-was-I-able-to-drop-a-role-even-though-objects-depend-on-it-tp5762049p5762060.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
David Johnston wrote: > Wojciechowski, Robert (GE Transportation) wrote > > [...] there is a database that shows this connect grant to a user that was > > known as foo1_userA (oid 3562339547): 3562339547=c/postgres > > The main user dependent situation is having said user OWNER on a database > object. Simply giving a user connect privileges on a database does not make > the database dependent upon said user and so removing said user remains > possible. Dropping a user which is either a database owner or has been granted CONNECT privileges is supposed to be disallowed: alvherre=# create user f; CREATE ROLE alvherre=# create database f owner f; CREATE DATABASE alvherre=# drop role f; ERROR: role "f" cannot be dropped because some objects depend on it DETALLE: owner of database f alvherre=# create role g; CREATE ROLE alvherre=# grant connect on database f to g; GRANT alvherre=# drop role g; ERROR: role "g" cannot be dropped because some objects depend on it DETALLE: privileges for database f We're supposed to have sufficient locking so that concurrent transactions don't see problems either (one xact drops the user while the other creates the database), but maybe there are bugs somewhere. -- Álvaro Herrera http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
Alvaro Herrera-9 wrote > David Johnston wrote: >> Wojciechowski, Robert (GE Transportation) wrote >> > [...] there is a database that shows this connect grant to a user that >> was >> > known as foo1_userA (oid 3562339547): 3562339547=c/postgres >> >> The main user dependent situation is having said user OWNER on a database >> object. Simply giving a user connect privileges on a database does not >> make >> the database dependent upon said user and so removing said user remains >> possible. > > Dropping a user which is either a database owner or has been granted > CONNECT privileges is supposed to be disallowed: > > alvherre=# create user f; > CREATE ROLE > alvherre=# create database f owner f; > CREATE DATABASE > alvherre=# drop role f; > ERROR: role "f" cannot be dropped because some objects depend on it > DETALLE: owner of database f > alvherre=# create role g; > CREATE ROLE > alvherre=# grant connect on database f to g; > GRANT > alvherre=# drop role g; > ERROR: role "g" cannot be dropped because some objects depend on it > DETALLE: privileges for database f > > We're supposed to have sufficient locking so that concurrent > transactions don't see problems either (one xact drops the user while > the other creates the database), but maybe there are bugs somewhere. OK. Looking at the documentation for this I see where this is stated though I read it that only direct permissions are evaluated. If foo1_userA is getting permission to connect to database "postgres" via a parent role then foo1_userA can be dropped since it would not need/have direct connect privileges but would still how up as being allowed by the system. I don't know whether 3562339547 = c/postgres would show up only in the direct case or if also via inheritance - and I'm not totally sure where this is actually being queried in the first place. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-was-I-able-to-drop-a-role-even-though-objects-depend-on-it-tp5762049p5762105.html Sent from the PostgreSQL - admin mailing list archive at Nabble.com.