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.
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 launchesServer_User
is compromised and the attacker logs intoRemote_Server
, I want to ensure the only damage they can do is toDatabase_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.
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_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
)
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).