Postgresql Backup on Digital Ocean
Creating a read-only database user on Postgresql for all databases, on a Digital Ocean Managed Database.
The easy way doesn’t work
Postgres has a predefined role which may be used for backups
https://www.postgresql.org/docs/current/predefined-roles.html#PREDEFINED-ROLES-TABLE
Allowing an otherwise unprivileged user to read all tables.
GRANT pg_read_all_data TO backup;
Returns ERROR: must have admin option on role "pg_read_all_data"
I think this is because on the managed database you don’t actually have superuser access, and the doadmin user doesn’t have the power to grant this global access.
Grant Read Permissions per database
Assuming you have created a user called backup on the cluster (using the UI or CLI)
For each database, connect as the doadmin user
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backup;
Now you should be able to run pg_dump
Footnote : mysql
This has always been a bit easier on mysql
As a superuser run this to grant readonly access on all databases.
GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, PROCESS, RELOAD on *.* to 'backup'@'%';