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.
