
Postgres 9.5 feature highlight: row-level security and policies - waffle_ss
http://michael.otacoo.com/postgresql-2/postgres-9-5-feature-highlight-row-level-security/
======
saurabhnanda
Wow. This is super exciting. What does this mean for multi tenant
applications? Does this mean we can push our multi-tenancy logic down to the
DB (if not logic, at least the last line of "data defence"). Can we create one
DB user for each app-level account, ensure that all tables have the account
identifier, and then enforce (at a DB level), that one account should be
unable to access another account's data?

What does this mean for DB pooling? Is it possible to maintain a set of open
DB connections where the account credentials can be applied, query executed,
and the account credentials removed, with the connection going back into the
pool?

~~~
masklinn
> Is it possible to maintain a set of open DB connections where the account
> credentials can be applied, query executed, and the account credentials
> removed, with the connection going back into the pool?

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.

~~~
ibotty
i do just that and it works like a charm.

------
jl6
[http://www.postgresql.org/docs/devel/static/ddl-
rowsecurity....](http://www.postgresql.org/docs/devel/static/ddl-
rowsecurity.html)

I found the dev documentation to be much clearer than this blog post.

~~~
colanderman
I find the Postgres dev documentation to be much clearer than most things,
honestly.

------
syntern
I open every "New Postgresql features" article in the hope that it will
contain "Simple cluster setup, you just start nodes and they will figure out
the rest." Unfortunately it didn't happen so far...

Is it such a hard problem, that no one is able to solve it, or it is not a
concern or interest of the parties that are developing Postgresql or the tools
around it?

(And by hard problem, I don't mean surviving an aphyr-level diagnostics
without any issues, but it it would be certainly nice to see such too.)

Edit: I'd settle for a multi-master replicated cluster, where the node
failures and startups (and the migration of the data) is handled transparently
in the cluster. I know that there are many other aspects, but even this basic
case is painfully hard to achieve (much harder than with MySQL).

~~~
imanaccount247
No it is not a hard problem, it is an impossible, unsolvable problem. Software
can not alter its behavior to match your desires if you do not tell it what
you desire. Clustering is not some simple monolithic thing where you just
"enable clustering" and that's that. There's billions of possible clustering
setups. Setting it up is as simple as it can get, you have to pick which of
the billions of setups you want.

~~~
syntern
Yes, there are billions of different setups, but there are a few basic ones,
and you could start by solving one or two, before solving the generic case.
For example, one could provide just a cluster setup for data replication. No
fancy distributed data models, just copy the data around in the cluster, if a
new node joins copy that data to it, if something fails, handle the failure.
Postgresql claims to have multi-master setups, so this is really about the
node handling and copying the data around.

After that, you could introduce locally distributed cluster. Later on you
could introduce geographically distributed cluster setups. But just because
the later is very complex, does not mean that you can't start with the basic
setup.

~~~
imanaccount247
Setting up the few basic ones is already as easy as it can get.

~~~
ohyesyodo
Synchronous replication with automatic failover?

~~~
imanaccount247
Yes.

~~~
syntern
Care to provide a link with the easy setup and cluster management? It could be
a well-hidden gem that needs more publicity.

~~~
imanaccount247
[https://wiki.postgresql.org/wiki/Replication,_Clustering,_an...](https://wiki.postgresql.org/wiki/Replication,_Clustering,_and_Connection_Pooling)

~~~
ohyesyodo
Trying to be ironic? That page does not present an easy way to set up what I
asked for.

~~~
imanaccount247
Yes it does. Here, I will click the correct link for you and everything:
[http://www.repmgr.org/](http://www.repmgr.org/)

------
elchief
Yay, RLS! Maybe I'm weird but I've been waiting for this.

This will make per-tenant replication easier too

~~~
brunoqc
What is RLS?

~~~
feld
Read the article...

------
radisb
Looks like Oracle's Virtual Private Database? (or something like that, I cant
remember the name)

~~~
y0ghur7_xxx
Yes, it's almost the same

------
porker
It's a nice example but when would you actually want RLS in the database? In
practice you'd want to validate phone number formats etc, so between this and
adding a "WHERE row_owner = X" to every query doesn't seem that different.

~~~
masklinn
> so between this and adding a "WHERE row_owner = X" to every query doesn't
> seem that different.

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

------
cvburgess
I would really like to see an implementation of MySQL's SQL_CALC_FOUND_ROWS.
It's super handy for pagination.

~~~
masklinn
According to [http://www.percona.com/blog/2007/08/28/to-
sql_calc_found_row...](http://www.percona.com/blog/2007/08/28/to-
sql_calc_found_rows-or-not-to-sql_calc_found_rows/) (which is a very old
thread and may be completely outdated), it's also often much slower than just
doing two queries.

~~~
cvburgess
Good to know!

Although, like you said, that thread dates back to MySQL 5.0 - I wouldn't
hedge my bets on it today...

Is there a "best practices" way to handle pagination of complex queries
without running the query twice?

------
sarciszewski
Server not found.

~~~
stonemetal
[http://webcache.googleusercontent.com/search?q=cache:CasMIHS...](http://webcache.googleusercontent.com/search?q=cache:CasMIHSDFfEJ:michael.otacoo.com/postgresql-2/postgres-9-5-feature-
highlight-row-level-security/+&cd=1&hl=en&ct=clnk&gl=us)

~~~
sarciszewski
Ah, thank you. :D

------
zobzu
Nice. :)

------
andyl
Is there an ETA for the production release of PG9.4 ?

~~~
saosebastiao
I've been wondering this as well. They've had a release candidate out for a
few months now, but no official release and nothing about an expected date on
their web page.

~~~
afonit
Here is a post on their mailing list about it:

""" Since we bit the bullet and changed the on-disk format for JSONB data, the
core committee feels that we should put out a new 9.4 beta release as soon as
possible. Accordingly, we plan to wrap 9.4beta3 on Monday (Oct 6) for release
Thursday Oct 9.

    
    
    			regards, tom lane

"""

[http://www.postgresql.org/message-
id/26018.1412041228@sss.pg...](http://www.postgresql.org/message-
id/26018.1412041228@sss.pgh.pa.us?utm_source=postgresweekly&utm_medium=email)

