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 Tue, Jan 13, 2004 at 02:33:20AM +1100, Russell Shaw wrote:
I don't know php, but is it (or Apache) running as user russell? If
not, then you can't authorize by IDENT.

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 (AT) hank (DOT) org


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On Mon, Jan 12, 2004 at 07:42:41 -0800,
Bill Moseley <moseley (AT) hank (DOT) org> wrote:
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.


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Dear Russell Shaw ,

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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....



---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On Mon, Jan 12, 2004 at 10:04:53AM -0600, Bruno Wolff III 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:


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 (AT) hank (DOT) org


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Mon, Jan 12, 2004 at 09:29:00 -0800,
Bill Moseley <moseley (AT) hank (DOT) org> wrote:
sameuser says that the os username must match the postgres username.

Since sameuser is a special ident map you can't use it as a named map in
the pg_ident.conf map.

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).

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

On Mon, Jan 12, 2004 at 11:57:42AM -0600, Bruno Wolff III wrote:
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.)

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 (AT) hank (DOT) org


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

On Mon, Jan 12, 2004 at 10:22:03 -0800,
Bill Moseley <moseley (AT) hank (DOT) org> wrote:
"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.

I am not sure what "dsn" means in this context, so I can't answer this.

Yes.

This makes sense since www-data isn't a postgres user.

I think this is caused by have the two names mixed up.
and the postgres name the second name.

I don't think that would add any useful information in this case.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

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 (AT) hank (DOT) org


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match