Re: Backup (& pg/web permissions)

> Having had a security problem, I recently backed up (with
> postgresql-dump) a database I had with group and user privileges
set
> on it.
>
> Once I dealt with the problem I tried to reload the dump with
>
> \i database.out
>
> only to find various errors to do with these privileges. Is there a
> better way to back up all data including who belongs to what
group and
> what that group is allowed to do on what tables?

pg_dumpall will dump all of your databases, along with users and
groups. This should automatically take care of restores w/users and
groups.

> Also, Is there a way of implementing a secure way of giving this data
> a web front end with a scripting language, whilst not giving too many
> permissions to user "nobody"(apache) or having clear text passwords
> in the scripts themselves?

What we do:

1) in pg_hba.conf, you can only connect to our database from
connections made on our webserver or the DBAs computer.
2) the database password is not in the perl script, but in a file
required by the perl script. (actually, the whole DBI setup is in there)

This way, in case some loser script writer screws up the permissions
of the perl script and makes the script readable by a casual user,
the password is still in the require'd file.

In any event, even if you got your hands on the password, you can
only connect from our webserver--not from any other computer
across the internet (unless you successful spoofed our server, etc.)

And, of course, we have privileges on the tables so that the web
user can do certain things (INSERTS, SELECTS, etc.) but only on
those tables that it needs.

If you use Perl, there are ways of 'encrypting' your perl script,
where the script unencrypts itself; I'm not very familiar with these,
but you can find these on CPAN.

Good luck,

--
Joel Burton, Director of Information Systems -*- jburton(at)scw(dot)org
Support Center of Washington (www.scw.org)