Ways to restrict remote user to only access a remote database

Question

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)

Answer 1

I would go for using ssh and restrict the command(s) that can be run by that user. At least that way you know the connection is secure. You can do so through an entry in /etc/ssh/sshd_config:

Match User your_user
   X11Forwarding no
   AllowTcpForwarding no
   ForceCommand your-command arg1 arg2

This might work for you if you, through the arguments, can force your-command to use the database of your choice. (Alternatively you can do this in the authorized_keys file based on the public key of Server_User).

I don't know the details of PostgreSQL, but it is possible that within the commandline tool you use to manipulate the database, you can just change the database that you are working on. If so you are not going to be able to restrict things. It might even have some temporarily open a (bash) subshell which is an even larger problem for your case.

I would therefore first look at what you want the user to do. If that is restricted to making a backup, restoring a backup, and a few other functions, it probably makes more sense to either create a small program that is restricted to executing this functionality, or only allow copying of command files to the server with a cron job then handling processing of the commands (and discarding anything outside of the allowed things).