Hi, I'm working on a little backup utility for a desktop application. It's going to execute pg_dumpall (-r) and pg_dump, but first I have to deal with the permissions needed to do that:
1. Users (pgsql roles) enabled to backup would be superusers all the time. This sounds insecure.
2. Users will get superuser access through a security definer function just before the backup, then they'll be nosuperuser again. An interrupted backup process would be dangerous, but I could check whether or not this clause is enabled, every time a user connects. Still risky.
3. Users will just be able to read every object in the database, and pg_authid. I've done some tests and this seems enough.
I need some advice to choose the better/safer option, what would you do?