Re: Locked out of schema public (pg_dump lacks backup of the grant)
Long story short:
pg_dump just forgets to backup the grant on schema public. :(
Long story:
After searching for half an hour to get some comprehensive listing
of permissions (which was in vain) I tried with pgadmin3 (which is
indeed a life-saver and still somehow works on 10.10 - and that's
the reason I am reluctant to upgrade postgres, as this can only get
worse) - and then it was a simple action of comparing page-by-page:
GRANT ALL ON SCHEMA public TO public;
That one is missing on the restored database.
So, if you do a "pg_restore -C -c -d postgres", then you get that
grant from the template database, and no problem. (But this is ugly,
as you need to find and terminate all the connections on the db.)
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.
I'd like to call this a bug.
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
Hello Tom,
thank You very much.
We improved that situation in v11, I believe. What I see for this
case these days is per commit 5955d9341:
[...]
Ah, well. I don't fully understand that, but as the iessue appears to
be known, then that is fine with me.
This thing is just bad if one never seriously worked with schemas and
has no immediate idea what could have hit - especially when that happens
on the top of a stack of open windows with other issues. :(
cheers,
PMc
Import Notes
Resolved by subject fallback