Re: pg_dump in 7.2.4 with trigger functions

Tom Lane wrote:
> Steve Wampler <swampler(at)noao(dot)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(at)noao(dot)edu
The gods that smiled on your birth are now laughing out loud.