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

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

From
Andre Lopes
Date:
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:

[article]
Here is how I do to create a Postgres user with the same username as
my regular login in Linux Ubuntu.

Go to your terminal with your regular user and do:

{{{
yourusername$ sudo su - postgres
}}}

Ok, now you are as postgres user. To access to postgres do:

{{{
postgres$ psql
}}}

Now that you are in postgres terminal, do this:

{{{
postgres=# create user yourusername with createdb createrole password
'newpassword';
}}}

an then do:

{{{
postgres=# create database yourusername with owner yourusername;
}}}

And now you can do this:

{{{
postgres=# \q
postgres$ exit
yourusername$ psql
...
yourusername=# ......
}}}

Remember that this will create a postgres user with the same username
as your regular login, a password, and the privileges to create more
databases and users.
[/article]

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?

Best Regards,

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

From
"David Johnston"
Date:
-----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
methodto 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.



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

From
Adrian Klaver
Date:
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

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

From
John R Pierce
Date:
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 Fri, Dec 2, 2011 at 11:34 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> 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

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

From
John R Pierce
Date:
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,


On Fri, Dec 2, 2011 at 11:46 PM, John R Pierce <pierce@hogranch.com> wrote:
> 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general