How To Access My PostgreSQL Server From Another PC Securely?

Question

I want to be able to connect from any PC anywhere to my Database.

Host

1. OS: Windows 10
2. PostgreSQL: 12.0

Currently all Tutorials say the same: (Thats what i also did)

1. Change listen_addresses to listen_addresses = '*' in the postgresql.conf
2. Add host all all 0.0.0.0/0 md5 in the pg_hba.conf

But they don't say how to connect from the Client Side and they don't pinpoint how secure it is to do so.

Let's say at the Host I Did the above steps and restarted. I created a Database called TablesTest.

How do I connect to this database at the Client? How secure is it?

Answer 1

How to connect should be trivial: fill the appropriate values into the pgAdmin dialog (host, port, user, database). If I remember correctly, pgAdmin calls the database "administrative database" or similar.

This is fairly secure, if you want to do better, here are some ideas:

• Use the scram-sha-256 authentication method rather than md5. This requires that you change password_encryption to scram-sha-256 on the server and assign a new password to the user.

• Limit the pg_hba.conf entry so that only one user can connect to one database from a single host, for example

  host databasename username 12.34.56.78/32 scram-sha-256
  

• For better security, don't allow remote connections as a superuser.

• For transport security, you can enable SSL connections. Without that, the communication between database client and database server is unencrypted.