Re: Inconsistencies restoring public schema ownership from pg_dump

I just wanted to echo that this has also caused me problems. We just used
pg_restore and pg_dump to migrate a bunch of databases to a newer version of
postgres. Some of the databases had changed the ownership of the public
schema, and that was lost in the migration.

Maybe changing the owner of the public schema is considered a bad idea, but
our use case is that we (the postgres admins) support a group of developers
that creates many database driven apps. Most of the databases are secured,
but some are public. By letting that group own the public role (they already
own the database and all its other contents), they can decide whether to
grant or revoke permission on the public schema without granting any of them
superuser access.

Basically, what I am suggesting is that, assuming an unmodified public
schema, running these commands
pg_dump -Fc -f test.pg test
pg_restore -s test.pg | grep -i 'alter schema'

should output:
ALTER SCHEMA public OWNER TO postgres;

And of course if the owner had been changed, that would also be reflected.



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-bugs-f2117394.html