Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1a9C3o-0001lL-4y for pgsql-admin@arkaria.postgresql.org; Wed, 16 Dec 2015 13:24:04 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84) (envelope-from ) id 1a9C3n-0003mT-6m for pgsql-admin@arkaria.postgresql.org; Wed, 16 Dec 2015 13:24:03 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84) (envelope-from ) id 1a9C28-0001pY-SB for pgsql-admin@postgresql.org; Wed, 16 Dec 2015 13:22:20 +0000 Received: from out.smtp-auth.no-ip.com ([8.23.224.60]) by magus.postgresql.org with esmtps (TLS1.0:DHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.84) (envelope-from ) id 1a9C1y-0006NO-Qq for pgsql-admin@postgresql.org; Wed, 16 Dec 2015 13:22:19 +0000 X-No-IP: alvh.no-ip.org@noip-smtp X-Report-Spam-To: abuse@no-ip.com Received: from alvin.alvh.no-ip.org (unknown [186.37.103.25]) (Authenticated sender: alvh.no-ip.org@noip-smtp) by smtp-auth.no-ip.com (Postfix) with ESMTPA id 004CF4009A2; Wed, 16 Dec 2015 05:22:02 -0800 (PST) Received: by alvin.alvh.no-ip.org (Postfix, from userid 1000) id 8D320A62; Wed, 16 Dec 2015 10:22:00 -0300 (CLT) Date: Wed, 16 Dec 2015 10:22:00 -0300 From: Alvaro Herrera To: Guillaume Lelarge Cc: Michael Anderson , "pgsql-admin@postgresql.org" Subject: Re: [REASSIGN confusion] Message-ID: <20151216132200.GG2618@alvherre.pgsql> References: <56701006.4050004@elegosoft.com> MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: User-Agent: Mutt/1.5.23 (2014-03-12) X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org Guillaume Lelarge wrote: > 2015-12-15 14:05 GMT+01:00 Michael Anderson > > 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