Perl - DBI and .pgpass

Question

I can successfully create a connection to a Postgres db using the following:

my $settings = {
    host => 'myhost',
    db => 'mydb',
    user => 'myuser',
    passwd => 'mypasswd'
};

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

But I'm left with valuable login credentials exposed (yes, I changed them) in my Perl module. Currently, I use psql to issue queries interactively. And to save on having to remember my username/password, I have placed the credentials in a file (~/.pgpass) with permissions 600. The file looks like this:

# host:port:database:user:passwd
myhost:5432:mydb:myuser:mypasswd

How can I safely use this file ("$ENV{HOME}/.pgpass") and the DBI module to hide my credentials? Can it be done? What is best practice?

Answer 1

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 -T
use 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_test
user=hotusr_test
# localhost, no TCP nonsense needed:
host=/tmp

[live]
dbname=hotapp_live
user=hotusr_live
host=pgsql-server.example.org

~/.pgpass:

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

Answer 2

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..

Answer 3

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.