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.
