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 [email protected]. To post to this group, send email to [email protected]. 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.
