From: | Alban Hertroys <haramrae(at)gmail(dot)com> |
---|---|
To: | matt(dot)figg(at)internode(dot)on(dot)net |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Programmatically duplicating a schema |
Date: | 2018-03-13 08:21:19 |
Message-ID: | A4ACB0CF-36A7-4A89-A34E-F3EDA3D3C718@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
> 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.
From | Date | Subject | |
---|---|---|---|
Next Message | Andy Halsall | 2018-03-13 08:48:31 | Question on corruption (PostgreSQL 9.6.1) |
Previous Message | Andreas Kretschmer | 2018-03-13 07:19:08 | Re: Logical decoding on standby |