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.

Search Discussions

  • Steve Wampler at Jan 31, 2005 at 4:49 pm

    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.
  • Bruno Wolff III at Jan 31, 2005 at 5:08 pm

    On Mon, Jan 31, 2005 at 09:45:16 -0700, Steve Wampler 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 at Jan 31, 2005 at 4:55 pm

    Steve Wampler 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
  • Steve Wampler at Jan 31, 2005 at 5:33 pm

    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.
  • Tom Lane at Jan 31, 2005 at 5:59 pm

    Steve Wampler 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedJan 31, '05 at 3:09p
activeJan 31, '05 at 5:59p
posts6
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase