Re: pg_dumpall and authentication

Steve Atkins wrote:
>
> On Nov 9, 2007, at 8:52 AM, Tom Hart wrote:
>
>> I'm sure you guys have heard this about 100 times, and I've done some
>> research on Google and found out some things, but I still have a
>> couple questions.
>>
>> As I'm sure you may have guessed from the subject, I'm trying to
>> schedule (under windows) pg_dumpall to run each night/morning/full
>> moon/whatever. The hitch in this is that it asks for a password for
>> each database as it dumps it. I know I can use the PGPASS environment
>> variable, or a ~/.pgpass file. What I'm wondering is what's
>> considered 'best practice' in practical applications. What solutions
>> do you guys use? Is it worth changing PGPASSFILE to point to a
>> different .pgpass?
>
> Any of those approaches should be fine. I'd probably stick with the
> default pgpass file, just for the sake of whoever may have to maintain
> it next.
>
> I tend to create a unix user just for doing backups and other
> scheduled maintenance, then give that user access to the database via
> ident authentication from the local system only. If PG-on-Windows has
> equivalent functionality that's another approach to consider.
Ok, here's what I think is going to work best for me. I'm going to
create a user with very little access rights, and then I'm going to set
up a scheduled task in windows to run pg_dumpall on the machine that
houses the db (and where the backup will be stored). On that machine
I'll have a pgpass file that I've placed somewhere where only the dummy
backup account can access it and pointed to it with the PGPASSFILE
environment variable. I think I'll be able to run a scheduled task
associated with that name without giving them login abilities.

Sound pretty solid to you guys?

BTW, this isn't protecting a ton of data, but the data itself is pretty
sensitive, so security is a concern. I appreciate your help in building
as solid a system as I can.
BTW2, I already told somebody today that Windows and security is like a
cherry pie with gravy on top, but I don't get a choice here, so try to
understand :-)