Обсуждение: Can't access to database from webapp in PostgreSQL 9.0, from shell it is Ok.

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,

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

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

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