Обсуждение: Connect error
Hi,
I created a database in debian using psql (as database user russell). My php4 script
can't access it. It reports:
Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL:
IDENT authentication failed for user "russell" . in /home/russell/public_html/hello.php on line 2
The script is:
<?php
$conn=pg_connect("dbname=parts_list user=russell");
if(!$conn)
exit(pg_result_error($conn));
?>
I have apache-1.3 running ok. In /etc/postgresql/pg_hba.conf, i
have "ident sameuser". I can access the database ok from my user
account using psql. What can i check now?
On Tue, Jan 13, 2004 at 02:33:20AM +1100, Russell Shaw wrote: > Hi, > > I created a database in debian using psql (as database user russell). My > php4 script > can't access it. It reports: > > Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL: > IDENT authentication failed for user "russell" . in > /home/russell/public_html/hello.php on line 2 I don't know php, but is it (or Apache) running as user russell? If not, then you can't authorize by IDENT. > I have apache-1.3 running ok. In /etc/postgresql/pg_hba.conf, i > have "ident sameuser". I can access the database ok from my user > account using psql. What can i check now? I added lines to pg_hba.conf for the md5 method. For example: local foo_db foo_user md5 host foo_db foo_user 127.0.0.1 255.255.255.255 md5 -- Bill Moseley moseley@hank.org
On Mon, Jan 12, 2004 at 07:42:41 -0800, Bill Moseley <moseley@hank.org> wrote: > > I don't know php, but is it (or Apache) running as user russell? If > not, then you can't authorize by IDENT. It is possible to authenticate using ident using a map that says the webserver account is allowed to use the db account "russell". The web server must either be on the same machine uisng domain sockets for connecting (which looks to be the case here) or be running an ident server. If you do this you are implicitly trusting the web server account, which might not be a good idea in some circumstances. You might want to create a separate db account for the web server with miminal privileges needed for its task.
Dear Russell Shaw ,
> <?php
> $conn=pg_connect("dbname=parts_list user=russell");
> if(!$conn)
> exit(pg_result_error($conn));
> ?>
>
> I have apache-1.3 running ok. In /etc/postgresql/pg_hba.conf, i
> have "ident sameuser". I can access the database ok from my user
Bill is correct IDENT gives me doubt also
To check further do one thing
psql -U <username> -h localhost <database>
If this fails
make sure postgres.conf has field
Check tcpip_socket is set to true
and
virtual_host is pointing to server's IP
hope this helps
Regards,
Vishal Kashyap
Since apache is not running as russell, you have to provide your password in
the connection string:
$conn=pg_connect("dbname=parts_list user=russell password=yourpassword");
This would be equivilent to being in a *nix shell as a DIFFERENT user and
attempting:
psql -U russell
and expecting to be able to connect. Won't work. Ident authentication
requires that you BE the user that you are trying to connect as.
The only way you could connect from PHP (as an Apache module) without a
password is for the user Apache runs as to be the postgres user. NOT a good
idea....
> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Russell Shaw
> Sent: Monday, January 12, 2004 10:33 AM
> To: pgsql-novice@postgresql.org
> Subject: [NOVICE] Connect error
>
>
> Hi,
>
> I created a database in debian using psql (as database user
> russell). My php4 script
> can't access it. It reports:
>
> Warning: pg_connect(): Unable to connect to PostgreSQL server: FATAL:
> IDENT authentication failed for user "russell" . in
> /home/russell/public_html/hello.php on line 2
>
> The script is:
>
> <?php
> $conn=pg_connect("dbname=parts_list user=russell");
> if(!$conn)
> exit(pg_result_error($conn));
> ?>
>
> I have apache-1.3 running ok. In /etc/postgresql/pg_hba.conf, i
> have "ident sameuser". I can access the database ok from my user
> account using psql. What can i check now?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
On Mon, Jan 12, 2004 at 10:04:53AM -0600, Bruno Wolff III wrote:
> On Mon, Jan 12, 2004 at 07:42:41 -0800,
> Bill Moseley <moseley@hank.org> wrote:
> >
> > I don't know php, but is it (or Apache) running as user russell? If
> > not, then you can't authorize by IDENT.
>
> It is possible to authenticate using ident using a map that says the
> webserver account is allowed to use the db account "russell". The web server
> must either be on the same machine uisng domain sockets for connecting
> (which looks to be the case here) or be running an ident server.
I was not able to get this configuration working, so I think I'm not
understanding the documentation correctly. Or maybe I was expecting
that "sameuser" would work:
moseley@bumby:~$ createdb newdb
CREATE DATABASE
moseley@bumby:~$ psql newdb
Welcome to psql 7.4.1, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
newdb=> \q
moseley@bumby:~$ su
Password:
bumby:/home/moseley# fgrep moseley /etc/postgresql/pg_ident.conf
sameuser www-data moseley
bumby:/home/moseley# /etc/init.d/postgresql restart
Stopping PostgreSQL database server: postmaster.
Starting PostgreSQL database server: postmaster.
bumby:/home/moseley# su www-data
bumby:/home/moseley$ psql newdb
psql: FATAL: user "www-data" does not exist
bumby:/home/moseley$ psql -Umoseley newdb
psql: FATAL: IDENT authentication failed for user "moseley"
I even tried using my own map name instead of "sameuser".
bumby:/etc/postgresql# fgrep testmap pg_hba.conf pg_ident.conf
pg_hba.conf:host all all 127.0.0.1 255.255.255.255 ident testmap
pg_ident.conf:testmap moseley www-data
So I think I'm missing an important concept.
--
Bill Moseley
moseley@hank.org
On Mon, Jan 12, 2004 at 09:29:00 -0800, Bill Moseley <moseley@hank.org> wrote: > > I was not able to get this configuration working, so I think I'm not > understanding the documentation correctly. Or maybe I was expecting > that "sameuser" would work: sameuser says that the os username must match the postgres username. > moseley@bumby:~$ createdb newdb > CREATE DATABASE > > moseley@bumby:~$ psql newdb > Welcome to psql 7.4.1, the PostgreSQL interactive terminal. > > Type: \copyright for distribution terms > \h for help with SQL commands > \? for help on internal slash commands > \g or terminate with semicolon to execute query > \q to quit > > newdb=> \q > > moseley@bumby:~$ su > Password: > > bumby:/home/moseley# fgrep moseley /etc/postgresql/pg_ident.conf > sameuser www-data moseley Since sameuser is a special ident map you can't use it as a named map in the pg_ident.conf map. > > bumby:/home/moseley# /etc/init.d/postgresql restart > Stopping PostgreSQL database server: postmaster. > Starting PostgreSQL database server: postmaster. > > bumby:/home/moseley# su www-data > > bumby:/home/moseley$ psql newdb > psql: FATAL: user "www-data" does not exist > > bumby:/home/moseley$ psql -Umoseley newdb > psql: FATAL: IDENT authentication failed for user "moseley" > > > I even tried using my own map name instead of "sameuser". > > bumby:/etc/postgresql# fgrep testmap pg_hba.conf pg_ident.conf > pg_hba.conf:host all all 127.0.0.1 255.255.255.255 ident testmap > pg_ident.conf:testmap moseley www-data > > So I think I'm missing an important concept. I think you are pretty close. In the last case you enabled ident authentication using testmap for internet connections, but unless you did this for local connections (domain sockets) as well, it wouldn't apply since the way you are using psql will use a domain socket to connect (unless you have set the PGHOST environment variable). > > > > > -- > Bill Moseley > moseley@hank.org > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings
On Mon, Jan 12, 2004 at 11:57:42AM -0600, Bruno Wolff III wrote: > > Since sameuser is a special ident map you can't use it as a named map in > the pg_ident.conf map. I thought maybe the pg_ident.conf file was enough to simply say "connections from www-data should be considered as coming from user moseley" and then sameuser would work (i.e. www-data would be able to connect to moseley's databases). BTW -- when using a map like this that uses the ident service, what dsn string is used when connecting? (I'm using Perl, BTW.) > > bumby:/etc/postgresql# fgrep testmap pg_hba.conf pg_ident.conf > > pg_hba.conf:host all all 127.0.0.1 255.255.255.255 ident testmap > > pg_ident.conf:testmap moseley www-data > > > > So I think I'm missing an important concept. > > I think you are pretty close. In the last case you enabled ident authentication > using testmap for internet connections, but unless you did this for local > connections (domain sockets) as well, it wouldn't apply since the way you > are using psql will use a domain socket to connect (unless you have set > the PGHOST environment variable). You mean adding a "local" entry too? host all all 127.0.0.1 255.255.255.255 ident testmap local all all ident testmap bumby:/etc/postgresql$ psql newdb psql: FATAL: user "www-data" does not exist bumby:/etc/postgresql$ psql -Umoseley newdb psql: FATAL: IDENT authentication failed for user "moseley" bumby:/etc/postgresql$ psql -Uwww-data newdb psql: FATAL: user "www-data" does not exist This is why I ended up creating a new database user with a password and then granting access to ALL of my objects to the new database user. And then using that username/password in the connect string in the web application. Can I turn on debugging in Postgresql to see why it's failing? That would likely help resolve the problem. -- Bill Moseley moseley@hank.org
On Mon, Jan 12, 2004 at 10:22:03 -0800, Bill Moseley <moseley@hank.org> wrote: > On Mon, Jan 12, 2004 at 11:57:42AM -0600, Bruno Wolff III wrote: > > > > Since sameuser is a special ident map you can't use it as a named map in > > the pg_ident.conf map. > > I thought maybe the pg_ident.conf file was enough to simply say > "connections from www-data should be considered as coming from user > moseley" and then sameuser would work (i.e. www-data would be able to > connect to moseley's databases). "sameuser" is used in several contexts. One is for the user field and there it means that if the username matches the database name, then this rule applies (if the IP address and the connection type also match). The other usage is for using a default ident map. If you use "sameuser" for the ident map, then pg_ident.conf doesn't get checked. > BTW -- when using a map like this that uses the ident service, what dsn > string is used when connecting? (I'm using Perl, BTW.) I am not sure what "dsn" means in this context, so I can't answer this. > > > > bumby:/etc/postgresql# fgrep testmap pg_hba.conf pg_ident.conf > > > pg_hba.conf:host all all 127.0.0.1 255.255.255.255 ident testmap > > > pg_ident.conf:testmap moseley www-data > > > > > > So I think I'm missing an important concept. > > > > I think you are pretty close. In the last case you enabled ident authentication > > using testmap for internet connections, but unless you did this for local > > connections (domain sockets) as well, it wouldn't apply since the way you > > are using psql will use a domain socket to connect (unless you have set > > the PGHOST environment variable). > > You mean adding a "local" entry too? > > host all all 127.0.0.1 255.255.255.255 ident testmap > local all all ident testmap Yes. > > bumby:/etc/postgresql$ psql newdb > psql: FATAL: user "www-data" does not exist This makes sense since www-data isn't a postgres user. > bumby:/etc/postgresql$ psql -Umoseley newdb > psql: FATAL: IDENT authentication failed for user "moseley" I think this is caused by have the two names mixed up. > > > pg_ident.conf:testmap moseley www-data According to the documentation the ident name should be the first name and the postgres name the second name. > bumby:/etc/postgresql$ psql -Uwww-data newdb > psql: FATAL: user "www-data" does not exist > > This is why I ended up creating a new database user with a password and then > granting access to ALL of my objects to the new database user. And then > using that username/password in the connect string in the web > application. > > Can I turn on debugging in Postgresql to see why it's failing? That > would likely help resolve the problem. I don't think that would add any useful information in this case.
OK, I think I get it now.
For a database "newdb", created by user moseley and to only
allow connections from user moseley and the web server running as www-data
add the following line early:
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
local newdb all ident webaccess
So, all unix-domain connections (local) trying to connect to user "newdb"
will check the "webaccess" map in the pg_ident.conf file.
webaccess moseley moseley
webaccess www-data moseley
The first one is basically duplicating the "sameuser" feature.
Connection requests as user moseley must ident as user moseley.
The second one says connections for user moseley can ident as
"www-data". "www-data" does not need to be a postgres user, of course.
bumby:~$ whoami
www-data
bumby:~$ psql -Umoseley newdb
Welcome to psql 7.4.1, the PostgreSQL interactive terminal.
Now, to allow host connections (which is how my web application would
connect), add the "host" line:
# TYPE DATABASE USER IP-ADDRES IP-MASK METHOD
local newdb all ident webaccess
host newdb all 127.0.0.1 255.255.255.255 ident webaccess
I'm running Debian sid which didn't have ident running, so I installed
the ident2 package before this would work.
I'm just learning, but...
I doubt I would use this method. Instead I'd use the md5 method
and load the password into the web server on startup (when running as
root). That won't work with CGI programs, but will with mod_perl, for
example. With the above method anyone with access to the web server can
access the newdb database. Using a username and password also allows
GRANT permissions per user.
--
Bill Moseley
moseley@hank.org
Bruno Wolff III wrote: > On Mon, Jan 12, 2004 at 07:42:41 -0800, > Bill Moseley <moseley@hank.org> wrote: > >>I don't know php, but is it (or Apache) running as user russell? If >>not, then you can't authorize by IDENT. > > It is possible to authenticate using ident using a map that says the > webserver account is allowed to use the db account "russell". The web server > must either be on the same machine uisng domain sockets for connecting > (which looks to be the case here) or be running an ident server. > > If you do this you are implicitly trusting the web server account, which > might not be a good idea in some circumstances. You might want to create > a separate db account for the web server with miminal privileges needed > for its task. In pg_ident.conf, i put: # MAPNAME IDENT-USERNAME PG-USERNAME apache www-data russell apache russell russell This works: psql -U russell parts_list This doesn't: psql -U www-data parts_list It says: psql: FATAL: IDENT authentication failed for user "www-data" I've tried adding -h localhost also. How can i test the identd server for user www-data? www-data is in /etc/passwd, and i can also su to it.
On Tue, Jan 13, 2004 at 02:38:04PM +1100, Russell Shaw wrote: > > In pg_ident.conf, i put: > > # MAPNAME IDENT-USERNAME PG-USERNAME > apache www-data russell > apache russell russell > > This works: > psql -U russell parts_list > > This doesn't: > psql -U www-data parts_list > > It says: psql: FATAL: IDENT authentication failed for user "www-data" I don't think that's how it works. But, I'm just learning -- so I'll try and get it correct (but no guarantees). The idea is you can do this: $ su www-data # now you are the web server user $ psql -U russell parts_lists So you are saying with -U that you want to connect at the *Postgres* user "russell". Now, normally, postgres would do an ident[1] and say, "Ok, you want to connect as user "russell" but the ident returned you as user "www-data" so you are not authenticated." But, by using the map: apache www-data russell that says (or so I'm guessing) that "ok, when ident returns 'www-data' map that to user 'russell' and use that username for connecting to the database." And thus you are requesting to connect as user (-U) russell and now the ident has been mapped to user russell so you are authenticated. Or to say it another way, when you are user "russell" and you connect to psql it can do an ident and say authenticate that you really are "russell". But when you are another unix user, but you use -U to specify the username, it can't authenticate you -- so the map allows mapping of one unix username to another for authentication purposes. All that, of course, has to work with pg_hba.conf. BTW - I found it somewhat confusing because the default (at least mine) pg_hba.conf says any user that is also a postgres user can authenticate and then access any database when they connect from their own account. local all all ident sameuser > I've tried adding -h localhost also. > > How can i test the identd server for user www-data? > www-data is in /etc/passwd, and i can also su to it. Yes, but www-data is not a Postgres user -- and doesn't need to be. [1] Also, when using psql you don't need an ident server running to authenticate -- just like "whoami" will report your username without using an ident server. That said, I hope someone will explain that better with regard to how unix-domain sockets work. I trust someone will correct any errors in the above... -- Bill Moseley moseley@hank.org
Bill Moseley wrote:
> On Tue, Jan 13, 2004 at 02:38:04PM +1100, Russell Shaw wrote:
>
>>In pg_ident.conf, i put:
>>
>> # MAPNAME IDENT-USERNAME PG-USERNAME
>> apache www-data russell
>> apache russell russell
>>
>>This works:
>> psql -U russell parts_list
>>
>>This doesn't:
>> psql -U www-data parts_list
>>
>>It says: psql: FATAL: IDENT authentication failed for user "www-data"
>
> I don't think that's how it works. But, I'm just learning -- so I'll
> try and get it correct (but no guarantees).
Hi,
By a process of elimination, i think it works like this:
The script is:
<?php
$conn=pg_connect("dbname=parts_list user=russell");
if(!$conn)
exit(pg_result_error($conn));
?>
Apache accesses postgres postmaster saying it is russell (from the php
user=russell above). However, postmaster finds out by identd that the
process (apache) is user www-data. So, ident user www-data needs to be
mapped as postgres user russell in pg_ident.conf. I have in pg_hba.conf:
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
local all postgres ident sameuser
local all russell ident apache
host all russell 127.0.0.1 255.255.255.255 ident apache
local all all ident sameuser
host all all 127.0.0.1 255.255.255.255 ident sameuser
host all all 0.0.0.0 0.0.0.0 reject
pg_ident.conf:
# MAPNAME IDENT-USERNAME PG-USERNAME
apache www-data russell
Bill Moseley wrote:
> On Tue, Jan 13, 2004 at 02:38:04PM +1100, Russell Shaw wrote:
>
>>In pg_ident.conf, i put:
>>
>> # MAPNAME IDENT-USERNAME PG-USERNAME
>> apache www-data russell
>> apache russell russell
>>
>>This works:
>> psql -U russell parts_list
>>
>>This doesn't:
>> psql -U www-data parts_list
>>
>>It says: psql: FATAL: IDENT authentication failed for user "www-data"
>
> I don't think that's how it works. But, I'm just learning -- so I'll
> try and get it correct (but no guarantees).
Hi,
By a process of elimination, i think it works like this:
The script is:
<?php
$conn=pg_connect("dbname=parts_list user=russell");
if(!$conn)
exit(pg_result_error($conn));
?>
Apache accesses postgres postmaster saying it is russell (from the php
user=russell above). However, postmaster finds out by identd that the
process (apache) is user www-data. So, ident user www-data needs to be
mapped as postgres user russell in pg_ident.conf. I have in pg_hba.conf:
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
local all postgres ident sameuser
local all russell ident apache
host all russell 127.0.0.1 255.255.255.255 ident apache
local all all ident sameuser
host all all 127.0.0.1 255.255.255.255 ident sameuser
host all all 0.0.0.0 0.0.0.0 reject
pg_ident.conf:
# MAPNAME IDENT-USERNAME PG-USERNAME
apache www-data russell
apache russell russell # for non-apache