DROP ROLE dependency tracking ...

good morning,

some days ago i have fallen over an issue which feels more or less like
a bug. consider:

test=# create role xy LOGIN;
CREATE ROLE

test=# grant connect on database test to xy;
GRANT

test=# drop role xy;
ERROR: role "xy" cannot be dropped because some objects depend on it
DETAIL: access to database test

this is a totally fresh instance --- all i did was creating a db called
"test".
failing would make sense if i would the owner of an object but i fact i
don't own anything.

test=# SELECT version();

version
----------------------------------------------------------------------------------------------------------
PostgreSQL 8.4devel on x86_64-unknown-linux-gnu, compiled by GCC gcc
(GCC) 4.2.3 (Ubuntu 4.2.3-2ubuntu7)
(1 row)

is this a known issue?

many thanks,

hans

--
Cybertec Schönig & Schönig GmbH
PostgreSQL Solutions and Support
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql-support.de, www.postgresql-support.com

Hans-Juergen Schoenig wrote:

> test=# create role xy LOGIN;
> CREATE ROLE
>
> test=# grant connect on database test to xy;
> GRANT
>
> test=# drop role xy;
> ERROR: role "xy" cannot be dropped because some objects depend on it
> DETAIL: access to database test
>
> this is a totally fresh instance --- all i did was creating a db called
> "test".
> failing would make sense if i would the owner of an object but i fact i
> don't own anything.

But in some cases you might not want to lose the information associated
to the grants you've done. That's why we have REASSIGN OWNED.

Hmm, but then there's no way to lose it, even if you do want that. DROP
OWNED does not touch grants (which is per design), but we don't have
DROP ROLE CASCADE. So maybe there's a functionality gap here ...

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera wrote:
> Hans-Juergen Schoenig wrote:
>
>
>> test=# create role xy LOGIN;
>> CREATE ROLE
>>
>> test=# grant connect on database test to xy;
>> GRANT
>>
>> test=# drop role xy;
>> ERROR: role "xy" cannot be dropped because some objects depend on it
>> DETAIL: access to database test
>>
>> this is a totally fresh instance --- all i did was creating a db called
>> "test".
>> failing would make sense if i would the owner of an object but i fact i
>> don't own anything.
>>
>
> But in some cases you might not want to lose the information associated
> to the grants you've done. That's why we have REASSIGN OWNED.
>
> Hmm, but then there's no way to lose it, even if you do want that. DROP
> OWNED does not touch grants (which is per design), but we don't have
> DROP ROLE CASCADE. So maybe there's a functionality gap here ...
>
>

when thinking of REASSIGNED OWNED people tend to think about tables
rather than about CONNECT rights.
i would suggest to make DROP ROLE just kill the role unless there is a
real object depending on it.
i would not see a permission to be an object. what do you think?

many thanks,

hans

--
Cybertec Schönig & Schönig GmbH
PostgreSQL Solutions and Support
Gröhrmühlgasse 26, A-2700 Wiener Neustadt
Tel: +43/1/205 10 35 / 340
www.postgresql-support.de, www.postgresql-support.com

Hans-Juergen Schoenig wrote:

> when thinking of REASSIGNED OWNED people tend to think about tables
> rather than about CONNECT rights.
> i would suggest to make DROP ROLE just kill the role unless there is a
> real object depending on it.
> i would not see a permission to be an object. what do you think?

Yes, this might make some sense. (Keep in mind that db CONNECT
privileges were invented after REASSIGN OWNED). Perhaps we could make
exceptions -- in which case it would be good to investigate which
exceptions we need (i.e. for all object types that we support, which
ones we should be caring about and which ones we should ignore).

I'm stuck in Canuckistan for a week still, so I expect your detailed
proposal by when I get back home ;-)

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera wrote:
> Hans-Juergen Schoenig wrote:
>
> > when thinking of REASSIGNED OWNED people tend to think about tables
> > rather than about CONNECT rights.
> > i would suggest to make DROP ROLE just kill the role unless there is a
> > real object depending on it.
> > i would not see a permission to be an object. what do you think?
>
> Yes, this might make some sense. (Keep in mind that db CONNECT
> privileges were invented after REASSIGN OWNED). Perhaps we could make
> exceptions -- in which case it would be good to investigate which
> exceptions we need (i.e. for all object types that we support, which
> ones we should be caring about and which ones we should ignore).
>
> I'm stuck in Canuckistan for a week still, so I expect your detailed
> proposal by when I get back home ;-)

Where are we on this issue? The original issue was CONNECT priviledges
prevented the role from being dropped.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Alvaro Herrera wrote:
> Hans-Juergen Schoenig wrote:
>
> > when thinking of REASSIGNED OWNED people tend to think about tables
> > rather than about CONNECT rights.
> > i would suggest to make DROP ROLE just kill the role unless there is a
> > real object depending on it.
> > i would not see a permission to be an object. what do you think?
>
> Yes, this might make some sense. (Keep in mind that db CONNECT
> privileges were invented after REASSIGN OWNED). Perhaps we could make
> exceptions -- in which case it would be good to investigate which
> exceptions we need (i.e. for all object types that we support, which
> ones we should be caring about and which ones we should ignore).
>
> I'm stuck in Canuckistan for a week still, so I expect your detailed
> proposal by when I get back home ;-)

Was there any progress on this?

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Bruce Momjian wrote:
> Alvaro Herrera wrote:
> > Hans-Juergen Schoenig wrote:
> >
> > > when thinking of REASSIGNED OWNED people tend to think about tables
> > > rather than about CONNECT rights.
> > > i would suggest to make DROP ROLE just kill the role unless there is a
> > > real object depending on it.
> > > i would not see a permission to be an object. what do you think?
> >
> > Yes, this might make some sense. (Keep in mind that db CONNECT
> > privileges were invented after REASSIGN OWNED). Perhaps we could make
> > exceptions -- in which case it would be good to investigate which
> > exceptions we need (i.e. for all object types that we support, which
> > ones we should be caring about and which ones we should ignore).
> >
> > I'm stuck in Canuckistan for a week still, so I expect your detailed
> > proposal by when I get back home ;-)
>
> Was there any progress on this?

Not from my side --- and Hans-Juergen never got back to me either ...

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Added to TODO:

Allow dropping of a role that has connection rights

* http://archives.postgresql.org/pgsql-hackers/2008-05/msg00736.php

---------------------------------------------------------------------------

Hans-Juergen Schoenig wrote:
> good morning,
>
> some days ago i have fallen over an issue which feels more or less like
> a bug. consider:
>
>
> test=# create role xy LOGIN;
> CREATE ROLE
>
> test=# grant connect on database test to xy;
> GRANT
>
> test=# drop role xy;
> ERROR: role "xy" cannot be dropped because some objects depend on it
> DETAIL: access to database test
>
> this is a totally fresh instance --- all i did was creating a db called
> "test".
> failing would make sense if i would the owner of an object but i fact i
> don't own anything.
>
> test=# SELECT version();
>
> version
> ----------------------------------------------------------------------------------------------------------
> PostgreSQL 8.4devel on x86_64-unknown-linux-gnu, compiled by GCC gcc
> (GCC) 4.2.3 (Ubuntu 4.2.3-2ubuntu7)
> (1 row)
>
> is this a known issue?
>
> many thanks,
>
> hans
>
>
> --
> Cybertec Sch?nig & Sch?nig GmbH
> PostgreSQL Solutions and Support
> Gr?hrm?hlgasse 26, A-2700 Wiener Neustadt
> Tel: +43/1/205 10 35 / 340
> www.postgresql-support.de, www.postgresql-support.com
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +