Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id BAB5F9FB5A0 for ; Mon, 23 Apr 2007 08:43:28 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 96710-03 for ; Mon, 23 Apr 2007 08:43:20 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id D18C89FB40C for ; Mon, 23 Apr 2007 08:43:24 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.3 Received: from pont.nucli.net (pont.nucli.net [69.10.136.206]) by svr4.postgresql.org (Postfix) with ESMTP id 3A5525AFC76 for ; Mon, 23 Apr 2007 11:43:21 +0000 (GMT) Received: from 182.red-80-36-214.staticip.rima-tde.net ([80.36.214.182] helo=[192.168.1.173]) by pont.nucli.net with esmtpa (Exim 4.63) (envelope-from ) id 1Hfwwx-000114-Ic for pgsql-admin@postgresql.org; Mon, 23 Apr 2007 13:43:19 +0200 Message-ID: <462C9BD3.6090405@linuxsilo.net> Date: Mon, 23 Apr 2007 13:43:15 +0200 From: Jaume Sabater User-Agent: Icedove 1.5.0.10 (X11/20070329) MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: pg_dump and pg_restore with different owners References: <46273CDE.50401@linuxsilo.net> In-Reply-To: <46273CDE.50401@linuxsilo.net> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - pont.nucli.net X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [0 0] / [47 12] X-AntiAbuse: Sender Address Domain - linuxsilo.net X-Source: X-Source-Args: X-Source-Dir: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200704/206 X-Sequence-Number: 25270 Jaume Sabater wrote: > Hello everyone! I am resending this email to see whether I have better luck now. I've been digging the Internet with no luck for the past few days. > We have a two servers, "devel" and "live", both running PostgreSQL > 8.1.8. At the devel server there is the database "ots_devel" owned by > user "ots_devel". At the live server there is the database "ots_live" > owned by the user "ots_live". They are normal users, with no ability to > create databases or roles. I have access as superuser. > > I want to dump the database "ots_devel", then copy it to the live server > and restore it under a different name ("ots_live") and owned by a > different user "ots_live". These are the commands I am using: > > - At the devel server: > > pg_dump --oids --no-owner --format=c --file=ots_devel.bak ots_devel > > - At the live server: > > createuser --no-superuser --no-createdb --no-createrole --pwprompt ots_live > > createdb --encoding=UTF-8 --owner=ots_live ots_live > > pg_restore --no-owner --dbname=ots_live ots_devel.bak > > If I do this, there is no warning, there is no error. But the user > ots_live does not have the right permissions over the tables in the > database. He is not the owner of those objects or the permissions are > not right. > > Then I tried restoring this way: > > pg_restore --no-owner --dbname=ots_live --host=127.0.0.1 -U ots_live -W > ots_devel.bak > > But I got these errors: > > pg_restore: [archiver (db)] Error while PROCESSING TOC: > pg_restore: [archiver (db)] Error from TOC entry 3751; 0 0 COMMENT > SCHEMA public postgres > pg_restore: [archiver (db)] could not execute query: ERROR: must be > owner of schema public > Command was: > COMMENT ON SCHEMA public IS 'Standard public schema'; > pg_restore: WARNING: no privileges could be revoked > pg_restore: WARNING: no privileges could be revoked > pg_restore: WARNING: no privileges were granted > pg_restore: WARNING: no privileges were granted > WARNING: errors ignored on restore: 1 > > What I want is that user "ots_live" is the new only owner of everything > inside the database "ots_live". I think that the problem is not when > working inside the database "ots_live", but when updating the schemas, > but I don't know how to prevent that from happening. > > I've read about authorization, but I don't know how to apply it to my case. > > Any hints? Thanks in advance. > -- Jaume Sabater http://linuxsilo.net/ "Ubi sapientas ibi libertas"