Perl - DBI and .pgpass

YES! There IS a better way.

Change between test & live servers easily.

• keep passwords in ~/.pgpass (for psql & pg_dump)
• other config info in ~/.pg_service.conf (or /etc/pg_service.conf)

e.g:

#!/usr/bin/perl -Tuse strict;use warnings;use DBI;my $dbh = DBI->connect(    #"dbi:Pg:service=live",    "dbi:Pg:service=test",    undef,    undef,    {        AutoCommit => 0,        RaiseError => 1,        PrintError => 0    }) or die DBI->errstr;

~/.pg_service.conf:

# http://www.postgresql.org/docs/9.2/static/libpq-pgservice.html# /usr/local/share/postgresql/pg_service.conf.sample# http://search.cpan.org/dist/DBD-Pg/Pg.pm#[test]dbname=hotapp_testuser=hotusr_test# localhost, no TCP nonsense needed:host=/tmp[live]dbname=hotapp_liveuser=hotusr_livehost=pgsql-server.example.org

~/.pgpass:

# http://www.postgresql.org/docs/9.2/static/libpq-pgpass.html# hostname:port:database:username:passwordlocalhost:5432:hotapp_test:hotusr_test:kq[O2Px7=g1pgsql-server.example.org:5432:hotapp_live:hotusr_live:Unm£a7D(H

1. Put your login credentials in a file called ~/.pgpass as per the question above.

2. To open a connection, you'll need to hard-code in the host, database and username. But that's ok, because at least you don't need to code in the password field. This field stays hidden in your ~/.pgpass file.

3. Make sure to set the connection instance's password field to undef.

Here's what worked for me:

my $settings = {    host => 'myhost',    db => 'mydb',    user => 'myuser'};my $connection = DBI->connect(    'DBI:Pg:dbname=' . $settings->{'db'} . ';host=' . $settings->{'host'},    $settings->{'user'},    undef,    {        RaiseError => 1,        ShowErrorStatement => 0,        AutoCommit => 0    }) or die DBI->errstr;

The connections establishes successfully because for some reason, unknown to me at least, the instance searches the ~/.pgpass file when attempting the connection. I knew there was some magic with this file, I was just unsure about what to do with it. Doc link:

http://search.cpan.org/dist/DBI/DBI.pm#data_string_diff

Notice how a search for "pgpass" on that page does not return? And I refuse to read all of it. Well, one day maybe..

open(my $fh, '<', "$ENV{HOME}/.pgpass") or die $!;my $settings;while (<>) {   chomp;   next if /^\s*(?:#.*)?\z/s;   @{$settings}{qw( host port database user passwd )} = split /:/;}die "No settings" if !$settings;

Any user capable of running the script would still be able to see the creds.