Re: BUG #8695: Reloading dump fails at COMMENT ON EXTENSION plpgsql

On Sun, Dec 22, 2013 at 01:56:13AM +0000, chris(at)chrullrich(dot)net wrote:
> The following bug has been logged on the website:
>
> Bug reference: 8695
> Logged by: Christian Ullrich
> Email address: chris(at)chrullrich(dot)net
> PostgreSQL version: 9.3.2
> Operating system: all
> Description:
>
> A non-superuser cannot reload any dump of a database that contains the
> plpgsql extension, because the dump unconditionally attempts to set the
> comment on that extension. This fails because plpgsql is owned by the
> superuser who installed it.
>
>
> This contradicts the manual, which says: "The dumps produced by pg_dump are
> relative to template0." The plpgsql extension is present in template0, with
> the identical comment, and therefore neither extension nor comment should be
> dumped at all. (I know this is splitting hairs, because pg_dump does not
> actually compare the subject database to template0, but still, the
> contradiction is there.)
>
>
> The extension itself is dumped as CREATE IF NOT EXISTS, so that works, but
> there is no conditional syntax for comments, and since pg_dump does not know
> whether the comment has been changed from the default, it could not use one
> anyway.
>
>
> I can think of one possible fix (aside from simply filtering that line from
> the dump): COMMENT could be a no-op if the requested comment is identical to
> the existing one.
>
>
> Another idea I had was to allow comments to be part of an extension, so that
> pg_dump would not dump them, but that does not work because pg_dump does not
> know if a comment has been changed from the original value. Not that anyone
> would ever do that.

I can reproduce this bug:

$ psql test
psql (9.4devel)
Type "help" for help.

test=> CREATE USER joe;
CREATE ROLE
test=> CREATE DATABASE test2 OWNER joe;
CREATE DATABASE
test=> \q
$ pg_dump test | psql -e -U joe test2
SET statement_timeout = 0;
SET
SET lock_timeout = 0;
SET
SET client_encoding = 'UTF8';
SET
SET standard_conforming_strings = on;
SET
SET check_function_bodies = false;
SET
SET client_min_messages = warning;
SET
CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
CREATE EXTENSION
--> COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
--> ERROR: must be owner of extension plpgsql
REVOKE ALL ON SCHEMA public FROM PUBLIC;
WARNING: no privileges could be revoked for "public"
REVOKE
REVOKE ALL ON SCHEMA public FROM postgres;
WARNING: no privileges could be revoked for "public"
REVOKE
GRANT ALL ON SCHEMA public TO postgres;
WARNING: no privileges were granted for "public"
GRANT
GRANT ALL ON SCHEMA public TO PUBLIC;
WARNING: no privileges were granted for "public"
GRANT

This would certainly cause a restore to abort for a non-super-user if
psql used --set ON_ERROR_STOP=on. Any easy way to fix this? I am not
super-excited about the suggested fixes listed above.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +