1

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?

3 Answers 3

1

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","" ) ;
4
  • 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, 2009 at 14:36
  • sorry for the formatting above!
    – mojones
    Dec 7, 2009 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? Dec 7, 2009 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. Dec 23, 2009 at 21:13
0

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

0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .