3

I am building a postgresql database and I want users to be able to run their own R and python scripts at the database rather than pulling it all down locally. Unfortunately, pl/R and pl/python are untrusted, so I can't just let 20 users have pl/R access to my database for fear some person will do something bad to my core database.

Is there a server configuration that will let me provide this flexibility to my users but still stop them from having inappropriate access to my database? For example, can I set up a special linked database just for them, that is close enough to the data to make it useful while still having security separation? (or anything else, just trying to give an illustrative example).

2
  • What's your threat model? Is the data PII, trade secret or some other high security data?
    – Larry
    Feb 8, 2017 at 22:56
  • It is probably pay-for-access, but more importantly I just dont want people to delete it.
    – dnb
    Feb 10, 2017 at 4:37

3 Answers 3

2

I don't think that there is any general method to do this. (Otherwise someone probably would have implemented a trusted version of the language using that method).

You could create a hot-standby database on a separate machine (or at least running as a separate and little-privileged OS-user) and let your users have at that with superuser permissions. Note that they would be able to do nasty things to each other if they are clever and malicious enough, but should have limited capability to mess with the primary server.

Since a hot-standby is read only, you would have to set up the (super)user roles you want in the primary server, but disable them in the pg_hba.conf file. Then use a different pg_hba.conf in the standby which allows those roles to connect.

I think it is kind of playing with fire to do even this much, though.

1

This actually sounds like a situation where docker might be useful. The issue with untrusted languages in postgres is that they execute as the operating system user that the postgresql process is running as and have full access to all of the filesystems available to the database cluster. So a malicious user in a shared tenancy database could do quite a bit of damage and you could not allow any sensitive data to be stored in the entire database cluster.

If your data is relatively static and updated infrequently then it's pretty straight forward to just launch a new database server with a copy of the data directory for each client. If the data is frequently updated then you will need to launch each new database and restore it to the present before making it available.

You haven't said if your use case requires your customers to be able to write to the database; but if so you probably want to just spawn new databases and use an eternal queue to keep the dataset up to date.

1
  • this is super helpful. It is mostly static data once updated, and users would not be adding data, just consuming data that I put in there. Sigh... now I need to go learn about Docker... :)
    – dnb
    Feb 13, 2017 at 1:19
0

Is there a server configuration that will let me provide this flexibility to my users but still stop them from having inappropriate access to my database? For example, can I set up a special linked database just for them, that is close enough to the data to make it useful while still having security separation? (or anything else, just trying to give an illustrative example).

You may find RAppArmor useful. You can find the white paper on it here. As an extension of App Armor, it's probably the best you can get. Your code is still untrusted, but it's securely sandboxed.

You call eval.secure with a custom profile and code. And, it runs that code according to profile.

R> eval.secure(readSyslog(), profile='r-user')
Switching profiles...
Error in file(con, "r") : cannot open the connection

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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