Thread: BUG #12843: pg_dump -O inserts four unwanted lines to the end of the dump
The following bug has been logged on the website: Bug reference: 12843 Logged by: Péter Mózes Merl Email address: mage@mage.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. Thank you.
Re: BUG #12843: pg_dump -O inserts four unwanted lines to the end of the dump
From
Bruce Momjian
Date:
On Mon, Mar 9, 2015 at 09:12:42AM +0000, mage@mage.li wrote: > The following bug has been logged on the website: > > Bug reference: 12843 > Logged by: Péter Mózes Merl > Email address: mage@mage.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@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
Bruce Momjian <bruce@momjian.us> writes: > 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? Indeed. --no-privileges seems like the answer to the OP's complaint. regards, tom lane
Re: BUG #12843: pg_dump -O inserts four unwanted lines to the end of the dump
From
Bruce Momjian
Date:
On Thu, Apr 30, 2015 at 09:20:50PM -0700, Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > 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? > > Indeed. --no-privileges seems like the answer to the OP's complaint. And --no-privileges does suppress the lines he wanted removed. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +