pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow was I able to drop a role even though objects depend on it?
5+ messages / 3 participants
[nested] [flat]
* How was I able to drop a role even though objects depend on it?
@ 2013-07-01 14:20 Wojciechowski, Robert (GE Transportation) <wojo@ge.com>
0 siblings, 2 replies; 5+ messages in thread
From: Wojciechowski, Robert (GE Transportation) @ 2013-07-01 14:20 UTC (permalink / raw)
To: pgsql-admin
I’ve noticed a weird situation on one of our development servers running PostgreSQL 9.1.3 where roles have been dropped even 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 databases from 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 have dependent objects and should fail on users that do. For some this is not holding true. Is this a bug? Am I relying too much on DROP USER/ROLE? Any alternative approaches to clearing out users that do not have dependent objects or guidance on debugging this situation would be much appreciated! -- Robert
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How was I able to drop a role even though objects depend on it?
@ 2013-07-01 16:20 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: Wojciechowski, Robert (GE Transportation) <wojo@ge.com>
1 sibling, 0 replies; 5+ messages in thread
From: Alvaro Herrera @ 2013-07-01 16:20 UTC (permalink / raw)
To: Wojciechowski, Robert (GE Transportation) <wojo@ge.com>; +Cc: pgsql-admin
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 dropped even 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 databases from 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 have dependent objects and should fail on users that do. For some this is not holding true. Is this a bug? Am I relying too much on DROP USER/ROLE? Any alternative approaches to clearing out users that do not have dependent objects or guidance on 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How was I able to drop a role even though objects depend on it?
@ 2013-07-01 16:46 David Johnston <polobo@yahoo.com>
parent: Wojciechowski, Robert (GE Transportation) <wojo@ge.com>
1 sibling, 1 reply; 5+ messages in thread
From: David Johnston @ 2013-07-01 16:46 UTC (permalink / raw)
To: pgsql-admin
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-...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How was I able to drop a role even though objects depend on it?
@ 2013-07-01 17:38 Alvaro Herrera <alvherre@2ndquadrant.com>
parent: David Johnston <polobo@yahoo.com>
0 siblings, 1 reply; 5+ messages in thread
From: Alvaro Herrera @ 2013-07-01 17:38 UTC (permalink / raw)
To: David Johnston <polobo@yahoo.com>; +Cc: pgsql-admin
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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How was I able to drop a role even though objects depend on it?
@ 2013-07-01 19:38 David Johnston <polobo@yahoo.com>
parent: Alvaro Herrera <alvherre@2ndquadrant.com>
0 siblings, 0 replies; 5+ messages in thread
From: David Johnston @ 2013-07-01 19:38 UTC (permalink / raw)
To: pgsql-admin
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-...
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2013-07-01 19:38 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-07-01 14:20 How was I able to drop a role even though objects depend on it? Wojciechowski, Robert (GE Transportation) <wojo@ge.com>
2013-07-01 16:20 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2013-07-01 16:46 ` David Johnston <polobo@yahoo.com>
2013-07-01 17:38 ` Alvaro Herrera <alvherre@2ndquadrant.com>
2013-07-01 19:38 ` David Johnston <polobo@yahoo.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox