Re: BUG #12843: pg_dump -O inserts four unwanted lines to the end of the dump

On Mon, Mar 9, 2015 at 09:12:42AM +0000, mage(at)mage(dot)li wrote:
> The following bug has been logged on the website:
>
> Bug reference: 12843
> Logged by: Péter Mózes Merl
> Email address: mage(at)mage(dot)li
> PostgreSQL version: 9.3.0
> Operating system: Any
> Description:
>
> It affects many versions.
>
> The manual says about -O:
>
> "To make a script that can be restored by any user, but will give that user
> ownership of all the objects, specify -O."
>
> This actually does not work because there are four lines inserted at the end
> of the dump:
>
> REVOKE ALL ON SCHEMA public FROM PUBLIC;
> REVOKE ALL ON SCHEMA public FROM postgres;
> GRANT ALL ON SCHEMA public TO postgres;
> GRANT ALL ON SCHEMA public TO PUBLIC;
>
> The problem is that the source and the target system might have different
> users. Usually they do when we deploy Rails.
>
> Not to forget that Linux distributions have different default user than the
> one created in FreeBSD (postgres vs pgsql).
>
> To load the dump by another user these four lines have to be removed. Which
> is painful when the dump is large. It is needed more often than not.
>
> I suggest simply not inserting them when -O is set.

I looked into this and -O says it controls the setting of _ownership_ of
objects, but it does not control the _permissions_ on objects. Is there
a use for not restoring permissions too?

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

+ Everyone has their own god. +