Thread: 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,
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
Re: Can't access to database from webapp in PostgreSQL 9.0, from shell it is Ok.
From
Andre Lopes
Date:
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
Re: Can't access to database from webapp in PostgreSQL 9.0, from shell it is Ok.
From
Andre Lopes
Date:
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