Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a bunch of Perl scripts that have recently stopped working. I have narrowed the problem down to this: when I connect to a postgresql database from a perl script, it attempts to use the wrong username and the connection fails.

E.g. if I am logged in as someuser:

someuser$ psql -l
           List of databases
      Name       |  Owner   | Encoding 
-----------------+----------+----------
 one             | someuser | UTF8
 two             | someuser | UTF8

the output is as expected.

But if I attempt the same from within Perl:

someuser$ perl -e 'system("psql -l")'
psql: FATAL:  role "anotheruser" does not exist

The same thing happens when using the DBI module; this is just the simplest way to reproduce the problem. Obviously the psql executable is picking up the wrong username when run from within Perl; any ideas how this is happening?

share|improve this question

3 Answers 3

The "have recently stopped working" implies there has been a change recently. Have you applied either a Perl DBI or PostgreSQL update recently that coincides with the change in behaviour? If so then information about which versions you moved between would be useful.

Is it possible that your perl interpreter is SUID "anotheruser"?

What does perl -e 'print "Real: $< Effective: $>\n";' output?

Authentication is quite flexible in PostgreSQL. Have you made customisations to your pg_hba.conf file? Are there any user mappings in the pg_ident.conf file?

Is it possible that there are any environment variables set in your shell (e.g. PGUSER) or in your ~/.psqlrc file?

To ensure deterministic behaviour I would recommend that when you connect to the database using DBI, you specify the username that should be used in the DSN

e.g.

$dbh = DBI->connect ( "dbi:Pg:dbname=one","someuser","" ) ;
share|improve this answer
    
Thanks for the suggestions. someuser$ perl -e 'print "Real: $< Effective: $>\n";' Real: 501 Effective: 501 I don't think there's been any recent version changes - this is on another user's computer so I will check. pg_hba.conf looks normal. I agree that the user should definitely be passed directly to the connect() method; I'd still like to get to the bottom of this problem though. –  mojones Dec 7 '09 at 14:36
    
sorry for the formatting above! –  mojones Dec 7 '09 at 14:40
    
Do you have anything set in the PG* environment variables or a ~/.psqlrc file? Are there any map entries in the pg_ident.conf file? –  Russell Heilling Dec 7 '09 at 16:04
    
you mention that it is on another user's computer. Does the "psql: FATAL: role "anotheruser" does not exist" error have the username of that other user? psql will attempt to log in with the current user if a username is not supplied. So while you may have yourself listed as a user in the pg_hba file, you might not have them listed so you are getting a reject. –  Arthur Thomas Dec 23 '09 at 21:13

The most common situation in which it would happen, if you had PGUSER variable set.

share|improve this answer

Another general tip, don't rely on environment variables for psql in general; the postgres dev's make no guarantees that they will work. Instead, use the psql flags (-U user, -d database, -h host, etc...) in your calls.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.