Thread: [BUGS] BUG #14540: REASSIGN OWNED cannot reassign from a USER to a ROLE

The following bug has been logged on the website:

Bug reference:      14540
Logged by:          Olivier DUPUY
Email address:      opldupuy@gmail.com
PostgreSQL version: 9.6.1
Operating system:   Linux
Description:

I have a database 'owned' by a role.
Users within this role create schemas, tables... in this database. I have
not much control on who does that (frameworks) but ideally, all these
resources should belong to the role.
Our database 'users' have a limited lifetime (Vault).
When a user is dropped, I want all his owned resources to be returned to the
role.

REASSIGN ROLE
https://www.postgresql.org/docs/current/static/sql-reassign-owned.html can
reassign bud sadly it is only from role A to role B. The documentation lets
no doubt about that.

Finding manually all the possible owned resources then reassign them is a
complex task.
When you DROP USER the operation fails if this user still owns something so
the ownership information is at hand already.

I highly suggest extending REASSIGN OWNED to:
- reassign USER resources to ROLE first (current user has to be in the
target role as now)
- reassign USER resources to USER next
I don't see a use case for ROLE to USER but someone may have the need too.

Thanks
Olivier



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

opldupuy@gmail.com wrote:

> I highly suggest extending REASSIGN OWNED to:
> - reassign USER resources to ROLE first (current user has to be in the
> target role as now)
> - reassign USER resources to USER next

In Postgres, "users" are just roles.  There is no distinction.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


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