Configure PostgreSQL to allow remote connection
Postgres.app is a simple, native macOS app that runs in the menubar without the need of an installer. Open the app, and you have a PostgreSQL server ready and awaiting new connections. Close the app, and the server shuts down. PostgreSQL can also be installed on macOS using Homebrew. Please see the Homebrew documentation. GUI Client Apps. There are many clients for PostgreSQL on the Mac. You can find many of them in the Community Guide to PostgreSQL GUI Tools in the PostgreSQL wiki. Some of them are quite powerful; some are still a bit rough.
By default PostgreSQL is configured to be bound to 'localhost'.
As we can see above port 5432 is bound to 127.0.0.1. It means anyattempt to connect to the postgresql server from outside the machine will be refused.We can try hitting the port 5432 by using telnet.
Configuring postgresql.conf
In order to fix this issue we need to find postgresql.conf. Indifferent systems it is located at different place. I usually search forit.
Open postgresql.conf file and replace line
with
Now restart postgresql server.
Here we can see that 'Local Address' for port 5432 has changed to 0.0.0.0.
Configuring pg_hba.conf
Let's try to connect to remote postgresql server using 'psql'.
In order to fix it, open pg_hba.conf and add following entry at thevery end.
The second entry is for IPv6 network.
Do not get confused by 'md5' option mentioned above. All it means isthat a password needs to be provided. If you want client to allowcollection without providing any password then change 'md5' to 'trust'and that will allow connection unconditionally.
Restart postgresql server.
You should be able to see list of databases.
Now we are able to connect to postgresql server remotely.
Please note that in the real world you should be using extra layer ofsecurity by using 'iptables'.
Summary: in this tutorial, we will show you step by step how to reset the password of the postgres user in PostgreSQL.
For some reason, after installing PostgreSQL, you may forget the password of the postgres user. In this case, you need to know how to reset the password.
PostgreSQL uses the pg_hba.conf configuration file stored in the database data directory (e.g., C:Program FilesPostgreSQL12data on Windows) to control the client authentication. The hba in pg_hba.conf means host-based authentication.
To reset the password for the postgres user, you need to modify some parameters in this configuration file, login as postgres without a password, and reset the password.
The following steps show you how to reset a password for the postgres user:
Step 1. Backup the pg_hba.conf file by copying it to a different location or just rename it to pg_hba.conf.bk
Step 2. Edit the pg_dba.conf file and change all local connections from md5 to trust. By doing this, you can log in to the PostgreSQL database server without using a password.
Step 3. Restart the PostgreSQL server. If you are on Windows, you can restart the PostgreSQL from Services:
Or run the following command from the window terminal:
The 'C:Program FilesPostgreSQL12data' is the data directory.
Step 4. Connect to PostgreSQL database server using any tool such as psql or pgAdmin:
Postgres Mac Os X Password
PostgreSQL will not require a password to login.
Step 5. Execute the following command to set a new password for the postgres user.
Mac Postgres Password Recovery
Step 6. Restore the pg_dba.conf file, restart the PostgreSQL database server and connect to the PostgreSQL database server with the new password.
Stop Postgres Mac
In this tutorial, you have learned how to reset the password of the postgres user.