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.

Terminology

Controlling_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.

I can think of two possible ways to accomplish this

1. Use a combination of remote user and local database user

Make 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.

1. 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.

2. I don't want this user to be able to view running processes or access anything else.

3. 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_A

2. Use a remote database user and expose PostgreSQL to the public internet

The 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.

1. 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.

2. Can I set PostgreSQL to simply only allow one database to be accessed remotely?

The entire process would be:

Controlling_Machine -> Database_A

What 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)

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.

Terminology

Controlling_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 database

1. Use a combination of remote user and local database user

Make 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.

1. 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.

2. I don't want this user to be able to view running processes or access anything else.

3. 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_A

2. Use a remote database user and expose PostgreSQL to the public internet

The 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.

1. 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.

2. Can I set PostgreSQL to simply only allow one database to be accessed remotely?

The entire process would be:

Controlling_Machine -> Database_A

What 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)