From: Alvaro Herrera <alvherre@2ndquadrant.com>
To: Guillaume Lelarge <guillaume@lelarge.info>
Cc: Michael Anderson <michael.anderson@elegosoft.com>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: [REASSIGN confusion]
Date: Wed, 16 Dec 2015 10:22:00 -0300
Message-ID: <20151216132200.GG2618@alvherre.pgsql> (raw)
In-Reply-To: <CAECtzeU6UYkMiF2QgzyksWScLiUJbzGz0zVY7dKpXWUhWf3WwA@mail.gmail.com>
References: <56701006.4050004@elegosoft.com>
<CAECtzeU6UYkMiF2QgzyksWScLiUJbzGz0zVY7dKpXWUhWf3WwA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: alvherre@2ndquadrant.com, guillaume@lelarge.info, michael.anderson@elegosoft.com
Subject: Re: [REASSIGN confusion]
In-Reply-To: <20151216132200.GG2618@alvherre.pgsql>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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