Обсуждение: Problem with pg_hba.conf
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
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@gmail.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@usersE420:/var/lib/postgresql/9.4/main# sudo -u postgres psql -qtWhat pg_hba.conf contains (last lines):
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# 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 restartBut still i cannot login into psql with overseer account:
[ ok ] Restarting postgresql (via systemctl): postgresql.service.root@usersE420:/var/lib/postgresql/9.4/main# psql -qt -U overseerI'm also confused because i think that it's still tries to use peer authentication.
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"
Help with this pls, Ubuntu 15.10, PostgreSQL 9.4
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin