Date: Mon, 1 Jul 2013 12:38:45 -0700 (PDT)
From: David Johnston <polobo@yahoo.com>
To: pgsql-admin@postgresql.org
Message-ID: <1372707525814-5762105.post@n5.nabble.com>
In-Reply-To: <20130701173803.GI3757@eldon.alvh.no-ip.org>
References: 
 <59BF48FAC524384DA413D8F4F2E4BA6907D9A9DA@CINURCNA06.e2k.ad.ge.com>
 <1372697164049-5762060.post@n5.nabble.com>
 <20130701173803.GI3757@eldon.alvh.no-ip.org>
Subject: Re: How was I able to drop a role even though objects depend on it?
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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.


-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin