Re: Locked out of schema public (pg_dump lacks backup of the grant) - Mailing list pgsql-general

Peter <pmc@citylink.dinoex.sub.org> writes:
> If you do only "pg_restore -c -d <db>", the sessions can stay open,
> but then it will do
> DROP SCHEMA public;
> CREATE SCHEMA public;
> and it will NOT restore the grant because it is not in the backup.

We improved that situation in v11, I believe.  What I see for this
case these days is per commit 5955d9341:

    Also, change the very ad-hoc mechanism that was used to avoid dumping
    creation and comment commands for the public schema.  Instead of hardwiring
    a test in _printTocEntry(), make use of the DUMP_COMPONENT_ infrastructure
    to mark that schema up-front about what we want to do with it.  This has
    the visible effect that the public schema won't be mentioned in the output
    at all, except for updating its ACL if it has a non-default ACL.
    Previously, while it was normally not mentioned, --clean mode would drop
    and recreate it, again causing headaches for non-superuser usage.  This
    change likewise makes the public schema less special and more like other
    built-in objects.

            regards, tom lane