Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
207

Add or edit the following line in your postgresql.conf : listen_addresses = '*' Add the following line as the first line of pg_hba.conf. It allows access to all databases for all users with an encrypted password: # TYPE DATABASE USER CIDR-ADDRESS METHOD host all all 0.0.0.0/0 md5 Restart Postgresql after adding this with service postgresql restart or ...


22

This solution works for IPv4 / IPv6 nano /var/lib/pgsql/data/pg_hba.conf add at final host all all ::1/128 md5 host all postgres 127.0.0.1/32 md5 and then restart postgresql service /etc/init.d/postgresql restart


11

The way I solved this was: Added the line as below in pg_hba.conf: hostnossl all all 0.0.0.0/0 trust and this was modified in postgresql.conf, as shown: listen_addresses = '*' I had this instance running on a Centos 7.3 and Postgres 9.5 in a VM in Azure, given this was a POC (proof of concept) you won't want to connect ...


11

I found out that you need to specify the exact name for the PostgreSQL service, which you can find under the list of services, using systemctl (also see this post): systemctl list-units|grep postgresql postgresql-10.service loaded active running PostgreSQL 10 database server Then you can ...


10

In a fresh install from a few days ago, the second line of my pg_hba.conf is local all all peer I believe this is the one that makes your connection attempt fail. The order of rules matter here: the first one that matches the access method, username, database name and source IP range will be considered. If it fails, then there ...


9

Version 10 and above There is a very convenient view for this called pg_hba_file_rules. table pg_hba_file_rules ; line_number │ type │ database │ user_name │ address │ netmask │ auth_method │ options │ error ─────────────┼───────────┼───────────────┼─────────────┼───────────┼──────────────────────────────────...


7

You can run: postgres=# SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=# .. from within psql. or kill -HUP the postmaster process. Any config validation errors will then get put into the Postgres log file, and it won't reload the config. Example log: 2016-10-05 10:31:57 BST LOG: received SIGHUP, reloading configuration ...


6

Look at the postgresql.auto.conf file in your $PGDATA. If someone uses the ALTER SYSTEM command to change the settings, you will find the value there. postgresql.auto.conf is read after postgresql.confand each setting put in this file overwrite those in postgresql.conf... It's worth giving a look...


4

Fresh Postgres 9.5 install, Ubuntu. The key was the local connection type, since psql uses domain socket connection. pg_hba.conf # TYPE DATABASE USER CIDR-ADDRESS METHOD local all all md5 host all all 0.0.0.0/0 md5


3

Putting adding two entries (one for each IP doesn't work: postgres just sees the first entry, which ether matches or doesn't and completely ignores the second entry. I don't believe that's true. PostgreSQL won't match it if the address is wrong, from the docs on pg_hba.conf Each record specifies a connection type, a client IP address range (if relevant ...


3

2017-08-16 03:44:34 GMT LOG: received fast shutdown request Someone did a pg_ctl -m fast stop then pg_ctl start, either manually or via the Windows service controller (services.msc, net service, ...). If it wasn't you, it was a scheduled job, automatic update for some tool related to PostgreSQL from some unofficial 3rd party, etc. Because unless your ...


3

If you have sudogoer as a role in postgresql, you can alternatively use: sudo systemctl restart postgresql


3

Figured it out. This was a long shot, but it may help someone who has exhausted the normal avenues or is not used to postgres. The server I was working on had another instance running through puppet, and so the config I was modifying was not for the instance I was connecting to. run find / -name 'postgresql.conf' to see if there are any other locations ...


3

Add the following line in the bottom of pg_hba.conf: hostnossl all all 0.0.0.0/0 md5 Add/modify the line in postgresql.conf: listen_addresses = '*' MAKE SURE THAT the user that is connecting has a password: (Example connect user named postgres) a. Run the following psql command with the postgres user account: sudo -u postgres ...


3

I had the same error when I tried to connect to a local database using an SSH tunnel. I solved it by changing the host name from localhost to 127.0.0.1.


3

You are nearly there, just have to process the output from pg_read_file() as it returns each line of pg_hba.conf as a single value. The following query will do the trick: WITH file (line) AS ( SELECT regexp_split_to_array(a, '\t+') FROM regexp_split_to_table(pg_read_file('pg_hba.conf'), '\n') AS t(a) WHERE a ~ '^[^#].*trust\s*$' ) SELECT ...


3

When pg_hba.conf is checked for an authentication request, the first match determines the rule. Quote from the manual The first record with a matching connection type, client address, requested database, and user name is used to perform authentication. There is no “fall-through” or “backup”: if one record is chosen and the authentication fails, ...


2

The problem is: "localhost" can be UNIX sockets, IPv4 or IPv6. The first rule you passed is for UNIX sockets. Try to add a rule for IPv4 and one for IPv6. It should work then. Btw, a SIGHUP is enough in case you change pg_hba.conf.


2

There is likely an invalid entry in pg_hba.conf file. Reload it and then check the PostgreSQL server log for any pg_hba.conf error: pg_ctl reload -D $PGDATA


2

The error message means you are already accessing pg_hba.conf. There is just no line granting access for your connection attempt. At least one of your provided details (host, user, database) does not match for any line in the file. I am not familiar with ElephantSQL, but it seems to be a database hosting service providing standard PostgreSQL. Advice in the ...


2

Yes, two things that need to happen: Your database must be listening on a port and ip to external connections. For this you need to modify postgresql.conf, you may choose to use the new-ish method of ALTER SYSTEM to do that. Your database must be properly configured to authenticate someone coming in externally. To do this you must add a host or preferably ...


2

Read the article Configuring PostgreSQL to Accept Connections From Computers on Your Network where it is explained with screen shots.


2

Starting with PostgreSQL 10, there's a built-in pg_hba_file_rules system view that provides this information. From https://www.postgresql.org/docs/10/static/view-pg-hba-file-rules.html : The view pg_hba_file_rules provides a summary of the contents of the client authentication configuration file, pg_hba.conf. A row appears in this view for each non-...


2

Yes, it is purely a security measure to prevent even momentary unauthorized access. If a hostile party on the same machine knows you are in the process of setting up a new cluster, they could easily test in a loop for it to become connectable with trust authentication. So even if you leave it open for just a few seconds, it could be exploited in that time. ...


2

Upgrading a database with either dump/restore or pg_upgrade will not copy the configuration files, so you are left with whatever pg_hba.conf was there right after initdb created the new cluster. You have to edit and adapt postgresql.conf and pg_hba.conf and other configuration files you use manually on the new cluster. With pg_hba.conf copying the file over ...


1

$ sudo systemctl restart postgresql-10 Or better: $ /usr/pgsql-10/bin/pg_ctl reload Or first (one-time): $ sudo ln -s /usr/pgsql-10/bin/pg_ctl /usr/bin/pg_ctl and then (every time): $ pg_ctl reload Note: pg_ctl cannot be run as root.


1

Check your PostgreSQL pg_hba.conf. It should have lines like this copied directly from the docs. # Allow any user on the local system to connect to any database with # any database user name using Unix-domain sockets (the default for local # connections). # # TYPE DATABASE USER ADDRESS METHOD local all all ...


1

Basic Role and User Management By default, PostgreSQL stores encrypted passwords: create user u_one with password 'password'; select * from pg_authid where rolname = 'u_one'; -[ RECORD 1 ]--+------------------------------------ rolname | u_one rolsuper | f rolinherit | t rolcreaterole | f rolcreatedb | f rolcatupdate | f ...


1

It's not clear what is the point in doing that, but if you're an admin you can do chmod o+r pg_hba.conf to let a non-admin read it. There are two ways in pgAdmin3 to access those files: by reading the file system (if it runs on the same host than the server). This corresponds to the File->Open *.conf commands. With that method, permission-wise, pgAdmin ...


1

The line host all all 0.0.0.0/0 trust Lets anyone in without a password (other than connections over the unix file socket, connections over the loopback device, and replication users). Since it occurs earlier in the file, it takes priority over the x.x.x.x/x line.


Only top voted, non community-wiki answers of a minimum length are eligible