Postgres 9.5 feature highlight: row-level security and policies

SET ROLE/RESET ROLE can be used for that, however for role A to be able to switch to role B, you need to "GRANT B TO A" which will lead to a combinatory explosion.

It'd probably be better to have a user with all roles granted as NOINHERIT (so the only thing it can do is SET ROLE) and all connections defaulting to that, then when you get a connection from the pool you "SET ROLE current_user" and you "RESET ROLE" the connection before storing it back.

I have not tested it.

They bring stuff in piece by piece, eg. in 9.4 they added logical changeset extraction/logical log streaming replication, which is another step down the road to clustering. On top of this they are building bi-directional replication. And so it continues.

This is the same reason they haven't added upsert or merge yet.

With clusters, there are far too many variables for it to be plug and play. For example, do you want replication, or distributed partitions? If replication, do you want master-slave, or master-master? If distributed partitions, what are you going to trade off: consistency or availability? (That being said, Postgres will likely never change from its CA stance, you'll have to use an alternative distribution like PostgresXL).

Furthermore, clustering isn't likely to be much more than a pareto-tail problem for the next decade or so. It is useful for millions of use cases, and something that only Google's and Twitter's can benefit from is not one of them. If you have the data to justify extensive clustering requirements, I would hope you also have the resources to contribute your patches to Postgres. So far, they all seem to be content solving their own niche problems with niche solutions that slowly bleed down to us mere mortals (Ex. Cassandra), or just buying a solution like EnterpriseDB or Oracle.

What makes it really hard is solving the problem in a way that doesn't lead people into a trap. MongoDB offers solutions for some users, but a lot of users walk away very disappointed when the "solution" didn't do what they think it would do (often after running into problems in production).

But it still needs to be done. A general solution is impossible, so we need specific solutions. Each of those will need to be designed in a way that the user knows as early as possible whether it will work for them, or they need to use one of the other approaches.

What's happening right now is work on very powerful infrastructure for logical replication. Postgres invested in binary replication before, and the results have been great. But binary replication is not a good foundation for multi-master; so now there's investment in logical. There is also early-stage investment in parallel query, which I believe can pay off with mutli-machine parallelism, which is another form of scale-out.

EDIT: Yes I'm simplifying; saying "never" overstates somewhat. If your cluster management tool can account for STONITH, you're probably safe — or at least safer. If you don't know what STONITH is without googling, you shouldn't be playing with multi-master, highly available databases and failover betwixt them.

We can be weird together!

The difference is you will forget to add the condition to a query at one point.

It probably only makes sense if your users also have user accounts on your postgres server.

You could dramatically reduce SQL Injections by giving each user their own database login with limited rights. Login to web site as foo, which connects you to database as foo. With RLS you can do less damage.

Or let management connect directly to the database via Excel. Use RLS to prevent lower managers from seeing upper managers' salaries.

Inside governments, there are often individual pieces of data within a larger dataset that are protectively marked. For example, fake identities created by the government, identities of prominent people (like members of the Royal family) or maybe even people at high risk of identity theft like bank managers.

Your normal app just thinks the records for these people are missing or knows that they are present but can't access all of the data for them. These people have to make special arrangements to eg, apply for a driving licence.

The way it works in paper processes is that normal caseworkers will get the file and see that it's protectively marked and then hand over to a caseworker who has security clearance.

Obviously it depends on how much of your problem domain you're reifying in your database but it can be a nice option, particularly as part of defence-in-depth.

Uh huh? That's what check constraints are for.