Ruby on Rails Wednesday, July 12, 2017



On Tuesday, 11 July 2017 23:39:13 UTC-4, Ralph Shnelvar wrote:
I have a database with a large table.

I have several users each of whom should have read-only rights to the large table but has read/wrtie rights to their own table(s) but not to each others table(s).

Adding to the complication, I want to give each RoR user the ability to write their own SQL statements against the large table as well as their own table(s).  I have successfully implemented being able to have them enter sql statements and create results they can view and/or download.  Doing that is not my question.

I want to make sure each of my "readonly" users can't modify any tables they are not authorized to see and/or change.

So,I guess, I want to change Postgres roles within Rails.  Any guidance would, of course, be appreciated.

I've never used it, but something like "SET SESSION AUTHORIZATION"  looks like it would do what you want, mostly:

https://www.postgresql.org/docs/current/static/sql-set-session-authorization.html

You'd set up a "superuser" in config/database.yml and then change to the lower-privileged specific user per-request.

Some potential problems:

* the lower-privileged users *must* not have sufficient permissions to call "SET SESSION AUTHORIZATION" themselves or the security is an illusion

* you'll want to make 100% certain that connections get a "RESET SESSION AUTHORIZATION", or a selected user's authorization will leak into the next request (and fail, see the previous point)

* you'll need to somehow sanitize the incoming SQL to remove queries like "RESET SESSION AUTHORIZATION; DROP TABLE all_the_things" or the security is an illusion

I noticed you mentioned "their own tables" above; if you're already committed to solutions where adding users is complex, you might want to think about separating things further. You could use a tool like pglogical:

https://www.2ndquadrant.com/en/resources/pglogical/

To replicate only the large table to per-user Postgres DBs. Definitely NOT an appropriate solution for multi-tenancy with lots of users, but neither is table-per-user.

--Matt Jones

--
You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email to rubyonrails-talk+unsubscribe@googlegroups.com.
To post to this group, send email to rubyonrails-talk@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/rubyonrails-talk/af2f5066-1ac6-4971-98e8-2acbfeb0be31%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

No comments:

Post a Comment