Postgresql – How To Access My PostgreSQL Server From Another PC Securely

connectivitypostgresql

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?

Best Answer

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.

Related Question