Thread: pg_dump in 7.2.4 with trigger functions
I realize 7.2.4 is long in the tooth, but it's an old system that's been running for several years now. Someday we'll upgrade... However, part of the upgrade will involve dumping and restoring the tables. I've just did a little playing with pg_dump on one of the databases and discovered that I can't restore it! Is this a known problem? If so, is there a workaround? Is this operator error? If so, can someone point me to what I did wrong? I did: ==================================================================== ->pg_dump -C atst.logdb | gzip >atst.logdb.out.gz ->dropdb atst.logdb DROP DATABASE ->gunzip <atst.logdb.out.gz | psql -q ERROR: permission denied to set session authorization ERROR: permission denied for language c ERROR: must be superuser to create procedural language ERROR: permission denied to set session authorization ERROR: permission denied for schema public ERROR: permission denied to set session authorization ERROR: language "plpgsql" does not exist HINT: You need to use "createlang" to load the language into the database. ERROR: language "plpgsql" does not exist HINT: You need to use "createlang" to load the language into the database. ERROR: permission denied to set session authorization ERROR: permission denied to set session authorization ERROR: must be owner of schema public ======================================================================= I see all the permission denied messages, but why? How can a user create a dump that they cannot load back in (the user has createdb *and* createuser permissions)? It looks as though the problems are with the procedural language (the database has some trigger functions attached to one of the tables), but I have no clue what went wrong. Any suggestions would be *most* welcome! [Correct ones even more so...] Thanks! Steve -- Steve Wampler -- swampler@noao.edu The gods that smiled on your birth are now laughing out loud.
Steve Wampler wrote: > > I realize 7.2.4 is long in the tooth, but it's an old system that's been > running for several years now. Someday we'll upgrade... > > However, part of the upgrade will involve dumping and restoring the > tables. I've just did a little playing with pg_dump on one of > the databases and discovered that I can't restore it! Is this > a known problem? If so, is there a workaround? Is this operator > error? If so, can someone point me to what I did wrong? > > I did: > ==================================================================== > ->pg_dump -C atst.logdb | gzip >atst.logdb.out.gz > ->dropdb atst.logdb > DROP DATABASE > ->gunzip <atst.logdb.out.gz | psql -q ... > I see all the permission denied messages, but why? How can a user > create a dump that they cannot load back in (the user has createdb *and* > createuser permissions)? To followup: operator error. That last sentence above wasn't true. The user (me) doing the pg_dump had createdb privilege, but the owner of the database being dumped did not. After granting *that* user createdb privilege, the restore went fine. Sorry for the wasted bandwidth, maybe this will help someone else in the future... -- Steve Wampler -- swampler@noao.edu The gods that smiled on your birth are now laughing out loud.
Steve Wampler <swampler@noao.edu> writes:
> ->gunzip <atst.logdb.out.gz | psql -q
> ERROR: permission denied to set session authorization
> ERROR: permission denied for language c
> ERROR: must be superuser to create procedural language
> I see all the permission denied messages, but why? How can a user
> create a dump that they cannot load back in (the user has createdb *and*
> createuser permissions)?
Which part of "must be superuser" are you not following? The database
contains objects that were created by a superuser, and therefore the
restore will have to be run as superuser.
7.2's pg_dump may also have some problems with choosing the wrong dump
order, but there's not evidence of that here.
regards, tom lane
On Mon, Jan 31, 2005 at 09:45:16 -0700,
Steve Wampler <swampler@noao.edu> wrote:
> Steve Wampler wrote:
> >
> >I realize 7.2.4 is long in the tooth, but it's an old system that's been
> >running for several years now. Someday we'll upgrade...
There are more recent releases even within the 7.2.x series. I believe there
will be a new one announced today or tomorrow.
> >
> >However, part of the upgrade will involve dumping and restoring the
> >tables. I've just did a little playing with pg_dump on one of
> >the databases and discovered that I can't restore it! Is this
> >a known problem? If so, is there a workaround? Is this operator
> >error? If so, can someone point me to what I did wrong?
When you do you upgrade, if possible you should use a pg_dump{all} client
from the new version rather than the old version. Newer versions of
pg_dump handle dependencies better.
Tom Lane wrote:
> Steve Wampler <swampler@noao.edu> writes:
>
>>->gunzip <atst.logdb.out.gz | psql -q
>>ERROR: permission denied to set session authorization
>>ERROR: permission denied for language c
>>ERROR: must be superuser to create procedural language
>
>
>>I see all the permission denied messages, but why? How can a user
>>create a dump that they cannot load back in (the user has createdb *and*
>>createuser permissions)?
>
>
> Which part of "must be superuser" are you not following? The database
> contains objects that were created by a superuser, and therefore the
> restore will have to be run as superuser.
Thanks, Tom. I had finally figured that out (more precisely, it was
that the "run as superuser" means individual steps within the dump file,
not the person running the psql command doing the restore). The
person running the restore *was* a superuser, hence my initial
confusion. Once I granted superuser (createdb, createuser) so those
internal steps were being executed by a superuser, things went fine.
In fact, now that I look more at the dump file, it's kinda interesting.
Near the top are the lines:
SET SESSION AUTHORIZATION 'atst';
--
-- TOC entry 2 (OID 0)
-- Name: atst.logdb; Type: DATABASE; Schema: -; Owner: atst
--
CREATE DATABASE "atst.logdb" WITH TEMPLATE = template0 ENCODING =
'SQL_ASCII';
\connect "atst.logdb" atst
SET client_encoding = 'SQL_ASCII';
SET check_function_bodies = false;
SET SESSION AUTHORIZATION 'sbw';
SET search_path = public, pg_catalog;
--
-- TOC entry 20 (OID 217823)
-- Name: plpgsql_call_handler(); Type: FUNC PROCEDURAL LANGUAGE;
Schema: public; Owner: sbw
--
CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler
AS '/usr/lib/plpgsql.so', 'plpgsql_call_handler'
LANGUAGE c;
SET SESSION AUTHORIZATION DEFAULT;
('sbw' is a superuser, 'atst' was not. The restore was run by
[Linux] user 'sbw'.)
So, it looked, at first glance, that the CREATE FUNCTION was being
done by a supersuser (sbw). However, I assume that the:
SET SESSION AUTHORIZATION 'sbw';
must have failed, since the session authorization at that time
was 'atst', a non-superuser.
Does that make sense? (I'm trying to make sure I understand just
what the problem was.)
-Steve
--
Steve Wampler -- swampler@noao.edu
The gods that smiled on your birth are now laughing out loud.
Steve Wampler <swampler@noao.edu> writes:
> In fact, now that I look more at the dump file, it's kinda interesting.
> Near the top are the lines:
> \connect "atst.logdb" atst
> SET client_encoding = 'SQL_ASCII';
> SET check_function_bodies = false;
> SET SESSION AUTHORIZATION 'sbw';
> ('sbw' is a superuser, 'atst' was not. The restore was run by
> [Linux] user 'sbw'.)
Yeah, this is a bug in pg_dump --- it's failing to consider that doing
the \connect that way might lose superuser privileges. More recent
versions of pg_dump avoid that problem, but as long as you're on 7.4
the best advice may be to not rely on the -C switch.
regards, tom lane