Re: Programmatically duplicating a schema

> On 13 Mar 2018, at 4:23, matt(dot)figg(at)internode(dot)on(dot)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 opinion.

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