> On 13 Mar 2018, at 4:23, matt.f...@internode.on.net wrote:
> Hi all,
> What is a reliable way to programmatically & generically populate an empty 
> schema with all the objects in the public schema as a template?
> We are using the multi tenancy ruby gem Apartment ( 
> https://github.com/influitive/apartment ), which was recently broken by the 
> changes made to pg_dump to address CVE-2018-1058 
> https://nvd.nist.gov/vuln/detail/CVE-2018-1058
> Apartment attempts to duplicate the public schema whenever creating a new 
> schema by running:
> pg_dump -s -x -0 -n public
> to get the SQL statements needed to recreate the public schema & then 
> executes the pg_dump's sql output after creating & switching to the new 
> schema ( via set search_path to <new schema>; )
> After the fix to CVE-2018-1058, all table references in pg_dump's output 
> (including within SQL of stored procedures) are prefixed by the public. 
> schema, which means you cannot just reuse this output in a different schema 
> context without first manually changing the sql.
> As a temporary fix so we can handle new customers in production, we are using 
> a regex search/replace for public. in the pg_dump output, but clearly this is 
> not a reliable solution for a generic gem such as Apartment.

In my opinion, it makes sense that if you have the option of dumping the 
contents of a specific schema, it should be possible to restore that dump into 
a different schema. Unfortunately, looking at pg_restore, there does not appear 
to be such an option (yet).

I'd even go so far to suggest that every single object type that can be dumped 
with pg_dump (single database, single schema, single table, single function, 
etc) should be restorable under a different name. I realise that this could 
make pg_restore options potentially more confusing.

I suppose people currently manually edit the dumps to this effect, but that 
risks silent corruption of data when for example a data value contains a string 
such as 'The building is now open to public.'. Regular expressions don't know 
the difference between data and identifiers in a dump file - pg_restore does.

Whether psql needs the same treatment? I'd qualify this as "advanced" use and 
limit it to pg_restore.

But then, I'm just a list-lurker, I currently have but the option of voicing my 

Alban Hertroys
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

Reply via email to