pg.ddx.io pgsql-admin@postgresql.org mailing list archivehelp / color / mirror / Atom feed
[REASSIGN confusion] 3+ messages / 3 participants [nested] [flat]
* [REASSIGN confusion] @ 2015-12-15 13:05 Michael Anderson <michael.anderson@elegosoft.com> 0 siblings, 1 reply; 3+ messages in thread From: Michael Anderson @ 2015-12-15 13:05 UTC (permalink / raw) To: pgsql-admin 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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [REASSIGN confusion] @ 2015-12-16 09:47 Guillaume Lelarge <guillaume@lelarge.info> parent: Michael Anderson <michael.anderson@elegosoft.com> 0 siblings, 1 reply; 3+ messages in thread From: Guillaume Lelarge @ 2015-12-16 09:47 UTC (permalink / raw) To: Michael Anderson <michael.anderson@elegosoft.com>; +Cc: pgsql-admin 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. -- Guillaume. http://blog.guillaume.lelarge.info http://www.dalibo.com ^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: [REASSIGN confusion] @ 2015-12-16 13:22 Alvaro Herrera <alvherre@2ndquadrant.com> parent: Guillaume Lelarge <guillaume@lelarge.info> 0 siblings, 0 replies; 3+ messages in thread From: Alvaro Herrera @ 2015-12-16 13:22 UTC (permalink / raw) To: Guillaume Lelarge <guillaume@lelarge.info>; +Cc: Michael Anderson <michael.anderson@elegosoft.com>; pgsql-admin 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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin ^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2015-12-16 13:22 UTC | newest] Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2015-12-15 13:05 [REASSIGN confusion] Michael Anderson <michael.anderson@elegosoft.com> 2015-12-16 09:47 ` Guillaume Lelarge <guillaume@lelarge.info> 2015-12-16 13:22 ` Alvaro Herrera <alvherre@2ndquadrant.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox