Thread: [GENERAL] Where is pg_hba.conf

 Hi,
I have a Mac with OSX 10.8 installed. It has Postgre 9.1.
According to https://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html
and http://www.thegeekstuff.com/2014/02/enable-remote-postgresql-connection/?utm_source=tuicool
I need to modify the pg_hba.conf file to get access to the DB
remotely.

However, I can't find this file anywhere on the system.
I am able to connec to the server locally with psql. I can also
connect to the server
from the ODBC driver from my program. However doing a global search I
can't find that file.

Can someone please help?

Thank you.

Re: Igor Korot 2017-08-13 <CA+FnnTwxiLXD2H_njPS6yf=nhjaE8vcwWFdVPPzbcuADmXTHgA@mail.gmail.com>
> I need to modify the pg_hba.conf file to get access to the DB
> remotely.
>
> However, I can't find this file anywhere on the system.

Try "SHOW hba_file;".

Christoph

Hi, Christoph,

On Sun, Aug 13, 2017 at 10:09 AM, Christoph Berg <myon@debian.org> wrote:
> Re: Igor Korot 2017-08-13 <CA+FnnTwxiLXD2H_njPS6yf=nhjaE8vcwWFdVPPzbcuADmXTHgA@mail.gmail.com>
>> I need to modify the pg_hba.conf file to get access to the DB
>> remotely.
>>
>> However, I can't find this file anywhere on the system.
>
> Try "SHOW hba_file;".

Last login: Sat Aug 12 23:49:33 on ttys000
/Library/PostgreSQL/9.1/scripts/runpsql.sh; exit
MyMac:~ igorkorot$ /Library/PostgreSQL/9.1/scripts/runpsql.sh; exit
Server [localhost]:
Database [postgres]: draft
Port [5432]:
Username [postgres]:
Password for user postgres:
psql (9.1.24)
Type "help" for help.

draft=# SHOW hba_file
draft-# SHOW hba_file;
         ERROR:  syntax error at or near "SHOW"
LINE 2: SHOW hba_file;
        ^

Thank you.

>
> Christoph

Hi, armand,

On Sun, Aug 13, 2017 at 10:12 AM, armand pirvu <armand.pirvu@gmail.com> wrote:
> Normally should reside in the data dir. My case below
>
> armandps-MacBook-Air:~ armandp$ ps -fu postgres |grep data
>   502 29591     1   0 Thu09PM ??         0:01.63
> /Library/PostgreSQL/9.6/bin/postgres -D /Library/PostgreSQL/9.6/data
>
> armandps-MacBook-Air:~ armandp$ ls -l
> /Library/PostgreSQL/9.6/data/pg_hba.conf
> ls: /Library/PostgreSQL/9.6/data/pg_hba.conf: Permission denied
> armandps-MacBook-Air:~ armandp$ sudo ls -l
> /Library/PostgreSQL/9.6/data/pg_hba.conf
> -rw-------  1 postgres  daemon  4248 Aug 10 21:17
> /Library/PostgreSQL/9.6/data/pg_hba.conf
>
> Is it possible that global search fails from a permission error ?

MyMac:/ igorkorot$ ps -fu postgres |grep data
  502    77     1   0 11:38PM ??         0:00.45
/Library/PostgreSQL/9.1/bin/postmaster -D/Library/PostgreSQL/9.1/data
  502  1733   243   0 10:42AM ??         0:00.05
/System/Library/Frameworks/CoreServices.framework/Frameworks/Metadata.framework/Versions/A/Support/mdworker
-s mdworker -c MDSImporterWorker -m com.apple.mdworker.shared
MyMac:/ igorkorot$ ls -la /Library/PostgreSQL/9.1/data/
ls: : Permission denied
MyMac:/ igorkorot$ su
Password:
sh-3.2# ls -la /Library/PostgreSQL/9.1/data/
total 88
drwx------   20 postgres  daemon    680 Aug 12 23:38 .
drwxr-xr-x   16 root      daemon    544 Dec  4  2016 ..
-rw-------    1 postgres  daemon      4 Dec  4  2016 PG_VERSION
drwx------    7 postgres  daemon    238 Dec  9  2016 base
drwx------   42 postgres  daemon   1428 Aug 12 23:39 global
drwx------    3 postgres  daemon    102 Dec  4  2016 pg_clog
-rw-------    1 postgres  daemon   4222 Dec  4  2016 pg_hba.conf
-rw-------    1 postgres  daemon   1636 Dec  4  2016 pg_ident.conf
drwxr-xr-x  204 postgres  daemon   6936 Aug 13 00:00 pg_log
drwx------    4 postgres  daemon    136 Dec  4  2016 pg_multixact
drwx------    3 postgres  daemon    102 Aug 12 23:38 pg_notify
drwx------    2 postgres  daemon     68 Dec  4  2016 pg_serial
drwx------    3 postgres  daemon    102 Aug 13 10:43 pg_stat_tmp
drwx------    3 postgres  daemon    102 Dec  4  2016 pg_subtrans
drwx------    2 postgres  daemon     68 Dec  4  2016 pg_tblspc
drwx------    2 postgres  daemon     68 Dec  4  2016 pg_twophase
drwx------    5 postgres  daemon    170 Dec 11  2016 pg_xlog
-rw-r--r--    1 postgres  daemon  19162 Dec  4  2016 postgresql.conf
-rw-------    1 postgres  daemon     70 Aug 12 23:38 postmaster.opts
-rw-------    1 postgres  daemon     75 Aug 12 23:38 postmaster.pid
sh-3.2#

Apparently it looks like the failure is from the permission.

So how do I enable it? Or it has to stay for "root" only?

Thank you.

>
> Hope this helps
>
>
>
> On Aug 13, 2017, at 9:00 AM, Igor Korot <ikorot01@gmail.com> wrote:
>
> Hi,
> I have a Mac with OSX 10.8 installed. It has Postgre 9.1.
> According to
> https://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html
> and
> http://www.thegeekstuff.com/2014/02/enable-remote-postgresql-connection/?utm_source=tuicool
> I need to modify the pg_hba.conf file to get access to the DB
> remotely.
>
> However, I can't find this file anywhere on the system.
> I am able to connec to the server locally with psql. I can also
> connect to the server
> from the ODBC driver from my program. However doing a global search I
> can't find that file.
>
> Can someone please help?
>
> Thank you.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>

Re: Igor Korot 2017-08-13 <CA+FnnTz8h-2tkRMv4uYN-fkRFU601cws9-uS7vwfpr-fka0edg@mail.gmail.com>
> draft=# SHOW hba_file
> draft-# SHOW hba_file;
>          ERROR:  syntax error at or near "SHOW"
> LINE 2: SHOW hba_file;
>         ^

Standard beginners error. If you forgot the ";" on the first line,
it'll process both lines as a single, erroneous command. Note the
"-#" prompt.

If you run into that situation again, hit ^C.

Christoph

Hi,
OK, I found it under the root account.

Now I am modifying it as follows:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             192.168.1.3/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                                md5
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

Should "METHOD" column be lept as 'md5' or as 'trusted' as in the link
I posted in the OP?

Thank you.


On Sun, Aug 13, 2017 at 10:37 AM, Christoph Berg <myon@debian.org> wrote:
> Re: Igor Korot 2017-08-13 <CA+FnnTz8h-2tkRMv4uYN-fkRFU601cws9-uS7vwfpr-fka0edg@mail.gmail.com>
>> draft=# SHOW hba_file
>> draft-# SHOW hba_file;
>>          ERROR:  syntax error at or near "SHOW"
>> LINE 2: SHOW hba_file;
>>         ^
>
> Standard beginners error. If you forgot the ";" on the first line,
> it'll process both lines as a single, erroneous command. Note the
> "-#" prompt.
>
> If you run into that situation again, hit ^C.
>
> Christoph

Also, I presume that the address in this file is the address of the
machine where the server is located, not the address from where the
connection is initiated.


On Sun, Aug 13, 2017 at 10:53 AM, Igor Korot <ikorot01@gmail.com> wrote:
> Hi,
> OK, I found it under the root account.
>
> Now I am modifying it as follows:
>
> # TYPE  DATABASE        USER            ADDRESS                 METHOD
>
> # "local" is for Unix domain socket connections only
> local   all             all                                     md5
> # IPv4 local connections:
> host    all             all             192.168.1.3/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                                md5
> #host    replication     postgres        127.0.0.1/32            md5
> #host    replication     postgres        ::1/128                 md5
>
> Should "METHOD" column be lept as 'md5' or as 'trusted' as in the link
> I posted in the OP?
>
> Thank you.
>
>
> On Sun, Aug 13, 2017 at 10:37 AM, Christoph Berg <myon@debian.org> wrote:
>> Re: Igor Korot 2017-08-13 <CA+FnnTz8h-2tkRMv4uYN-fkRFU601cws9-uS7vwfpr-fka0edg@mail.gmail.com>
>>> draft=# SHOW hba_file
>>> draft-# SHOW hba_file;
>>>          ERROR:  syntax error at or near "SHOW"
>>> LINE 2: SHOW hba_file;
>>>         ^
>>
>> Standard beginners error. If you forgot the ";" on the first line,
>> it'll process both lines as a single, erroneous command. Note the
>> "-#" prompt.
>>
>> If you run into that situation again, hit ^C.
>>
>> Christoph

Also, I presume that the address in this file is the address of the
machine where the server is located, not the address from where the
connection is initiated.

On Sun, 13 Aug 2017 10:55:00 -0400, Igor Korot <ikorot01@gmail.com>
wrote:

>Also, I presume that the address in this file is the address of the
>machine where the server is located, not the address from where the
>connection is initiated.

No.  The addresses / network segments in the file specify from where
the client(s) can connect.

George

Hello,

On Sun, Aug 13, 2017 at 10:00:23AM -0400, Igor Korot wrote:
> However, I can't find this file anywhere on the system.
> I am able to connec to the server locally with psql. I can also
> connect to the server
> from the ODBC driver from my program. However doing a global search I
> can't find that file.
>
> Can someone please help?

You can execute the query:

=# select setting from pg_settings where name = 'hba_file';

--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company