Hacker News Reader

Comment by wingsonfire | Hacker News Reader

I am looking forward for Row Level Security, Though cell level security will be even more awesome to have.. Difficult to achieve in SQL. Only Apache Accumulo in NoSQL space has it.. But once we have it make sure no one has access to SSN column and we will be protected to one degree in data breaches.

View on HN

Wouldn't you be able to do this in Postgres now ?

"RLS implements true per-row and per-column data access control"

Any practical use cases of Row level security ?

https://github.com/begriffs/postgrest

To be more specific, with Postgrest you're supposed to be able to use database-level permissions as your client-side permissions. (I personally haven't tried it so can't say how well it works)

Imagine you're working in a place with a rather large sales/marketing department. Now, sales is a pretty cutthroat job, and some people will do any cheat possible to get ahead of their co-workers. In a typical sales database, someone who manages to borrow a bit of sql from a techie friend could potentially go in and get sales leads from co-workers, poaching their deals. With row level security, the sleezier co-worker isn't allowed to look at those rows to be able to poach.

Do you mean as another security layer? I guess the sales people don't normally have direct access to the databases and the softwares already restricts which data they can see.

Maybe you mean just in case they manage to bypass the software restrictions.

In some cases, people will defer to database level security restrictions. It really depends on how much application logic is in the database. Some applications are designed with as much logic as possible in the DB, including each user being a db user with credentialed access. Others will treat the DB as dumb storage with all access through a programatic API... with thin API shims over DBMS, the db security is paramount.

Exactly. Defense in depth. Most people are going to play by the rules, but making it harder to break the rules is useful.

Imagine how much simpler every single query becomes, when you don't have to do complex access checking as part of the criteria. You can just write a normal select/join! The database handles access checking for you.

Suddently, it's not that scary to ask the intern to generate a rather complex report - he can't show anyone data they're not allowed to see by accident.

What can you do with row-level security that you can't do by setting permissions on an updatable view?

Normal views aren't designed for security. There are a number of ways that they can "leak" information that is supposed to be hidden.

The reason is that the optimizer reorders operations. So, a tricky person can write the query in a way that, for example, throws a divide-by-zero error if someone's account balance is within a certain range, even if they don't have permission to see the balance. Then they can run a few queries to determine the exact balance.

RLS builds on top of something called a "security barrier view" which prevents certain kinds of optimizations that could cause this problem.

It also offers a nicer interface that's easier to manage.

I may be wrong in the level of separation that pgsql provides in such situations, but it appears that a materialized view offers another level of isolation that would make such leaks more difficult to handle.

For one, you don't need a view. And you'd probably need (in 9.4) insert, update, and delete triggers for anything beyond trivial row security.

HBase has also had cell level security for a couple of years now.

https://blogs.apache.org/hbase/entry/hbase_cell_security

Why would you have a SSN column if no one has access to it?

I meant no human user has access to it. But application user that connects to credit score engine, map reduce job, or something similar has access.

I know applications can be compromised but now you can freely share your DB freely with other teams to analyze or play around

Reply on news.ycombinator.com