Re: Problem with pg_hba.conf

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(at)gmail(dot)com> 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(at)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(at)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(at)usersE420:/var/lib/postgresql/9.4/main# psql -qt -U overseer
>> psql: FATAL: Peer authentication failed for user "overseer"
>> root(at)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>