Thread: pg_restore and permissions

pg_restore and permissions

From
Florian Weimer
Date:
Is there a simple way to restore a databases from a pg_dump file?
I've got a "pg_dump -F c" from version 7.1.3 and would like to restore
it on a 7.2.1 database server.

However, if I invoke, say, "pg_restore -C -d web web.db", I get the
following error message:

pg_restore: [archiver (db)] could not reconnect to database: FATAL 1:  IDENT authentication failed for user "www-data"

My pg_hba.conf looks like this:

local        all                                           ident sameuser
host         all         127.0.0.1     255.0.0.0           ident sameuser
host         all         0.0.0.0       0.0.0.0             reject

I know how to work around this problem (and did it in the past), but
I'd really like to be able to restore a database using a single
command.  Does such a command exist?

--
Florian Weimer                       Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart           http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT                          +49-711-685-5973/fax +49-711-685-5898

Re: pg_restore and permissions

From
Bruce Momjian
Date:
That should work.  Because of the error, I wonder if your OS doesn't
support local/indent authentication.  Can you psql into template1 as
that user?

---------------------------------------------------------------------------

Florian Weimer wrote:
> Is there a simple way to restore a databases from a pg_dump file?
> I've got a "pg_dump -F c" from version 7.1.3 and would like to restore
> it on a 7.2.1 database server.
>
> However, if I invoke, say, "pg_restore -C -d web web.db", I get the
> following error message:
>
> pg_restore: [archiver (db)] could not reconnect to database: FATAL 1:  IDENT authentication failed for user
"www-data"
>
> My pg_hba.conf looks like this:
>
> local        all                                           ident sameuser
> host         all         127.0.0.1     255.0.0.0           ident sameuser
> host         all         0.0.0.0       0.0.0.0             reject
>
> I know how to work around this problem (and did it in the past), but
> I'd really like to be able to restore a database using a single
> command.  Does such a command exist?
>
> --
> Florian Weimer                       Weimer@CERT.Uni-Stuttgart.DE
> University of Stuttgart           http://CERT.Uni-Stuttgart.DE/people/fw/
> RUS-CERT                          +49-711-685-5973/fax +49-711-685-5898
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: pg_restore and permissions

From
Florian Weimer
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:

> That should work.  Because of the error, I wonder if your OS doesn't
> support local/indent authentication.  Can you psql into template1 as
> that user?

Yes, I can, and I even receive an indication that I'm the database
superuser ('template1=#').

--
Florian Weimer                       Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart           http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT                          +49-711-685-5973/fax +49-711-685-5898

Re: pg_restore and permissions

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> That should work.

No, it won't because the pg_dumpall script will try to connect as each
user who owns objects in the database.  With ident authorization in
force, the DB rejects these connection requests as falsified.

SET SESSION AUTHORIZATION should have been used instead, but I doubt
Florian has any convenient way to re-do the dump file with that option.

I do not think there is any way to restore such scripts except by
temporarily suspending auth checking for local connections.  (If you
feel too paranoid to do that, you might consider tightening access
permissions on the socket file instead.)

            regards, tom lane

Re: pg_restore and permissions

From
Florian Weimer
Date:
Tom Lane <tgl@sss.pgh.pa.us> writes:

> SET SESSION AUTHORIZATION should have been used instead, but I doubt
> Florian has any convenient way to re-do the dump file with that option.

Fortunately, I can re-do the dump (I'm eager to become a professional
with a production and a testing machine ;-).

But SET SESSION AUTHORIZATION does not seem to be available in 7.2.1,
yet 7.1.3.

--
Florian Weimer                       Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart           http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT                          +49-711-685-5973/fax +49-711-685-5898

Re: pg_restore and permissions

From
Bruce Momjian
Date:
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > That should work.
>
> No, it won't because the pg_dumpall script will try to connect as each
> user who owns objects in the database.  With ident authorization in
> force, the DB rejects these connection requests as falsified.

Oh, I thought he was getting that error on first connection, but now I
understand it was part of a larger dump.

I think Tom's idea is correct that you have to open things up to get it
loaded.  Sorry about that.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: pg_restore and permissions

From
Florian Weimer
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:

>> No, it won't because the pg_dumpall script will try to connect as each
>> user who owns objects in the database.  With ident authorization in
>> force, the DB rejects these connection requests as falsified.
>
> Oh, I thought he was getting that error on first connection, but now I
> understand it was part of a larger dump.

I've created the dump with pg_dump, not with pg_dumpall.

> I think Tom's idea is correct that you have to open things up to get it
> loaded.  Sorry about that.

You mean, change the server authentication settings?  Okay, that's the
usual approach. :-/

Will it be possible some day to restore backups more easily, without
having to tweak configuration files?

--
Florian Weimer                       Weimer@CERT.Uni-Stuttgart.DE
University of Stuttgart           http://CERT.Uni-Stuttgart.DE/people/fw/
RUS-CERT                          +49-711-685-5973/fax +49-711-685-5898

Re: pg_restore and permissions

From
Tom Lane
Date:
Florian Weimer <Weimer@CERT.Uni-Stuttgart.DE> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> SET SESSION AUTHORIZATION should have been used instead, but I doubt
>> Florian has any convenient way to re-do the dump file with that option.

> Fortunately, I can re-do the dump (I'm eager to become a professional
> with a production and a testing machine ;-).

Oh, in that case you can use
    pg_dump -X use-set-session-authorization

> But SET SESSION AUTHORIZATION does not seem to be available in 7.2.1,
> yet 7.1.3.

I do not think 7.1's pg_dump had this option, but you can run 7.2's
pg_dump against the 7.1 server (in fact that is probably preferable
anyhow).

I would like to see use-set-session-authorization become the default
before 7.3 release, but haven't got around to doing anything about it.

            regards, tom lane

Re: pg_restore and permissions

From
Bruce Momjian
Date:
Tom Lane wrote:
> Florian Weimer <Weimer@CERT.Uni-Stuttgart.DE> writes:
> > Tom Lane <tgl@sss.pgh.pa.us> writes:
> >> SET SESSION AUTHORIZATION should have been used instead, but I doubt
> >> Florian has any convenient way to re-do the dump file with that option.
>
> > Fortunately, I can re-do the dump (I'm eager to become a professional
> > with a production and a testing machine ;-).
>
> Oh, in that case you can use
>     pg_dump -X use-set-session-authorization
>
> > But SET SESSION AUTHORIZATION does not seem to be available in 7.2.1,
> > yet 7.1.3.
>
> I do not think 7.1's pg_dump had this option, but you can run 7.2's
> pg_dump against the 7.1 server (in fact that is probably preferable
> anyhow).
>
> I would like to see use-set-session-authorization become the default
> before 7.3 release, but haven't got around to doing anything about it.

Certainly seems like a good idea.  TODO item?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026