On 03/12/2018 08:23 PM, matt.figg@xxxxxxxxxxxxxxxx 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.
Is there a different way this gem could now be achieving this?
Without a flag on pg_dump to force the old format, or a CREATE SCHEMA
<schema_name> WITH TEMPLATE <other_schema> kind of statement, we can't
see any alternative more reliable than the regex search/replace.
Wild idea:
1) Roll back to:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=tree;h=c987089c48afba99df0c3c2dc45fd69238b52705;hb=2840d201c6a62beb86d9671a66eeec56183d261b
2) Use that to build pre-10.3 version of pg_dump and use that to dump
your schema. Basically long way to to force old format. Is fragile
though as that version will lose contact with changes.
Less wild idea and previously suggested upstream.
1) Check your schema objects into scripts that are checked into version
control w/o schema qualifications.
2) Use scripts to populate new schema.
The open issue on the Apartment gem for
context: https://github.com/influitive/apartment/issues/532
Thanks in advance.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
