Postgres Pro
Downloads
Home   >   mailing lists

Thread: Privs

Privs

From
Simon Riggs
Date:
Few minor things...

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

Now I can see the detail "access to database fred" but its not
completely clear that this means "fred still owns database fred".

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.

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.

-- Simon Riggs           www.2ndQuadrant.com

Re: Privs

From
Tom Lane
Date:
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

Re: Privs

From
Simon Riggs
Date:
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