Programmatically duplicating a schema

Поиск
Список
Период
Сортировка
Искать
От
matt.figg@internode.on.net
Тема
Programmatically duplicating a schema
Дата
Msg-id
bf04e73a8f29eee80703d3ec7e4507e95a8a7af2@webmail.internode.on.net
Список
Дерево обсуждения
Programmatically duplicating a schema matt.figg@internode.on.net
Re: Programmatically duplicating a schema Adrian Klaver <adrian.klaver@aklaver.com>
Re: Programmatically duplicating a schema matt.figg@internode.on.net
Re: Programmatically duplicating a schema Adrian Klaver <adrian.klaver@aklaver.com>
Re: Programmatically duplicating a schema "David G. Johnston" <david.g.johnston@gmail.com>
Re: Programmatically duplicating a schema Alban Hertroys <haramrae@gmail.com>
Re: Programmatically duplicating a schema Melvin Davidson <melvin6925@gmail.com>
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. 

The open issue on the Apartment gem for context: https://github.com/influitive/apartment/issues/532

Thanks in advance.
В списке pgsql-general по дате отправления
От: Andres Freund
Дата:
От: Nikolay Samokhvalov
Дата:
FAQ