2

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)

  • 1
    Shouldn't the title be "Ways to restrict remote user to only access a remote database" (both inserting only as well ad dropping the caps). The this in "to accomplish this" is that referring to the title? I – Anthon Apr 24 '16 at 7:08
  • @Anthon, Thanks for the good suggestions. I have made some updates to make the question clearer. – Joseph Apr 24 '16 at 7:32
  • Do you want to make a tcp connection (e.g. to port 5432, postgres' default port) or run psql over ssh? If the former, you need to use a tunnel of some kind to securely provide access (e.g. ssh port-forwarding). If the latter, ssh user@host psql will work as long as there's a valid route to the pg host (and there's no firewall blocking inbound ssh). – cas Apr 25 '16 at 1:26
  • @Joseph, did you state which Linux distro you are doing this in? – Daniel Nov 4 '18 at 12:54
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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.