One requirement in some projects is to expose the database to read the clients of them. The client need a URL, User and Password to access to our DB.
Use a VPN to secure the connection, but this is a overkill security requirement because need a lot of maintenance to be working property.
On the other hand, we propose a Secure TCP/IP Connection with SSL.
To create this secure connection we need do the nexts steps:
In the Postgres configuration file we need to change the next lines:
listen_addresses = 0.0.0.0port = 12345 --> Use a secure port, out of next range 1024 - 10000One tip to be more secure is deny the superuser access:
superuser_reserved_connections = 0and remove the superuser line of thepg_hba.confif exists.
In the Postgres configuration file we need to change the next lines:
ssh: trueWe propose create a SQL view to acces to the data. We do not expose the entire data schema, only the data that the provider needs. By this reason, we create a SQL view to expose only the data that we want expose:
view to access to the required data.We need create a user to access our data:
pg_hba.confOnce time we have the user, the view with the data and the GRANT permissions, we need access to this user from out of the server with a URI, user and password.
To make this possible, we need change the pg_hba.conf file:
pg_haba.conf: hostssl <db-name> <username> <IP>/32 md5You need to be sure that you don’t have other entry that give access with other permission to the db. Only the localhost and the IP that you know.
schema where the view is created and only create GRANT permission rules for this schema. With this change only this schema relations can to be viewed by th user.'man in the middle'