Re: backup and permissions

On Thu, Nov 13, 2008 at 5:30 PM, Fernando Moreno <azazel(dot)7(at)gmail(dot)com> wrote:
> 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.

So, letting a user have all your data, but no power over the database
is somehow more secure? I kinda get your point but wouldn't go so
far as to call it insecure to require a superuser to do backups.
Plus, any user who owns a db can back it up. So, you can always have
individual user accounts backup individual databases. Keep in mind
pg_dumpall backs up things like user accounts as well. You don't want
tom dick and harry backing up user accounts do you?

> 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.

Sounds like a lot of work to avoid having users just back up
individual databases they have permissions on.

> 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?

Backup with a superuser. Or split the backups to users who own their
own databases.