Re: changing ownership of db

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Devrim GUNDUZ <devrim(at)gunduz(dot)org>
Cc: "Benjamin Thelen (CCGIS)" <thelen(at)ccgis(dot)de>, pgsql-admin(at)postgresql(dot)org
Subject: Re: changing ownership of db
Date: 2003-07-29 21:41:08
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-admin

Devrim GUNDUZ <devrim(at)gunduz(dot)org> writes:
> On Tue, 29 Jul 2003, Benjamin Thelen (CCGIS) wrote:
>> I would like to change the ownership of a database.

> UPDATE pg_database SET datdba=(SELECT usesysid FROM pg_shadow WHERE
> usename='new_owner') WHERE datname='db_name';

That is all you need to do --- it's the only place CREATE DATABASE
records the owner's identity.

> If you also want to change the owner of the tables, update pg_class:

> UPDATE pg_class SET relowner=(SELECT usesysid FROM pg_shadow WHERE
> usename='new_owner') WHERE relname IN (SELECT relname from
> pg_class WHERE relname NOT LIKE 'pg_%');

This is likely to be a very bad idea, especially if you give ownership
of the system tables to a non-superuser. Ownership of those tables
stays with the postgres user during a CREATE DATABASE.

regards, tom lane

In response to


Browse pgsql-admin by date

  From Date Subject
Next Message Tom Lane 2003-07-29 21:47:11 Re: 7.3.4 and OpenSSl
Previous Message Ragnar Kjrstad 2003-07-29 20:35:45 Re: Replication/Failover/HA solution