4

Is it possible to ensure that dumps taken from a PostgreSQL db are always encrypted?

The data in the database is highly sensitive and we cannot afford un-authorized personnel, including Sys Admins who need to backup the db, to access the actual data.

3 Answers 3

3

There's nothing built in to PostgreSQL. Your best bet is to encrypt it with an external program.

pg_dump ... | encrypt  > mydump.pg.enc
1
2

Is it possible to ensure that dumps taken from a PostgreSQL db are always encrypted?

Yes. You would need to create a mechanism (probably a script or program) that would perform the backup and provide a dump that is encrypted as the result. The administrators would then run the script and observe the process, but would not be able to devine the contents of the dump.

You would also need to secure the raw data in such a way that it cannot be seen, probably putting it on an encrypted volume that has a password that can only be entered by specific personnel. Access to that volume would then be constrained to a very specific set of users and services, in effect closing off any means of replicating the data for examination elsewhere. This means on Windows machines, that they would not be domain administrators (who can bypass security); on Linux/BSD, they would not be able to 'become root' (which also bypasses security). This creates a huge burden in terms of administration of the machines you'd be using, because many administrative functions require access at that level. That's not to say it can't be done, but that it will create an enormous overhead in how they perform their duties. You'll need to find creative ways to get scripts to run other scripts that have elevated priviledges (and so on).

The easiest solution would be to physically isolate the server and only have specific, trusted personnel perform the backup sequence by running a script that creates the encrypted dump file. Access to the data would be through a normal (port 5432) PostgreSQL connection, but no other network access would be allowed. It also implies that your SysAdmins would not be allowed access to the server in question.

The data in the database is highly sensitive and we cannot afford un-authorized personnel, including Sys Admins who need to backup the db, to access the actual data.

Then you have a fundamental quandary. Your admins are people that you should have in a position of trust. This is because the majority of administrative access on a computer bypasses security checks so that the administrator can perform their duties.

You can certainly create tools and methods that separate them from direct access of the data, but to be frank, a clever admin will be able to work around it with little effort. This is where trust comes into play.

Remember, integrity and trust are the real value of an SysAdmin, and what separates them from other users. It is up to them to ensure the integrity of your data and to keep the trust of your users in their systems. Without either, well...you're better off performing the duty yourself.

1

A sysadmin that backups the DB is IMHO always able to look into the tables, so the table contents themselves have to be locked down. The only way to achive this is to encrypt the data before you store it in the DB, but this might increase query complexity and index creation a bit.

You must log in to answer this question.