Postgresql 14 permissions on database objects

Application issues
1

I’m backing up postgresql 14 and the permissions on the database objects are not being backed up. When I restore a database the permissions are set to the default permissions as if I just created the database objects manually. Is there a way to backup the database permissions so that when the backup is restored, the permissions on the database objects are also restored?

2

Hi Mike_T,

Sorry, but SQLBackupAndFTP can’t dump privileges and users. Users are stored at the DBMS level, and we only dump a single database.

If you have any other questions, please let us know.

Thank you and sorry for the inconvenience.

4

Ok thanks. You might want to add this feature and offer it to upgraded users. Here are a couple of scripts your developers can use to get them started if they are interested in doing that.

SELECT grantee
,table_catalog
,table_schema
,table_name
,string_agg(privilege_type, ', ’ ORDER BY privilege_type) AS privileges
FROM information_schema.role_table_grants
WHERE grantee != ‘postgres’
GROUP BY grantee, table_catalog, table_schema, table_name;

select grantee, routine_name,* from information_schema.role_routine_grants rrg where grantee not in (‘postgres’, ‘PUBLIC’, ‘pg_monitor’);

5

Hi Mike_T,

Thank you for your reply and for all the details. We have forwarded the details to our dev team.

Thank you and sorry for the inconvenience.