Thread: pg_restore and permissions
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
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
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
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
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
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
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
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
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