Restore Postgres Object Privileges

I created an instance of Postgres on my local computer. I then restored my database using a db.custom file which restored all data. I then restored all roles using the globals.sql file.

However, what did not get restored was the object privileges for each role. Was my backup missing something? I am using the backup script found on the postgres web site here: Automated Backup on Linux - PostgreSQL wiki

I would restore the roles first, and THEN the datas.

OK, let me try that. Thanks.

I did try the globals first, but they didn’t port over. Any other ideas would be greatly appreciated.

I should add that my live database is postgres 10 and my local database is postgres 14. Not sure if that has anything to do with it.

When you ran pg_restore in either case did you get any errors? If so, what were they?
If not, then try the “-v” verbose option and see what gets spit out there.

1 Like

You may want to use pg_dumpall, see PostgreSQL: Documentation: 15: pg_dumpall
“pg_dumpall also dumps global objects that are common to all databases, namely database roles, tablespaces, and privilege grants for configuration parameters. (pg_dump does not save these objects.)”

So it looks like I had to give myself all privileges first and then I could apply the global.sql script without a problem.

GRANT INSERT, UPDATE, SELECT, DELETE
ON ALL TABLES IN SCHEMA public 
TO sample_user, example_user;

Thanks for all your help.