On Thu, Nov 13, 2008 at 5:30 PM, Fernando Moreno <azazel.7@xxxxxxxxx> 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. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
