I want to be able to setup a remote user that can update a PostgreSQL database on a production server.I have two machines, one is a local server that I use as a command and control system, named Controlling_Machine. This machine is to login my remote server (Remote_Server) running in production and update a database Database_A.TerminologyControlling_Machine: A linux box that can access the internet, but sites behind a firewall and doesn't accept incoming connections. (Cannot be remotely accessed.)Remote_Server: A production VM of either Centos or Ubuntu running in the cloud.DB_User: The user account on PostgreSQL that can only write to one specific database.Server_User: The user account that SSH's into the server.Database_A: The database I want to be remotely updated via controlling machine.So far I have thought about two ways to enable remote access to the database1. Use a combination of remote user and local database userMake the Server_User login to the server via SSH and restrict all read access except for their home folder. This user can then login to the database with the DB_User for this purpose.I don't want the Server_User to be able to read any other folder, not /etc not /media nothing but home. I want them to be severely restricted.I don't want this user to be able to view running processes or access anything else.The idea is that if Controlling_Machine that launches Server_User is compromised and the attacker logs into Remote_Server, I want to ensure the only damage they can do is to Database_A.The entire process would be:Controlling_Machine -> Remote_Server -> Database_A2. Use a remote database user and expose PostgreSQL to the public internetThe other, probably simpler way to achieve this result is to enable the PostgreSQL database public access so that I can just login with a user for the database. This means I don't have to make another user just for the Remote_Server, as it's no longer involved in the transaction.But is this as secure as a remote user SSH setup into Remote_Server? I would want to use a private/public key for this.Can I set PostgreSQL to simply only allow one database to be accessed remotely?The entire process would be:Controlling_Machine -> Database_AWhat is safer?I would like to use the safest method possible. If putting PostgreSQL on my public IP is too risky, then I will have to keep the database access local on the production server and remotely login via SSH.In addition, if that is the safest way, how do I restrict this remote linux user to nothing but logging into PostgreSQL locally with DB_User (once it has logged in remotely via SSH with Server_User)
Ways to restrict remote user to only access a remote database
ssh;permissions;remote;postgresql