Trouble Connecting to PostgreSQL with DBD::Pg

[vendion]

I'm trying to use DBD::Pg module to connect to a PostgreSQL database, but it keeps giving me an Ident error.

DBI connect('database=Repairs;host=localhost;port=5432','',...) failed: FATAL: Ident authentication failed for user "vendion" at TickItAdmin.pl line 73

This is what I am passing to the module as my connect statement.

my $dsn = "DBI:Pg:database=Repairs;host=localhost;port=5432";
 my $dbh = DBI->connect( $dsn, '', '', { AutoCommit => 0,  RaiseError 
+=> 1 } )
    || croak "Couldn't authenticate to the Database: $OS_ERROR";

From my understanding from the documentation of the module passing '' as the username and password it will use the active user's username. I do have a ROLE setup for the username I am using and I know it works,

vendion@linux-9rf6:~> psql Repairs
psql (8.4.5)
Type "help" for help.

Repairs=>

Has anyone here run into this before or have an idea of where to look?

[afoken]

$OS_ERROR does not contain what you think. Read the DBI connect documentation for how to properly report errors.

Also note that PostgreSQL, by default, refuses most connect / login attempts. See Client Authentication in the PostgreSQL documentation. psql will probably use a different communication path (Unix domain socket) than your perl script (TCP/IP socket via localhost IPv4/IPv6), and the server probably consideres the first one trustworthy, but not the second one.

Alexander

[vendion]

Thanks for the pointer about catching errors when DBI trys to connect to the database, I'm checking into the possibily I don't have PostgreSQL configured correctly to allow for connections.

[erix]

I know it works

You can only conclude that it works from the server itself. Is your perl program also running & connecting from the server?

Your code works for me. That means probably that you have to tweak your pg_hba.conf:

Pg authentication docs (NOTE: change '/current/' to whatever pg version you use; 'current' means Pg version 9.0 - and yes, it has changed).

Oh wait, I see you have 8.4, so here: 8.4 Pg authentication docs

Keep in mind that the first matching line (in pg_hba.conf) will be used.

irc freenode #postgresql is often the quickest way to get postgres database problems solved, or at least, discussed. The DBD:Pg author(s) are often there too.

UPDATE: if you leave the $dsn 'host=' param away, connection will be done via unix sockets (as opposed to tcp/ip).

[vendion]

I am running this code on my server, if the host=localhost part didn't give it away. I figured it may have but wasn't quite sure it was a problem on my end. I'll ask in the IRC for help getting my pg_hba.conf in order. I didn't think of using the unix socket for it but I will give it a try for now, will eventually need to get it to work through the tcp/ip port as well.

[erix]

Although of course I cannot garantee to spot any errors, you could show your pg_hba.conf here. (make sure you copy & paste the correct one; check with select current_setting('hba_file')).