Privs

Simon Riggs <simon@2ndQuadrant.com> writes:

1. DROP OWNED BY does not drop databases owned by the role. Should it? I
would say not. This causes this strangeness

postgres=# drop owned by fred;
DROP OWNED
postgres=# drop user fred;
ERROR: role "fred" cannot be dropped because some objects depend on it
DETAIL: access to database fred

Works as expected for me:

regression=# create user fred;
CREATE ROLE
regression=# create database dd owner = fred;
CREATE DATABASE
regression=# drop owned by fred;
DROP OWNED
regression=# drop user fred;
ERROR: role "fred" cannot be dropped because some objects depend on it
DETAIL: owner of database dd
regression=#

2. REASSIGN OWNED BY cannot be executed by the role that is being
reassigned. It throws
ERROR: permission denied to reassign objects

It seems strange that you can GRANT a priv to another user, yet you
cannot REASSIGN ownership.

Why do yo think that is strange? Giving away ownership is traditionally
forbidden in most privilege systems. If you don't see why, think about
it from a cracker's perspective.

3. CREATE DATABASE syntax is OWNER = x. We might wish to deprecate that
(but not remove it) and add OWNED BY x to make the "OWNED BY" phrase
work in all places that need owners.

That just moves the inconsistency from one place to another, ie the
OWNER option of CREATE DATABASE would work differently from every
other option.

regards, tom lane

On Fri, 2010-04-02 at 10:46 -0400, Tom Lane wrote:

Simon Riggs <simon@2ndQuadrant.com> writes:

1. DROP OWNED BY does not drop databases owned by the role. Should it? I
would say not. This causes this strangeness

postgres=# drop owned by fred;
DROP OWNED
postgres=# drop user fred;
ERROR: role "fred" cannot be dropped because some objects depend on it
DETAIL: access to database fred

Works as expected for me:

regression=# create user fred;
CREATE ROLE
regression=# create database dd owner = fred;
CREATE DATABASE
regression=# drop owned by fred;
DROP OWNED
regression=# drop user fred;
ERROR: role "fred" cannot be dropped because some objects depend on it
DETAIL: owner of database dd
regression=#

Hmmm, I get that also: I can't repeat the error message I got before. Oh
well. I'll guess that the message was accurate after all.

2. REASSIGN OWNED BY cannot be executed by the role that is being
reassigned. It throws
ERROR: permission denied to reassign objects

It seems strange that you can GRANT a priv to another user, yet you
cannot REASSIGN ownership.

Why do yo think that is strange? Giving away ownership is traditionally
forbidden in most privilege systems. If you don't see why, think about
it from a cracker's perspective.

OK

I will add a few short words to both command docs to describe the
behaviour.

--
Simon Riggs www.2ndQuadrant.com