Received: from water.oasis.net.au (water.oasis.net.au [210.8.139.2]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g164vXE05608 for ; Tue, 5 Feb 2002 23:57:34 -0500 (EST) (envelope-from rudi@oasis.net.au) Received: from sun (palm.oasis.net.au [210.8.139.3]) by water.oasis.net.au (8.9.3/100.1) with SMTP id JAA14614 for ; Wed, 6 Feb 2002 09:00:49 +1000 X-Authentication-Warning: water.oasis.net.au: Host palm.oasis.net.au [210.8.139.3] claimed to be sun Message-ID: <006d01c1ae97$50994040$0c00a8c0@sun> From: "Rudi" To: References: <002601c1adf5$24a37750$0c00a8c0@dev1> <20020205152230.GB23826@media-support.de> Subject: Re: Database ownership Date: Wed, 6 Feb 2002 08:49:05 +1000 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200202/63 X-Sequence-Number: 6493 Michael, Thank you kindly - exactly what I was after. Cheers Rudi. ----- Original Message ----- From: "Michael Lamertz" To: "Rudi" Cc: Sent: Wednesday, February 06, 2002 1:22 AM Subject: Re: [SQL] Database ownership > On Tue, Feb 05, 2002 at 01:28:12PM +1000, Rudi wrote: > > Hi, > > > > I've been searching without any joy so far so I thought I'd ask. > > Can I change the ownership of a database after the database is created ? > > Or do I need to drop the database and recreate it using the new database owner account ? > > Nope, > > \d pg_database and pg_shadow. pg_database.datdba is a reference to > pg_shadow.usesysid. > > Find out who's the owner: > select a.datname, b.usename from pg_database a, pg_shadow b > where a.datdba = b.usesysid; > > Update the owner: > update pg_database > set datdba = (select usesysid from pg_shadow where usename = 'SOMEUSERHERE') > where datname = 'SOMEDATABASEHERE'; > > That should do the trick. > > -- > Michael Lamertz | mike@lamertz.net > Nordstr. 49 | http://www.lamertz.net - http://www.perl-ronin.de > 50733 Cologne | Priv: +49 221 445420 > Germany | Mobile: +49 171 6900 310