I need to give access to 1 database for x users and y ip addresses. I'd like to avoid many lines is there a way to use a file to read a list of users, ip-addresses from, like ...

host   db_name         scram-sha-256

if so, how does the syntax look like?

I have installed the PostgreSQL 9.4 on my MacBook via MacPorts, and I am trying to edit pg_hba.conf, but I don't see it in my current installation. Furthermore, psql is reporting that config_file is in my old 9.3 directory! How should I fix this? $ port select --list postgresql Available versions for postgresql: none postgresql93 postgresql94 (active) $ psql --version psql (PostgreSQL) 9.4.1 $ psql -U postgres -c 'SHOW config_file' config_file ---------------------------------------------- /Library/PostgreSQL/9.3/data/postgresql.conf (1 row) $ ls /Library/PostgreSQL/9.3/data PG_VERSION pg_hba.conf pg_notify pg_stat_tmp pg_xlog base pg_ident.conf pg_serial pg_subtrans postgresql.conf global pg_log pg_snapshots pg_tblspc postmaster.opts pg_clog pg_multixact pg_stat pg_twophase postmaster.pid $ ls /Library/PostgreSQL/9.4/data ls: /Library/PostgreSQL/9.4/data: No such file or directory $ ls /Library/PostgreSQL/9.4 ls: /Library/PostgreSQL/9.4: No such file or directory $ psql -U postgres -c 'SHOW data_directory' data_directory ------------------------------ /Library/PostgreSQL/9.3/data (1 row) $ locate pg_hba.conf /Library/PostgreSQL/9.3/share/postgresql/pg_hba.conf.sample /opt/local/share/postgresql93/pg_hba.conf.sample /opt/local/share/postgresql94/pg_hba.conf.sample $ ls /opt/local/share/postgresql94 conversion_create.sql postgres.bki snowball_create.sql extension postgres.description sql_features.txt information_schema.sql postgres.shdescription system_views.sql pg_hba.conf.sample postgresql.conf.sample timezone pg_ident.conf.sample psqlrc.sample timezonesets pg_service.conf.sample recovery.conf.sample tsearch_data

I want to connect to postgres-12 from a postgres-14 server in order to get dumps from the remote. Apparently there is a connection problem when trying to access the remote postgres server though.

postgres@dvzsn-rd5482:> hostname -I
10.4.91.68
postgres@dvzsn-rd5482:> psql -p 5449 -h 10.4.91.32
psql: error: connection to server at "10.4.91.32", port 5449 failed: FATAL:  no pg_hba.conf entry for host "10.4.91.68", user "postgres", database "postgres", SSL off

so it complains about a missing pg_hba.conf entry on the remote for 10.4.91.68 however ...

postgres@dvzsn-rd1941:> psql -p 5449 -c "SELECT * FROM pg_hba_file_rules WHERE address = '10.4.91.68';"
 line_number |   type    |  database  | user_name  |  address   |     netmask     | auth_method | options | error
-------------+-----------+------------+------------+------------+-----------------+-------------+---------+-------
          92 | host      | {all}      | {all}      | 10.4.91.68 | 255.255.255.255 | md5         |         |
          93 | hostnossl | {postgres} | {postgres} | 10.4.91.68 | 255.255.255.255 | md5         |         |
(2 rows)

in my eyses the first line should be sufficient even. Any idea on what I am not seeing? It does not seem to be listen_addresses ``` postgres@dvzsn-rd1941:> psql -c "SHOW listen_addresses" listen_addresses ------------------ * `

I'm running Postgres16 on Windows server. Start researching on pg_basebackup - when I execute the following command, pg_basebackup.exe -h SERVERNAME -U postgres -p 5432 -D Z:\PostgreSQL\16\backups\ -Fp -Xs -P -R --checkpoint=fast --max-rate=1024M I'm getting the following error: > pg_basebackup: error: connection to server at "servername" port 5432 > failed: FATAL: no pg_hba.conf entry for replication connection from > host, user "postgres", no encryption I think it's because I added the SSPI connections . I have the following entries before the sspi but still getting error host all all 0.0.0.0/0 scram-sha-256 host all all 127.0.0.1/32 scram-sha-256 host all all ::0/0 sspi map=sspi How do I fix pg_hba so I can use sspi for certain usernames will able to allow connections for local logins (much like mixed mode in MSSQL)? Thank you

I am trying to run a website sent to me but after doing so this error appeared > connect to PostgreSQL server: FATAL: no pg_hba.conf entry for host "4X.XXX.XX.XXX", user "userXXX", database "dbXXX", SSL off in C:\xampp\htdocs\xmastool\index.php on line 37 I found this answer that says that I just need to add an entry in the pg_hba.conf file for that particular user. This is my pg_hba.conf file. # TYPE DATABASE USER ADDRESS METHOD # IPv4 local connections: local dbXXX userXXX md5 host dbXXX userXXX XX.XXX.XXX.XXX md5 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. #host replication postgres 127.0.0.1/32 md5 #host replication postgres ::1/128 md5 but after doing so, the error still persists. I restarted my XAMPP server several times but nothing changes. What do I need to change in pg_hba.conf?

I just installed PostgreSQL 9.4 on Ubuntu 15.10. 1. I created a user with createuser -P myuser 2. I created a database with createdb -O myuser mydatabase 3. I edited pg_hba.conf and added local mydatabase myuser md5 4. I restarted PostgreSQL with sudo service postgresql restart User *myuser* is a PostgresSQL user only and has no user account on Ubuntu. **When I try to connect to the database with psql -W mydatabase myuser it fails with psql: FATAL: Peer authentication failed for user "myuser".** PostgreSQL is running … ● postgresql.service - PostgreSQL RDBMS Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled) Active: active (exited) since Thu 2016-03-03 09:53:00 CET; 9min ago Process: 22219 ExecStart=/bin/true (code=exited, status=0/SUCCESS) Main PID: 22219 (code=exited, status=0/SUCCESS) Mar 03 09:53:00 SERVER01 systemd: Starting PostgreSQL RDBMS... Mar 03 09:53:00 SERVER01 systemd: Started PostgreSQL RDBMS. ... and listening. Proto Recv-Q Send-Q Local Address Foreign Address State tcp 0 0 localhost:postgresql *:* LISTEN tcp6 0 0 localhost:postgresql [::]:* LISTEN Active UNIX domain sockets (only servers) Proto RefCnt Flags Type State I-Node Path unix 2 [ ACC ] STREAM LISTENING 151534 /var/run/postgresql/.s.PGSQL.5432 **What do I have to do to connect with user *myuser* to database *mydatabase*?**

Please help me to figure it out, help is much appreciated! ERROR: FATAL: no pg_hba.conf entry for replication connection from host "172.18.0.4", user "repmgr", SSL on The following is a list of my pg_hba entries: The following is the query output from pg_hba_file_rules: line_number | type | database | user_name | address | netmask | auth_method | options | error ------------+-------+---------------+------------+------------+-----------------------------------------+-------------+---------+------- 85 | local | {all} | {postgres} | | | peer | | 90 | local | {all} | {all} | | | peer | | 92 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | md5 | | 94 | host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | md5 | | 97 | local | {replication} | {all} | | | peer | | 98 | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | md5 | | 99 | host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | md5 | | 101 | host | {all} | {repmgr} | 172.18.0.0 | 255.255.255.0 | trust | | 102 | host | {replication} | {repmgr} | 172.18.0.0 | 255.255.255.0 | trust | | As you can see, the pg_hba.conf did get loaded to the database. Where did I do wrong?

I am running postgres 9.6 cluster on ubuntu 20. All installation and starting the database is fine, however, I am experiencing a weird behavior. In the pg_hba.conf file, I am trusting any connection from any user from the local host and it is working just fine when I start the cluster using the "pg_ctl start" utility. However, when I start it from the systemd, systemctl start postgresql@9.6-main, it fails with the following error Anyone has proper explanation for this difference in behaviour ?

Many times, the postgresql service can't restart after some configuration changes. Is there any command line tool allowing to check the syntax of pg_hba.conf and other pg *.conf files before reloading/restarting the service, or even better, after any config change ?

My PostgreSQL server unexpectedly restarted with such messages: 2017-08-16 03:44:34 GMT LOG: received fast shutdown request 2017-08-16 03:44:34 GMT LOG: aborting any active transactions 2017-08-16 03:44:34 GMT FATAL: terminating connection due to administrator command 2017-08-16 03:44:34 GMT FATAL: terminating connection due to administrator command 2017-08-16 03:44:34 GMT LOG: autovacuum launcher shutting down 2017-08-16 03:44:34 GMT LOG: shutting down 2017-08-16 03:44:34 GMT LOG: database system is shut down 2017-08-16 03:46:04 GMT LOG: incomplete startup packet 2017-08-16 03:46:04 GMT LOG: database system was shut down at 2017-08-16 03:44:34 GMT 2017-08-16 03:46:04 GMT LOG: MultiXact member wraparound protections are now enabled 2017-08-16 03:46:04 GMT LOG: database system is ready to accept connections 2017-08-16 03:46:04 GMT LOG: autovacuum launcher started what could be the reason? Configuration details: OS: Ubuntu 16.04.1 LTS (Windows Azure VM) PostgreSQL version: 9.5.8 listen address: * (my bad, but Azure have to block another ports from external access. I didn't enable PG 5432 port for external access) HBA config: local all postgres ident host all all 127.0.0.1/32 md5 host all all ::1/128 md5 host all all 10.0.0.0/24 md5 host replication app_replicator 10.0.0.5/32 md5 local all all peer

I am working on setting up LDAP authentication for a PostgreSQL database and have added the following in my pg_hba.conf file:

# TYPE  DATABASE    USER    ADDRESS     METHOD
host    all         all     all         ldap ldapurl="ldap://example.local/dc=example,dc=local?sAMAccountName" ldapbinddn="username" ldapbindpasswd="password"

It works! But now I'd like to store the username and password as environment variables, like I do for most of my config settings. I've tried adding the username and password as $LDAPUSERNAME or "$LDAPUSERNAME" but they aren't inserting any values. Is this possible? Note: The database will be running in a docker container. If I have to run some script to process the pg_hba.conf file before starting the database, I can.

Let's say I have the following 3 child IP addresses - 192.168.10.15 ,192.168.10.41 and 192.168.10.81 1 master IP address - 192.168.10.37 I want the 3 child IP addresses to be able to access the master IP address. Now, in order to do this, the pg_hba.conf file of the Master must contain an entry for each of the child IP addresses. My question is instead of having 3 separate entries like this, host all all 192.168.10.15/32 md5 host all all 192.168.10.41/32 md5 host all all 192.168.10.81/32 md5 Can it have 1 entry for all 3, something like host all all 192.168.10.15/32,192.168.10.41/32,192.168.10.81/32 md5 I tried the above method but none of the 3 IPs got the access. Is it even possible?

I am hosting two different databases (manage_invite and manage_invite_v49) on the same server. As you can see, when I try to connect them using the CLI, it demands the password for the second database only. But they are on the same server therefore the pg_hba.conf file is the same for both database. **Do you know what could the issue be?** I would like my manage_invite_v49 db to be accessible without a password, as the first one. Here is my pg_hba.conf file:

# 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                                     trust
# 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     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

I am trying to run a function periodically with pg_cron but every time it wants to execute it throws connection refused. the cron job is created using the postgres user. pg_hba.conf

local   all             postgres                                md5
# TYPE  DATABASE        USER            ADDRESS                 METHOD

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

I've tried adding a .pgpass file but, I don't know where to put it. this is my machine version: Debian GNU/Linux 10 (buster)

I have the following pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

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

With the above I assume host all all 127.0.0.1/32 trust would allow TCP connections from localhost. However this does not seem to be the case

[root@XenonKiloCranberry:~]# psql -U postgres
psql (11.7)
Type "help" for help.

postgres=# \q

[root@XenonKiloCranberry:~]# psql -U postgres -h 127.0.0.1
psql: FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "postgres", database "postgres", SSL off

Where am I going wrong? Output of select * from pg_hba_file_rules:

line_number | type  | database | user_name | address |                 netmask                 | auth_method | options | error 
-------------+-------+----------+-----------+---------+-----------------------------------------+-------------+---------+-------
           1 | local | {all}    | {all}     |         |                                         | trust       |         | 
           2 | host  | {all}    | {all}     | ::1     | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust       |         | 
(2 rows)

We're trying to connect to a postgres/postgis installation on a remote server, using QGIS-Application. When accessing from another pc or from the same pc via pgadmin4v4 with the same credential/authentification settings, it works just fine. Therefore I conclude the pg_hba.conf is ok and it's in fact a problem of QGIS. In order to better understand the problem and maybe file a bug for QGIS I need help analysing the error message. It sais:

Area: dbname = , host , port 5432, user , password , authcfg = 

SSL error: certificate verify failed
FATAL: no pg_hba.conf entry for host , user , database , SSL off

My main questions: 1. shouldn't the host-ip addresses be identical? Could it be part of the problem, that the second ip mentioned is not the correct server-ip? As far as I can see, there should be only one host (remote server) and one client involved (pc trying to get access). 2. why doesn't it say "no pg_hba.conf entry for **client** ..." 3. does QGIS think is it is a certificate problem, or a pg_hba.conf problem, or is that both the same *Update: further Details about the installation* - Client-PC1: Win10, QGIS 3.4 [error] pgAdmin 4v4 [access ok] - Client-PC2: Win10, QGIS 3.10 [access ok] pgAdmin 4v4 [access ok] - Server: postgres 12 - 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                                     peer
# IPv4 local connections:
host    all             all             0.0.0.0/0               scram-sha-256
# IPv6 local connections:
hostssl all             all             ::0/0                   scram-sha-256

I want to test if a replication connection is authorized by pg_hba.conf on the provider before issuing the replication-starting command, and don't know how. (I have access to both unix and postgresql shells on both nodes) For the non-replication connection, I would connect psql using a connstring like 'host=$MASTER_IP port=5432 dbname=$DATABASE user=$DBUSER password=$DBPASSWORD' **Context:** I am writing a script to automate the setup of replication between servers, and configuration of the servers is managed through different systems/repositories (legacy reasons). Therefore, I want to test if settings are all right at each step.

I have 5 Postgres Servers, with IP addresses let's say as A,B,C,D and E. A is the master server and remaining are the child servers which need to access A. In the postgresql.conf file of A, the listen address has been set to '*'. Now, let's say I want B to access A remotely. For that, the pg_hba.conf file of A should contain an entry with the IP address of B (along with other necessary data), like this for example: host all all IP_Address_of_B 32 md5 But, the problem which I am facing is that even if I am entering the details of any one of the child servers in the pg_hba.conf file of A, the access to A is enabled to all of the child servers. And if I remove that entry, then the access is disabled for all. As far as I know, if the entry of B is present in the pg_hba.conf file of A, the only B must be allowed to access A, not the remaining. But, in my case, either all have the access or none. Is their a way to enable only B (or only a selected servers, not all) to access A ?

Whatever settings I try, I keep getting connection errors, from external clients as well as trying to connect from a running console on the server itself. My last attempt for a local connection with "user_test":

psql -h /var/run/postgresql -p5433 -U user_test

When I checked the log on /var/log/postgresql/postgresql-12-db_test.log he last line on the log puzzled me: why is the log referring to "line 90" in the pg_hba.conf? That line should not exist???

2019-12-23 00:24:50.620 CET  LOG:  listening on IPv4 address "0.0.0.0", port 5433
2019-12-23 00:24:50.620 CET  LOG:  listening on IPv6 address "::", port 5433
2019-12-23 00:24:50.623 CET  LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5433"
2019-12-23 00:24:50.649 CET  LOG:  database system was shut down at 2019-12-23 00:24:50 CET
2019-12-23 00:24:50.654 CET  LOG:  database system is ready to accept connections
2019-12-23 00:25:22.452 CET  user_test@user_test LOG:  provided user name (user_test) and authenticated user name (postgres) do not match
2019-12-23 00:25:22.452 CET  user_test@user_test FATAL:  Peer authentication failed for user "user_test"
2019-12-23 00:25:22.452 CET  user_test@user_test DETAIL:  Connection matched pg_hba.conf line 90: "local   all             all                                     peer"

Here's a summary of my setup: 0. Fresh installation of postgesql 12 on ubuntu cloud-server 1. created a cluster "db_test" (alongside to the standard installation "main") 2. added a role "admin" and assigned user "user_test" to the cluster 3. told postgres to listen to all ports (ALTER SYSTEM SET listen_addresses='*';) 4. modified pg_hba.conf (sudo nano /var/lib/postgresql/12/db_test/pg_hba.conf) That file currently reads (had many other settings none worked):

# TYPE  DATABASE USER ADDRESS METHOD
local all user_test trust
hostssl all all 31.164.122.223 md5

5. restarted the process using either:

postgres@servername:~$ sudo service postgresql@12-db_test restart

or

pg_ctlcluster 12 db_test start

[This other Question](https://dba.stackexchange.com/q/220700/19079) has Answers about viewing and editing settings in postgresql.conf file using pgAdmin 4. But those Answers did not mention editing the pg_hba.conf file used for authentication of users. ➥ How to use *pgAdmin 4* for editing the pg_hba.conf file?