Re: Problem with pg_hba.conf

[John Scalia <jayknowsunix@xxxxxxxxx>]

First off, you don't need to restart your postgrsql server once you've edited the pg_hba.conf file, just issue a reload which forces the server to reread this file. Now, about the contents itself. Your first line is only permitting the "postgres" user to connect locally. Please look at the format which you even show in your email. The first field is TYPE, then DATABASE, then USER, and finally METHOD. Your psql command should work if you use "psql -qt -h localhost -U overseer" as that forces the connection over tcp/ip and not a *nix pipe. Finally, look in your $PGDATA/pg_log directory at what your server is saying about connection attempts. That can be invaluable. 

Hope this helps you,

Jay

On Fri, Nov 20, 2015 at 5:44 AM, olologin <olologin@xxxxxxxxx> wrote:

Hello folks, i have some weird problem which i cannot solve. I'm trying to setup PostgreSQL for first time (I've never used it before, and maybe this question is too newbie, but still):

root@usersE420:/var/lib/postgresql/9.4/main# sudo -u postgres psql -qt
postgres=# \l
 overseer  | overseer | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 ..........

postgres=# \du
 overseer  |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}

postgres=# show hba_file;
 /etc/postgresql/9.4/main/pg_hba.conf

What pg_hba.conf contains (last lines):

# Database administrative login by Unix domain socket
local   all             postgres peer

# TYPE  DATABASE        USER            ADDRESS METHOD

# "local" is for Unix domain socket connections only
local   all             all md5
# IPv4 local connections:
host    all             all             127.0.0.1/32 md5
# IPv6 local connections:
#host    all             all             ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres peer
#host    replication     postgres        127.0.0.1/32 md5
#host    replication     postgres        ::1/128 md5

After editing of this file i restarted server with:

root@usersE420:/var/lib/postgresql/9.4/main# /etc/init.d/postgresql restart
[ ok ] Restarting postgresql (via systemctl): postgresql.service.

But still i cannot login into psql with overseer account:

root@usersE420:/var/lib/postgresql/9.4/main# psql -qt -U overseer
psql: FATAL:  Peer authentication failed for user "overseer"
root@usersE420:/var/lib/postgresql/9.4/main# psql -qt -U overseer -W
Password for user overseer:
psql: FATAL:  Peer authentication failed for user "overseer"

I'm also confused because i think that it's still tries to use peer authentication.

Help with this pls, Ubuntu 15.10, PostgreSQL 9.4


--
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin