Programmatically duplicating a schema

From: matt(dot)figg(at)internode(dot)on(dot)net
To: pgsql-general(at)postgresql(dot)org
Subject: Programmatically duplicating a schema
Date: 2018-03-13 03:23:11
Message-ID: bf04e73a8f29eee80703d3ec7e4507e95a8a7af2@webmail.internode.on.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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 ; )
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 WITH
TEMPLATE kind of statement, we can't see any alternative more
reliable than the regex search/replace. 
The open issue on the Apartment gem for
context: https://github.com/influitive/apartment/issues/532
Thanks in advance.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Nikolay Samokhvalov 2018-03-13 03:48:43 Re: Reindex doesn't eliminate bloat
Previous Message Andres Freund 2018-03-13 01:40:41 Re: Logical decoding on standby