Skip site navigation (1) Skip section navigation (2)

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

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: chris(at)chrullrich(dot)net
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #8695: Reloading dump fails at COMMENT ON EXTENSION plpgsql
Date: 2014-03-31 17:00:09
Message-ID: 20140331170009.GA18559@momjian.us (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-bugs
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. +


In response to

Responses

pgsql-bugs by date

Next:From: Christian UllrichDate: 2014-03-31 20:31:39
Subject: Re: BUG #8695: Reloading dump fails at COMMENT ON EXTENSION plpgsql
Previous:From: Jeff JanesDate: 2014-03-31 15:55:47
Subject: Re: BUG #9756: Inconsistent database after OS restart

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group