> On 13 Mar 2018, at 4:23, 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. 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.
