pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedproblem with pg_hba.conf
3+ messages / 3 participants
[nested] [flat]
* problem with pg_hba.conf
@ 2000-12-13 21:24 Niral Trivedi <niral.trivedi@insage.com>
0 siblings, 0 replies; 3+ messages in thread
From: Niral Trivedi @ 2000-12-13 21:24 UTC (permalink / raw)
To: pgsql-admin
All,
I have already posted this message yesterday but didn't get any reply yet...
Please help me with this..I am having trouble doing db authentication with
'pg_hba.conf' file..
I have added two users using 'CREATE USER' command for a db. And then I have
created two separated password file to do authentication to two different db
in the system.
Also, I have added separate lines in 'pg_hba.conf' file for two db as
follows:
local db1 crypt file1.dat
local db2 crypt file2.dat
But problem is I can connect to both db using both password. What I supposed
to get is, I can connect to 'db1' only with username in 'file1.dat' and
'db2' with username in 'file2.dat'. But that is not happening!!! I have
already commented out the default 'local all trust' line!!!!
Can anybody give some suggestions on this??
Thanks in advance..
Niral
^ permalink raw reply [nested|flat] 3+ messages in thread
* Problem with pg_hba.conf
@ 2015-11-20 11:44 olologin <olologin@gmail.com>
2015-11-20 12:46 ` Re: Problem with pg_hba.conf John Scalia <jayknowsunix@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: olologin @ 2015-11-20 11:44 UTC (permalink / raw)
To: pgsql-admin
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Problem with pg_hba.conf
2015-11-20 11:44 Problem with pg_hba.conf olologin <olologin@gmail.com>
@ 2015-11-20 12:46 ` John Scalia <jayknowsunix@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: John Scalia @ 2015-11-20 12:46 UTC (permalink / raw)
To: olologin <olologin@gmail.com>; +Cc: pgsql-admin
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 -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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2015-11-20 12:46 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-12-13 21:24 problem with pg_hba.conf Niral Trivedi <niral.trivedi@insage.com>
2015-11-20 11:44 Problem with pg_hba.conf olologin <olologin@gmail.com>
2015-11-20 12:46 ` Re: Problem with pg_hba.conf John Scalia <jayknowsunix@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox