4 ms·
Let me just take a moment to point out how important row-level security is as a concept. That features allows one to create essentially a secure analytics data
by bmh100 11y ago
Let me just take a moment to point out how important row-level security is as a concept. That features allows one to create essentially a secure analytics data model by tying security business logic into the values of a table. For every query, just join to that security table, and now the database is flexible and secured.
Example:
Tie a salesperson to an order placed, and use the order table as the primary fact table. For all reporting and visualizations that can be tied to orders, all one has to do is incorporate an association to that table. Worrying about which customers, products, time periods, etc. a salesperson can see are automatically handled by that association.
I am not saying that PostresSQL implements what I am describing, but this example can be expanded by creating an intermediate table with many-to-many associations. E.g., this table might have one row for each salesperson's access and one row for each salesperson under a supervisor. Once again, a centralized location for controlling access throughout the entire analytical data model. It is a very useful tool that I have relied upon extensively.
- emidln 11y agoMeh, you can already do this with a query AST. For every request, grab the user's data permissions represented in the same query AST and then AND them together. Compile your query AST into whatever search technology (I've seen it done with ES, Postgres, MySQL, Mongo, Solr, and Rethinkdb in the last couple years). If you're being super fancy you can even use your query ast to match on a document stream in real-time by compiling to some actual programming language and checking things on whatever document stream.
- pgeorgi 11y agoThe difference is that postgres can enforce this for arbitrary queries. This doesn't matter in the typical webapp where all accesses to the DB happen through the same database user id, but when actually using the user system of the DB, it allows for fine grained access control to a common data set. The closest you have without explicit RLS support is to create a view for each user. RLS generates per-user views on demand under a common name.
- darrhiggs 11y agoI use schemas[0] for this. Could someone explain what advantage is gained from RLS in comparison, either to views or schemas? [0] http://www.postgresql.org/docs/current/static/ddl-schemas.html http://www.postgresql.org/docs/current/static/ddl-schemas.ht...
- bmh100 11y agoI am very familiar with RLS, but not schemas. Could you provide an example of how you would use schemas? If not too much trouble, could you also use my salesperson example from my other comments?