This one comes up quite regularly, so worthy of note. Whenever an application using Postgres changes or creates tables (i.e. Odoo module updates for example), those tables don’t seem to be accessible by the backups user on the next backup run. This is the SQL I use to work around it:
GRANT SELECT ON ALL TABLES IN SCHEMA public to backups;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public to backups;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backups;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO backups;
It’d be nice to find a way for the backups user to automatically get read permissions, but I’ve yet to figure that out. I understood that the above ALTER DEFAULT PRIVILEGES would cover it, but I seem to need to run it myself each time there is a change.