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?

Impressionable

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

Chinatown

Or even connect with my $dbh = DBI->connect("dbi:Pg:", undef, undef); via the shell environment variable export PGSERVICE=test – Chinatown

Cool answer. I expected all along that there would be a better way to perform the connection without hard-coding a lot of the connection info into the perl. I have created a ~/.pg_service.conf file with the relevant fields, but I am receiving: ERROR: service file "/etc/sysconfig/pgsql/pg_service.conf" not found. Is there a way to force the connection to look at ~/.pg_service.conf instead? – Impressionable

Actually, I found the answer myself. All I need to do is set: $ENV{'PGSYSCONFDIR'}. Thanks! – Impressionable

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

Impressionable

~/.pgpass is a feature of libpq, the postgres C client library. DBI is built on top of libpq so it just inherits its features. This is why it works with DBI and why it's not mentioned in DBI doc. – Hippomenes

It's PostgreSQL-specific, so it should not be mentioned in DBI's docs. On the other hand, it should be mentioned in DBD::Pg's, although it isn't. However, the latter does mention environment variables and a different config file format! – Whish

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.

Whish

Yeah, but surely there's a better way? I'm starting to think there isn't. – Impressionable

To simply pass the creds onto the $connection instance, without declaring them or opening up the file. – Impressionable

That makes no sense. Passing args to a function has nothing to do with the two things you mentioned. So again, I ask, better way to do what, exactly? And the follow up question is obviously going to be, better by what metric? – Whish

I do apologize for the poor explanation. However, our last comments helped lead to the solution. If you're interested in the solution, please see my answer. Thank again for your help. – Impressionable

Sorry, ikegami, I hope you don't mind, but I think Craig has the safest answer here and the least points too. Thanks again. – Impressionable

While this answer avoids hard-coding the password, sending it as a command line argument, or an environment variable, is an example of how to implement it if you are using a non-libpq PostgreSQL connection and is about as safe as using the .pgpass file from libpq, it re-invents the wheel since DBD::Pg is based on libpq. Just letting libpq deal with it's .pgpass seems like a better practice for DBD::Pg – Boyett