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

On Mon, Mar 31, 2014 at 01:00:09PM -0400, Bruce Momjian wrote:
>     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.

In looking at this further, I wonder why we don't filter out pg_catalog
extensions when we dump them;  right now we use:

    appendPQExpBufferStr(query, "SELECT x.tableoid, x.oid, "
                         "x.extname, n.nspname, x.extrelocatable, x.extversion, x.extconfig, x.extcondition "
                         "FROM pg_extension x "
                         "JOIN pg_namespace n ON n.oid = x.extnamespace");

I see a filter on pg_catalog for pg_proc.  Is there a reason there isn't
one for pg_extension?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +