Postgres Pro
Downloads
Home   >   mailing lists

Thread: [REASSIGN confusion]

[REASSIGN confusion]

From
Michael Anderson
Date:
I'm confused about the usage (or purpose) of REASSIGN.
If I've understood the docs correctly, REASSIGN changes the ownership of
all database owned by old_role to new_role, as well as all objects on
the *currently* connected database.

It seems to me that the most common task for an administrator would be
to either:

change ownership of all objects on a single database owned by old_role
without affecting any other databases
or
change ownership of all objects on all databases owned by old_role.

As I understand it, I can't use REASSIGN to perform either of these
tasks. Or am I missing something?

Thanks,

Mike

--
--
Michael Anderson
IT Services & Support

elego Software Solutions GmbH
Gustav-Meyer-Allee 25
Building 12.3 (BIG) room 227
13355 Berlin, Germany

phone +49 30 23 45 86 96      michael.anderson at elegosoft.com
fax   +49 30 23 45 86 95      http://www.elegosoft.com

Geschaeftsfuehrer: Olaf Wagner, Sitz Berlin Amtsgericht
Berlin-Charlottenburg, HRB 77719, USt-IdNr: DE163214194

Re: [REASSIGN confusion]

From
Guillaume Lelarge
Date:
2015-12-15 14:05 GMT+01:00 Michael Anderson <michael.anderson@elegosoft.com>:
I'm confused about the usage (or purpose) of REASSIGN.

Reassign ownership to make it easy to drop a role.
 
If I've understood the docs correctly, REASSIGN changes the ownership of all database owned by old_role to new_role, as well as all objects on the *currently* connected database.


Correct.
 
It seems to me that the most common task for an administrator would be to either:

change ownership of all objects on a single database owned by old_role without affecting any other databases
or
change ownership of all objects on all databases owned by old_role.

As I understand it, I can't use REASSIGN to perform either of these tasks. Or am I missing something?


Nope, that's it. You need to execute REASSIGN on each database to change ownership of all objects on all databases.


--

Re: [REASSIGN confusion]

From
Alvaro Herrera
Date:
Guillaume Lelarge wrote:
> 2015-12-15 14:05 GMT+01:00 Michael Anderson <michael.anderson@elegosoft.com>

> > It seems to me that the most common task for an administrator would be to
> > either:
> >
> > change ownership of all objects on a single database owned by old_role
> > without affecting any other databases
> > or
> > change ownership of all objects on all databases owned by old_role.
> >
> > As I understand it, I can't use REASSIGN to perform either of these tasks.
> > Or am I missing something?
>
> Nope, that's it. You need to execute REASSIGN on each database to change
> ownership of all objects on all databases.

The reason we don't have a command to reassign all the objects in all
databases is that it's not possible to implement it inside the server:
each session is connected to one database only, and can only run
commands to modify that database.  So you need one session in each
database in order to modify the objects in that database.  But "shared"
objects can be modified in any database, so global objects (databases
and tablespaces) are reassigned in the first session.

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