Обсуждение: backup and permissions

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?

Thanks in advance.

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

Hello Scott, thanks for your answer. I've just noticed that my first message lacked some important info.

First, this is an accounting software, and there's only one database. Almost all of the options (buttons, generally ) are stored in a set of tables, beside the database privileges needed to work properly. Permissions are assigned from the application, and they're translated internally as a list of grant/revoke commands on tables, sequences, functions and schemas. Every application user is a pgsql role with login and nosuperuser options.

Right now there are about 20 users, 3 of them with admin permissions (still regular users, but they can execute functions and modify data that others can't). They can't create, alter or drop database objects.

Doing backups will be just an option more to enable/disable and it's not likely to be a public one, just a few people will be allowed to do it. What they do with the backup file is beyond my scope, of course, but I wouldn't like to see a bunch of users having fun with the database server ;) . This is why I'm thinking of a temporary superuser privilege, or even a temporary read access to let a user execute pg_dump and pg_dumpall without being a superuser. By the way, I don't like the idea of backing up the postgres account, I might need to create a customized dump to include just the regular roles and their md5-passwords.

Maybe, as said by a scottish girl: I think I'm paranoid...

Cheers.