Perl - DBI and .pgpass

Question

I can successfully create a connection to db Postgres 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 still have valuable login credentials (yes, I changed them) in my Perl module. I am currently using psql to interactively query queries. And to save on remembering my username / password, I put the credentials in a file (~ / .pgpass) with 600 permissions. 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 DBI to hide my credentials? It can be done? What is the practice?

Answer 1

YES! There is a better way.

Easy to change between test and live servers.

• save passwords in ~/.pgpass (for psql and pg_dump )
• other configuration information in ~/.pg_service.conf (or /etc/pg_service.conf )

eg:

 #!/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

 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 who can run a script will still be able to see credits.

Answer 3

• Put your credentials in a file named ~/.pgpass in accordance with the above question.

• To open a connection, you will need to hard-code the host, database and username. But this is normal, because at least you do not need to enter the code in the password field. This field remains hidden in your ~/.pgpass .

• Be sure to set the connection instance password field to undef .

Here is 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; 

Connections are established successfully, because for some reason, at least unknown to me, the instance is looking for the ~/.pgpass when trying to connect. I knew that there was some magic in this file, I just did not know what to do with it. Link to Doc:

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

Please note that the search for "pgpass" on this page does not return? And I refuse to read all this. Well, one day ..