Thread: Authentication Question
I've got an authentication questions. I've just setup pg_hba.conf to use "local all all trust" for testing purposes, but when the application I'm using tries to connect to the PostgreSQL Db (Using PHP) I get the following error.
" Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: IDENT authentication failed for user "webuser" . in /var/www/html/issue-tracker/includes/classes/dbi.class.php on line 98 "
To me, it looks like PostgreSQL is still trying to authenticate, even though it should be trusting all local socket connections. BTW, the web server and PostgreSQL server are on the same machine, so it's a local connection, not host. Do I need to modify anything in pg_ident.conf? I'd appreciate any help you can offer. Thx.
On Tue, Oct 28, 2003 at 14:12:31 -0600, "Epps, Aaron M." <Epps.Aaron@mayo.edu> wrote: > I've got an authentication questions. I've just setup pg_hba.conf to use "local all all trust" for testingpurposes, but when the application I'm using tries to connect to the PostgreSQL Db (Using PHP) I get the followingerror. > > " Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: IDENT authentication failed for user "webuser". in /var/www/html/issue-tracker/includes/classes/dbi.class.php on line 98 " > > To me, it looks like PostgreSQL is still trying to authenticate, even though it should be trusting all local socket connections. BTW, the web server and PostgreSQL server are on the same machine, so it's a local connection, not host. DoI need to modify anything in pg_ident.conf? I'd appreciate any help you can offer. Thx. The authentication tests are done in order, with the first one that applies being used. So if there is a rule for doing ident authentication for that database and user before the trust rule, then the trust rule won't get used.
"Epps, Aaron M." <Epps.Aaron@mayo.edu> writes:
> I've got an authentication questions. I've just setup pg_hba.conf to use "local all all trust" for
testingpurposes, but when the application I'm using tries to connect to the PostgreSQL Db (Using PHP) I get the
followingerror.
> " Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: IDENT authentication failed for user
"webuser". in /var/www/html/issue-tracker/includes/classes/dbi.class.php on line 98 "
> To me, it looks like PostgreSQL is still trying to authenticate, even
though it should be trusting all local socket connections.
(1) Did you SIGHUP the postmaster after editing the config file?
("pg_ctl reload" is the easy way to do this.) If not, it's still using
whatever setup you had before.
(2) It's fairly likely that PHP will try to connect via TCP/IP even for
a local server. If so, the relevant pg_hba.conf line will be the one
for 127.0.0.1, not the "local" line. "local" is for Unix-socket
connections.
regards, tom lane
On Tue, 28 Oct 2003, Epps, Aaron M. wrote:
> I've got an authentication questions. I've just setup pg_hba.conf
> to use "local all all trust" for testing purposes, but when the
> application I'm using tries to connect to the PostgreSQL Db (Using PHP)
> I get the following error.
>
> > " Warning: pg_connect(): Unable to connect to PostgreSQL server:
> FATAL: IDENT authentication failed for user "webuser" . in
> /var/www/html/issue-tracker/includes/classes/dbi.class.php on line 98 "
>
> To me, it looks like PostgreSQL is still trying to authenticate, even
> though it should be trusting all local socket connections. BTW, the web
> server and PostgreSQL server are on the same machine, so it's a local
> connection, not host. Do I need to modify anything in pg_ident.conf?
> I'd appreciate any help you can offer. Thx.
Two possibilities.
1: You have another line that says something like:
127.0.0.1 all all ident
and you're connecting like so:
pg_connect("host=hostname dbname=...");
OR
2: You need to restart your server:
pg_ctl restart
OR
pg_ctl reload
There are a handful of options, like shared buffers, that can only be
changed by restart, not reload. I'm pretty sure pg_hba.conf can be reset
by either restart or reload.
On Tue, 28 Oct 2003, Tom Lane wrote:
> "Epps, Aaron M." <Epps.Aaron@mayo.edu> writes:
> > I've got an authentication questions. I've just setup pg_hba.conf to use "local all all trust" for
testingpurposes, but when the application I'm using tries to connect to the PostgreSQL Db (Using PHP) I get the
followingerror.
>
> > " Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: IDENT authentication failed for user
"webuser". in /var/www/html/issue-tracker/includes/classes/dbi.class.php on line 98 "
>
> > To me, it looks like PostgreSQL is still trying to authenticate, even
> though it should be trusting all local socket connections.
>
> (1) Did you SIGHUP the postmaster after editing the config file?
> ("pg_ctl reload" is the easy way to do this.) If not, it's still using
> whatever setup you had before.
>
> (2) It's fairly likely that PHP will try to connect via TCP/IP even for
> a local server. If so, the relevant pg_hba.conf line will be the one
> for 127.0.0.1, not the "local" line. "local" is for Unix-socket
> connections.
This is correct if you specify a host name:
pg_connect("host=local dbname=db") <- TCP/IP
pg_connect("dbname=db") <- local unix sockets (i.e. no host=)
There is no hostname specified in the PHP code when it connects to PostgreSQL, so it's using a socket connections
thencorrect? Also, I did SIGHUP the PostgreSQL server after the configuration changes. Here's what the PHP config
filelooks like that's used to connect to PostgreSQL
$db = array(
"type" => "pgsql",
"host" => "",
"port" => "",
"name" => "issue-tracker",
"user" => "webuser",
"pass" => "password"
);
I wonder if it's trying to authenticate because there's a username and password specified in the pg_connect()
function. However, when I remove the username and password I get the following error...
Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: IDENT authentication failed for user
"dbname=issue-tracker". in /var/www/html/issue-tracker/includes/classes/dbi.class.php on line 98 "
Thoughts?
-----Original Message-----
From: scott.marlowe [mailto:scott.marlowe@ihs.com]
Sent: Tuesday, October 28, 2003 2:37 PM
To: Tom Lane
Cc: Epps, Aaron M.; 'pgsql-admin@postgresql.org'
Subject: Re: [ADMIN] Authentication Question
On Tue, 28 Oct 2003, Tom Lane wrote:
> "Epps, Aaron M." <Epps.Aaron@mayo.edu> writes:
> > I've got an authentication questions. I've just setup pg_hba.conf to use "local all all trust" for
testingpurposes, but when the application I'm using tries to connect to the PostgreSQL Db (Using PHP) I get the
followingerror.
>
> > " Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: IDENT authentication failed for user
"webuser". in /var/www/html/issue-tracker/includes/classes/dbi.class.php on line 98 "
>
> > To me, it looks like PostgreSQL is still trying to authenticate,
> > even
> though it should be trusting all local socket connections.
>
> (1) Did you SIGHUP the postmaster after editing the config file?
> ("pg_ctl reload" is the easy way to do this.) If not, it's still
> using whatever setup you had before.
>
> (2) It's fairly likely that PHP will try to connect via TCP/IP even
> for a local server. If so, the relevant pg_hba.conf line will be the
> one for 127.0.0.1, not the "local" line. "local" is for Unix-socket
> connections.
This is correct if you specify a host name:
pg_connect("host=local dbname=db") <- TCP/IP
pg_connect("dbname=db") <- local unix sockets (i.e. no host=)
I bet your connect class is distilling your $db array down into a connect
string that looks like:
pg_connect("host= dbname=db user=username");
And the presence of a host= in there is goofing things up. Can you edit
the class to make sure it isn't inserting a host= part before connecting?
On Tue, 28 Oct 2003, Epps, Aaron M. wrote:
> There is no hostname specified in the PHP code when it connects to PostgreSQL, so it's using a socket
connectionsthen correct? Also, I did SIGHUP the PostgreSQL server after the configuration changes. Here's what the
PHPconfig file looks like that's used to connect to PostgreSQL
>
> $db = array(
> "type" => "pgsql",
> "host" => "",
> "port" => "",
> "name" => "issue-tracker",
> "user" => "webuser",
> "pass" => "password"
> );
>
>
> I wonder if it's trying to authenticate because there's a username and password specified in the pg_connect()
function. However, when I remove the username and password I get the following error...
>
> Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: IDENT authentication failed for user
"dbname=issue-tracker". in /var/www/html/issue-tracker/includes/classes/dbi.class.php on line 98 "
>
> Thoughts?
>
> -----Original Message-----
> From: scott.marlowe [mailto:scott.marlowe@ihs.com]
> Sent: Tuesday, October 28, 2003 2:37 PM
> To: Tom Lane
> Cc: Epps, Aaron M.; 'pgsql-admin@postgresql.org'
> Subject: Re: [ADMIN] Authentication Question
>
> On Tue, 28 Oct 2003, Tom Lane wrote:
>
> > "Epps, Aaron M." <Epps.Aaron@mayo.edu> writes:
> > > I've got an authentication questions. I've just setup pg_hba.conf to use "local all all trust" for
testingpurposes, but when the application I'm using tries to connect to the PostgreSQL Db (Using PHP) I get the
followingerror.
> >
> > > " Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: IDENT authentication failed for user
"webuser". in /var/www/html/issue-tracker/includes/classes/dbi.class.php on line 98 "
> >
> > > To me, it looks like PostgreSQL is still trying to authenticate,
> > > even
> > though it should be trusting all local socket connections.
> >
> > (1) Did you SIGHUP the postmaster after editing the config file?
> > ("pg_ctl reload" is the easy way to do this.) If not, it's still
> > using whatever setup you had before.
> >
> > (2) It's fairly likely that PHP will try to connect via TCP/IP even
> > for a local server. If so, the relevant pg_hba.conf line will be the
> > one for 127.0.0.1, not the "local" line. "local" is for Unix-socket
> > connections.
>
> This is correct if you specify a host name:
>
> pg_connect("host=local dbname=db") <- TCP/IP
> pg_connect("dbname=db") <- local unix sockets (i.e. no host=)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
Here's the PHP code that's being used to create the connection string. It looks like it isn't inserting anything
ifthe host name isn't specified...
// Build the connection string
$conn_str = "user=".$this->user;
$conn_str .= !empty($this->pass) ? " password='".$this->pass."'" : "";
$conn_str .= !empty($this->host) ? " host=".$this->host : "";
$conn_str .= !empty($this->port) ? " port=".$this->port : "";
$conn_str .= " dbname=".$this->name;
$this->link = pg_connect($conn_str)
or $this->logger("Database connection failed!","DBI");
break;
default:
$this->logger("Unknown database type in init()","DBI");
break;
-----Original Message-----
From: scott.marlowe [mailto:scott.marlowe@ihs.com]
Sent: Tuesday, October 28, 2003 2:58 PM
To: Epps, Aaron M.
Cc: 'pgsql-admin@postgresql.org'
Subject: Re: [ADMIN] Authentication Question
I bet your connect class is distilling your $db array down into a connect string that looks like:
pg_connect("host= dbname=db user=username");
And the presence of a host= in there is goofing things up. Can you edit the class to make sure it isn't inserting a
host=part before connecting?
On Tue, 28 Oct 2003, Epps, Aaron M. wrote:
> There is no hostname specified in the PHP code when it connects to
> PostgreSQL, so it's using a socket connections then correct? Also, I
> did SIGHUP the PostgreSQL server after the configuration changes.
> Here's what the PHP config file looks like that's used to connect to
> PostgreSQL
>
> $db = array(
> "type" => "pgsql",
> "host" => "",
> "port" => "",
> "name" => "issue-tracker",
> "user" => "webuser",
> "pass" => "password"
> );
>
>
> I wonder if it's trying to authenticate because there's a username and password specified in the pg_connect()
function. However, when I remove the username and password I get the following error...
>
> Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: IDENT authentication failed for user
"dbname=issue-tracker". in /var/www/html/issue-tracker/includes/classes/dbi.class.php on line 98 "
>
> Thoughts?
>
> -----Original Message-----
> From: scott.marlowe [mailto:scott.marlowe@ihs.com]
> Sent: Tuesday, October 28, 2003 2:37 PM
> To: Tom Lane
> Cc: Epps, Aaron M.; 'pgsql-admin@postgresql.org'
> Subject: Re: [ADMIN] Authentication Question
>
> On Tue, 28 Oct 2003, Tom Lane wrote:
>
> > "Epps, Aaron M." <Epps.Aaron@mayo.edu> writes:
> > > I've got an authentication questions. I've just setup pg_hba.conf to use "local all all trust" for
testingpurposes, but when the application I'm using tries to connect to the PostgreSQL Db (Using PHP) I get the
followingerror.
> >
> > > " Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: IDENT authentication failed for user
"webuser". in /var/www/html/issue-tracker/includes/classes/dbi.class.php on line 98 "
> >
> > > To me, it looks like PostgreSQL is still trying to authenticate,
> > > even
> > though it should be trusting all local socket connections.
> >
> > (1) Did you SIGHUP the postmaster after editing the config file?
> > ("pg_ctl reload" is the easy way to do this.) If not, it's still
> > using whatever setup you had before.
> >
> > (2) It's fairly likely that PHP will try to connect via TCP/IP even
> > for a local server. If so, the relevant pg_hba.conf line will be
> > the one for 127.0.0.1, not the "local" line. "local" is for
> > Unix-socket connections.
>
> This is correct if you specify a host name:
>
> pg_connect("host=local dbname=db") <- TCP/IP
> pg_connect("dbname=db") <- local unix sockets (i.e. no host=)
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
>
And you've restarted the server and all, right?
Just for troubleshooting edit the 127.0.0.1 line for trust and restart and
see if the problem goes away. I wonder if php changed behaviour on unix
sockets versus TCP/IP somewhere along the way. We only use explicit host
namd / TCP/IP nowadays where I work...
On Tue, 28 Oct 2003, Epps, Aaron M. wrote:
> Here's the PHP code that's being used to create the connection string. It looks like it isn't inserting anything
ifthe host name isn't specified...
>
> // Build the connection string
> $conn_str = "user=".$this->user;
> $conn_str .= !empty($this->pass) ? " password='".$this->pass."'" : "";
> $conn_str .= !empty($this->host) ? " host=".$this->host : "";
> $conn_str .= !empty($this->port) ? " port=".$this->port : "";
> $conn_str .= " dbname=".$this->name;
>
> $this->link = pg_connect($conn_str)
> or $this->logger("Database connection failed!","DBI");
> break;
> default:
> $this->logger("Unknown database type in init()","DBI");
> break;
>
> -----Original Message-----
> From: scott.marlowe [mailto:scott.marlowe@ihs.com]
> Sent: Tuesday, October 28, 2003 2:58 PM
> To: Epps, Aaron M.
> Cc: 'pgsql-admin@postgresql.org'
> Subject: Re: [ADMIN] Authentication Question
>
> I bet your connect class is distilling your $db array down into a connect string that looks like:
>
> pg_connect("host= dbname=db user=username");
>
> And the presence of a host= in there is goofing things up. Can you edit the class to make sure it isn't inserting a
host=part before connecting?
>
> On Tue, 28 Oct 2003, Epps, Aaron M. wrote:
>
> > There is no hostname specified in the PHP code when it connects to
> > PostgreSQL, so it's using a socket connections then correct? Also, I
> > did SIGHUP the PostgreSQL server after the configuration changes.
> > Here's what the PHP config file looks like that's used to connect to
> > PostgreSQL
> >
> > $db = array(
> > "type" => "pgsql",
> > "host" => "",
> > "port" => "",
> > "name" => "issue-tracker",
> > "user" => "webuser",
> > "pass" => "password"
> > );
> >
> >
> > I wonder if it's trying to authenticate because there's a username and password specified in the pg_connect()
function. However, when I remove the username and password I get the following error...
> >
> > Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: IDENT authentication failed for user
"dbname=issue-tracker". in /var/www/html/issue-tracker/includes/classes/dbi.class.php on line 98 "
> >
> > Thoughts?
> >
> > -----Original Message-----
> > From: scott.marlowe [mailto:scott.marlowe@ihs.com]
> > Sent: Tuesday, October 28, 2003 2:37 PM
> > To: Tom Lane
> > Cc: Epps, Aaron M.; 'pgsql-admin@postgresql.org'
> > Subject: Re: [ADMIN] Authentication Question
> >
> > On Tue, 28 Oct 2003, Tom Lane wrote:
> >
> > > "Epps, Aaron M." <Epps.Aaron@mayo.edu> writes:
> > > > I've got an authentication questions. I've just setup pg_hba.conf to use "local all all trust"
fortesting purposes, but when the application I'm using tries to connect to the PostgreSQL Db (Using PHP) I get the
followingerror.
> > >
> > > > " Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: IDENT authentication failed for user
"webuser". in /var/www/html/issue-tracker/includes/classes/dbi.class.php on line 98 "
> > >
> > > > To me, it looks like PostgreSQL is still trying to authenticate,
> > > > even
> > > though it should be trusting all local socket connections.
> > >
> > > (1) Did you SIGHUP the postmaster after editing the config file?
> > > ("pg_ctl reload" is the easy way to do this.) If not, it's still
> > > using whatever setup you had before.
> > >
> > > (2) It's fairly likely that PHP will try to connect via TCP/IP even
> > > for a local server. If so, the relevant pg_hba.conf line will be
> > > the one for 127.0.0.1, not the "local" line. "local" is for
> > > Unix-socket connections.
> >
> > This is correct if you specify a host name:
> >
> > pg_connect("host=local dbname=db") <- TCP/IP
> > pg_connect("dbname=db") <- local unix sockets (i.e. no host=)
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 1: subscribe and unsubscribe commands go to
> > majordomo@postgresql.org
> >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>