Can't access to database from webapp in PostgreSQL 9.0, from shell it is Ok.

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Andre Lopes
Sent: Friday, December 02, 2011 6:14 PM
To: postgresql Forums
Subject: [GENERAL] Can't access to database from webapp in PostgreSQL 9.0, from shell it is Ok.

With the method above I have no problems in enter "psql" but when I try to connect with the user created with these method to a webapp I got an error:

[code]
Exception Value:
FATAL: Ident authentication failed for user "mypoatgreuser"
[/code]

There is more permissions that I must to give to the user "mypoatgreuser"? What could be wrong here?

-----------------------------------------------------

Andre,

The information you have supplied is inadequate but I am concerned that you may just have a simple typo in your webapp configuration:

"mypoatgreuser" => "mypostgreuser"; note the "a" should be an "s"

Also, it is considered proper for the shortened form of PostgreSQL to include the trailing "s" (Postgres) as opposed to simply "Postgre"; might as well get used to that before you go naming everything you come across "postgre" :)

David J.

On Friday, December 02, 2011 3:13:41 pm Andre Lopes wrote:

Hi,

I've installed PostgreSQL 9.0 in CentOS6 I don't have configured
anything in Postgre, I just created a user with this method:

With the method above I have no problems in enter "psql" but when I
try to connect with the user created with these method to a webapp I
got an error:

[code]
Exception Value:
FATAL: Ident authentication failed for user "mypoatgreuser"
[/code]

There is more permissions that I must to give to the user
"mypoatgreuser"? What could be wrong here?

Some pointers. The client authentication is handled here:
http://www.postgresql.org/docs/9.0/interactive/auth-pg-hba-conf.html

In your pg_hba.conf there is at least one authentication method set to ident.
That is described here:
http://www.postgresql.org/docs/9.0/interactive/auth-methods.html#AUTH-IDENT

From the sequence of commands you have given you are looking to use password
authentication. For security you want md5.
There may already be a line with that method in your pg_hba.conf. In pg_hba.conf
first matching line wins, so if there is a line with ident first it will take
precedence.

If this is too confusing post your pg_hba.conf(unless of course there are
security issues) and we can go from there.

Best Regards,

--
Adrian Klaver
adrian.klaver@gmail.com

On 12/02/11 3:13 PM, Andre Lopes wrote:

[code]
Exception Value:
FATAL: Ident authentication failed for user "mypoatgreuser"
[/code]

There is more permissions that I must to give to the user
"mypoatgreuser"? What could be wrong here?

'ident' is the default authentication type for local domain socket
connections, and it means that user 'x' can only connect as postgresql
role 'x'.

A web app likely runs as httpd or webuser or some such, so ident
authentication isn't really applicable.

You control the authentication methods in the pg_hba.conf file. I'd
suggest doing this...

1) set (and remember) passwords for all roles, including the "postgres"
default adminstrator role....

ALTER ROLE postgres WITH PASSWORD 'somepassword';

2) configure pg_hba.conf as follows...

local all postgres ident
local all all md5

host all all 127.0.0.1/32 md5
host all all (yoursubnet) md5

and HUP the master postgres process (pg_ctl ... reload).

this way, the postgres user can authenticate locally with ident, but all
other users will require passwords.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

Hi Adrian,

Thanks for the reply.

My pg_hba.conf have this:
[code]
# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all ident
# IPv4 local connections:
host all all 127.0.0.1/32 ident
# IPv6 local connections:
host all all ::1/128 ident

[/code]

Some clue with this config file?

Best Regards,

On 12/02/11 3:41 PM, Andre Lopes wrote:

My pg_hba.conf have this:
[code]
# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all ident
# IPv4 local connections:
host all all 127.0.0.1/32 ident
# IPv6 local connections:
host all all ::1/128 ident

[/code]

Some clue with this config file?

see my other post. and don't use ident for 'host' connections, its not
really suitable.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

Hi John,

Thanks for the replies.

The problem was the ident in the host. Problem solved.

Thanks a lot!

Best Regards,