Perl - DBI and .pgpass
YES! There IS a better way.
Change between test & live servers easily.
- keep passwords in
~/.pgpass
(forpsql
&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
Put your login credentials in a file called
~/.pgpass
as per the question above.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.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.