Re: BUG #8695: Reloading dump fails at COMMENT ON EXTENSION plpgsql - Mailing list pgsql-bugs

On Sun, Dec 22, 2013 at 01:56:13AM +0000, chris@chrullrich.net wrote:
> The following bug has been logged on the website:
>
> Bug reference:      8695
> Logged by:          Christian Ullrich
> Email address:      chris@chrullrich.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@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +