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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ Everyone has their own god. +