I think the article is wrong on assiming and calling out that RLS is only useful if you use only the DB roles for security mechanism.<p>When you create a RLS policy you specify a predicate (the USING and WITH CHECK parts) that is checked for each accessed row (read or write). The predicate is not in any way restricted to refer to a DB role it can compare for example a field with a parameter variable.<p>EDIT: Here is a gist how to not use it without roles for the permissions: <a href="https://gist.github.com/luben/4ab60b0dbda66ecf4b6601b88c852272" rel="nofollow">https://gist.github.com/luben/4ab60b0dbda66ecf4b6601b88c8522...</a>
You can use RLS with a web app.<p>You can still use connection pooling.<p>PostgreSQL is performant with hundreds of thousands of user accounts (though not pgAdmin).<p><pre><code> create role www noinherit login password 's3cr3t';
create role alice;
grant alice to www;
</code></pre>
Connect to database as www (same connection string = you can use pooling)<p>After you get your connection from the pool<p><pre><code> set role alice;
</code></pre>
When you release you connection back to the pool<p><pre><code> reset role;
</code></pre>
Want per-database users instead of per-cluster users? See the db_user_namespace setting<p>Use LDAP / AD? Sync users/groups/roles to pg: <a href="https://github.com/larskanis/pg-ldap-sync" rel="nofollow">https://github.com/larskanis/pg-ldap-sync</a>
So why does this feature require real database users? Why couldn't a statement be added like "SET current_user = some_id;" that is called once per connection and have the row level security authenticate against that? Sure, it's not real security, but it's a step up from a single user being able to select everything in your database through a single SQLi as long as you ensure that the "current_user" is the first statement executed.
Has anyone used RLS in a standard web service? Are there any common use cases where this is handy? I'm thinking it won't work for your standard permissioning system since it seems clunky if you have any slightly sophisticated logic (X is in org Y and can see anything Y has read access to).<p>Maybe it'd be more useful if you're using a single DB for a multi-tenant setup, and you know each tenant's data is strictly isolated?
Row-level security can be respected by pg_dump as well, which means it can be used (in a multitenant context) to do things like migrate just a single tenant between databases.
> But, you do have to enable it for each table plus you need to commit to using database roles as a main security mechanism. That last part is the barrier but also the reason to use such a feature.<p>That's kind of a sticking point for web apps. Wonder if there's a way around that.
This is completely not pertinent to the actual content of the article, but I think there a slight error in the first paragraph of the article. Jsonb support was added as part of Postgres 9.4, at least according to <a href="http://www.postgresql.org/docs/9.4/static/release-9-4.html" rel="nofollow">http://www.postgresql.org/docs/9.4/static/release-9-4.html</a>, right?<p>I ask because we're planning on using jsonb here soon, and we were hoping it would be available in the version of Postgres available in ubuntu 16.04.
Relying on the PG user/role system doesn't feel general enough to me.<p>A better solution would set a query on the connection that constrains which rows it can touch. I know this ends up being more complicated and less performant but it splits the difference between app-level and database-enforced security.