Thread: dump & restore to different schema

dump & restore to different schema

From

Craig de Stigter

Date:

19 May 2011, 02:15:52

Hi list

We're writing a plugin for our website that loads single-table database dumps created by untrusted users. My question is two-fold:

1. I'm assuming that the dump format can contain arbitrary sql commands, so a pg_restore of this nature should be run under an untrusted account in its own restricted schema. Can someone confirm that this is the case?

2. The dump is presumed to contain exactly one table, which I want to load into my restricted schema. But it seems pg_restore has no option to set the schema during restoration. It always loads into the original schema as stored in the dump. How can I get around this?

I would prefer not to use s/^SET search_path.*$/SET search_path TO untrusted_schema/g if I can avoid it ;)

Thanks

Craig de Stigter

--
Koordinates Ltd
PO Box 1604, Shortland St, Auckland, New Zealand
Phone +64-9-966 0433 Fax +64-9-969 0045
Web http://www.koordinates.com

Re: dump & restore to different schema

From

Craig Ringer

Date:

19 May 2011, 02:25:47

On 05/19/2011 10:10 AM, Craig de Stigter wrote:

> 1. I'm assuming that the dump format can contain arbitrary sql commands,
> so a pg_restore of this nature should be run under an untrusted account
> in its own restricted schema. Can someone confirm that this is the case?

Correct. You very definitely want to run the restore under a
limited-privs account.

> 2. The dump is presumed to contain exactly one table, which I want to
> load into my restricted schema. But it seems pg_restore has no option to
> set the schema during restoration. It always loads into the original
> schema as stored in the dump. How can I get around this?

If you're using a table structure you defined rather than a user-defined
one you can use COPY rather than pg_restore. That way you're guaranteed
to have data and only data. They'd need to dump their table with a COPY
command, but that's pretty trivial.

--
Craig Ringer