[CLUE-Tech] RE: postgres questions on pg_dump and pg_hba.conf security

[Friedman, Jason]

I've used the .pgpass file.  The sample here is with a database named "chess" and a user named "powerpull".

powerpull at love ~ $ ll .pgpass
-rw-------    1 powerpull users          41 Dec 14 04:03 .pgpass

powerpull at love ~ $ cat .pgpass
localhost:5432:chess:powerpull:putPasswordHere
host:port:dbName:userName:putPasswordHere

postgres at love ~ $ grep chess /var/lib/pgsql/data/pg_hba.conf
local   chess           postgres        md5
local   chess           powerpull       md5

When an ordinary user attempts an operation on the database:
jason at love ~ $ pg_dump -s chess
pg_dump: [archiver (db)] connection to database "chess" failed: FATAL:  No pg_hba.conf entry for host localhost, user jason, database chess

But, when an authorized (pg_hba.conf) user with a .pgpass file attempts an operation on the database:
powerpull at love ~ $ pg_dump -s chess
[ output here ]

Without the .pgpass file, a password prompt is issued.

> 
> Greetings.
> 
> I have my pg_hba.conf file set to password authenticate for a 
> single user into 
> the database used by an application.
> 
> I want to pg_dump the database each night for backup, but 
> pg_dump has no 
> password parameter.  I can easily pipe in the database user's 
> password into 
> pg_dump using an expect script.  But, this seems like a 
> security hole because 
> the password would be unecrypted.
> 
> So, my questions:
> 
> 1)  Anyone have a slick way to crypt and decrypt a password 
> to plain text?  
> I'm thinking that the expect script would read the encrypted 
> password from a 
> file, decrypt it to it's plain text and then pass it into pg_dump.
> 
> 2)  What is the security risk if I loosen my pg_hba.conf file 
> and allow 
> ident/sameuser for this user?
> 
> The main security goal is to protect the database from 
> unauthorized access, 
> which is why I tightened up the pg_hba.conf in the first 
> place.  Only Java 
> applications (via hibernate) access the database.
> 
> I appreciate any suggestions.
> 
> Jeff