Date: Mon, 1 Jul 2013 13:38:04 -0400
From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: David Johnston <polobo@yahoo.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: How was I able to drop a role even though objects depend
 on it?
Message-ID: <20130701173803.GI3757@eldon.alvh.no-ip.org>
References: 
 <59BF48FAC524384DA413D8F4F2E4BA6907D9A9DA@CINURCNA06.e2k.ad.ge.com>
 <1372697164049-5762060.post@n5.nabble.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <1372697164049-5762060.post@n5.nabble.com>
User-Agent: Mutt/1.5.21 (2010-09-15)
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

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