> 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
> 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
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.