
Row Level Security with PostgreSQL 9.5 - thomcrowe
https://www.compose.io/articles/row-level-security-with-postgresql-95/
======
karavelov
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.

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.

EDIT: Here is a gist how to not use it without roles for the permissions:
[https://gist.github.com/luben/4ab60b0dbda66ecf4b6601b88c8522...](https://gist.github.com/luben/4ab60b0dbda66ecf4b6601b88c852272)

~~~
dsp1234
This is great for systems like ours that have thousands of 'users' and setting
up Roles is a pain.

I do see an issue in that if an sql injection is found, then it's trivial for
the attacker to use set_foo or set session themselves.

Do you know if it is possible to run to get the system to a point where the
initial connection role doesn't have permission to 'set session' itself, but
does have permission to run set_foo. Where set_foo can set the session, then
set a role that does not have access to execute set_foo again.

Said differently, could this be adapted so that:

1.) at the beginning of a connection, the session is unset

2.) the only way to set the session is via function

3.) once the function has been called once, it cannot be called again on the
same connection

~~~
Jweb_Guru
I'm pretty sure it's not possible to prevent a role from setting session
parameters.

I believe it is not possible to SET ROLE or SET SESSION AUTHORIZATION with
code executed within a SECURITY DEFINER function (which is what you're asking
to do), though as Tom Lane points out one shouldn't rely on that:

[http://www.postgresql.org/message-
id/10703.1417480773@sss.pg...](http://www.postgresql.org/message-
id/10703.1417480773@sss.pgh.pa.us)

Given arbitrary SQLi, it's hard to see how one can do better than setting up
an untrusted sandbox like that and executing your untrusted SQL there, but
being able to prevent setting a session parameter would still be useful within
that context.

~~~
dsp1234
I was hoping for something like mssql's "EXECUTE AS... NO REVERT" where
reverting back to the previous role can be prevented.

~~~
haberman
Or in UNIX terms, this is simply "sudo -u". Seems like something that would be
pretty useful.

------
elchief
You can use RLS with a web app.

You can still use connection pooling.

PostgreSQL is performant with hundreds of thousands of user accounts (though
not pgAdmin).

    
    
      create role www noinherit login password 's3cr3t';
    
      create role alice;
    
      grant alice to www;
    

Connect to database as www (same connection string = you can use pooling)

After you get your connection from the pool

    
    
      set role alice;
    

When you release you connection back to the pool

    
    
      reset role;
    

Want per-database users instead of per-cluster users? See the
db_user_namespace setting

Use LDAP / AD? Sync users/groups/roles to pg:
[https://github.com/larskanis/pg-ldap-sync](https://github.com/larskanis/pg-
ldap-sync)

~~~
sandGorgon
The problem is that "set role Alice" is tricky to do with many orm. Which is
why group roles are not very effective in postgres. There should be some way
to set this on the database itself rather than client side.

~~~
lgas
Thats a problem with the ORMs.

------
orf
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.

~~~
takeda
While merb provided a workaround, RLS is not intended for web applications
that uses a single account for all database communication. Your application
should be enforcing this and be written in a way that SQL injections are
impossible.

This is more for scenarios such as:

\- a DBA needs access to work on a database, but perhaps he/she should not
have access to certain financial information which would enable them to do
insider trading.

\- or perhaps SaaS allows for their users to have direct read access to their
database (probably not smart anyway), but want to make sure an user can access
information about different users.

~~~
orf
That's fair enough, it's very impressive and it seems like a great addition to
PG.

> Your application should be enforcing this and be written in a way that SQL
> injections are impossible.

I wholeheartedly agree, but it's wishful thinking. It's still the OWASP #1
critical vulnerability and it's still _everywhere_.

~~~
takeda
> I wholeheartedly agree, but it's wishful thinking. It's still the OWASP #1
> critical vulnerability and it's still everywhere.

It's everywhere, because you need to be aware about the problem to mitigate
it. The way to mitigate it is that you should _never_ build SQL command in
your language (concatenating, formatting etc). Instead you should use variable
binding / parametrized queries.

When you do you that you ensure that there's separation between an SQL
statement and the data and no untrusted data can affect the SQL statement.

If you are programming in Python and use PsycoPG2 then first section talks
about it[1][2].

[1]
[http://initd.org/psycopg/docs/usage.html](http://initd.org/psycopg/docs/usage.html)

[2] [http://initd.org/psycopg/docs/usage.html#sql-
injection](http://initd.org/psycopg/docs/usage.html#sql-injection)

~~~
snuxoll
A problem with SET ROLE in particular, however, is that it takes an
identifier, not a string - it cannot be parameter. However, PostgreSQL does
have a quote_ident function you can use to prevent arbitrary SQL injection
(this won't prevent you from injecting a specific DB role, but honestly this
is fairly trivial to secure at the application level).

~~~
takeda
Well, the thing is that SET ROLE changes your security permissions you should
never use arguments that come from an untrusted input, if you do, you already
caused a security vulnerability, SQL injection or not.

It looks like lack of quote_ident is a feature here because it makes you think
"what the heck I'm doing?".

------
joshma
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).

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?

~~~
tiffanyh
RLS is great for SaaS providers.

Stick all of your customer data in a single database. Implement RLS per
customer. Now "Pepsi" can't see "Coca-Cola's" data.

Essentially creating a virtual private database per customer by using RLS.

edit:typo

~~~
agopaul
This is interesting, but wouldn't that require to create a postgres user for
each customer (and then use that user to make the connection to the DB)?

~~~
kyllo
Something like this has been done, and web app frameworks have had libraries
that do it--only with schemas instead of users--for quite some time.

------
Jweb_Guru
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.

~~~
johnbellone
This is awesome.

------
davidw
> 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.

That's kind of a sticking point for web apps. Wonder if there's a way around
that.

~~~
rch
It can be really nice for enterprise web apps. One might have thousands of
users organized into a handful of departments with different authorizations,
some of which have their own DBAs and developers. Solid testing on the
application side is important, of course.

~~~
rst
If the database roles correspond to departments, and authorizations at a
department level, this sort of thing can be quite useful. (e.g., in a
hospital, designating certain rows as visible only to the psychiatry
department, in a way that developers within other departments can't screw it
up.)

But establishing a DB role for every distinct person within a department gets
awkward very fast, particularly if permissioning requirements have a bit of
business logic associated with them (e.g., "this row can be updated by the
user designated in the row itself as "owner", and is visible to both them and
anyone designated in this other table over here as their assistant...") So,
you'll often still need additional permissioning logic within the webapp
regardless.

~~~
rch
Yes, exactly. Roles would generally be by department, or in an
application/department matrix.

Your hospital example is spot on, and in finance a department might be a
trading group.

------
herge
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
[http://www.postgresql.org/docs/9.4/static/release-9-4.html](http://www.postgresql.org/docs/9.4/static/release-9-4.html),
right?

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.

~~~
simcop2387
JSONB is definitely in 9.4. We used that at the company I worked at
previously. There might be improvements to it in 9.5 that I can't recall but
it's definitely usable and production ready in 9.4

EDIT: Looks like [1] talks about what he was mentioning, there's a number of
functions they added to improve the use and experience in 9.5.

[1] [https://www.compose.io/articles/could-postgresql-9-5-be-
your...](https://www.compose.io/articles/could-postgresql-9-5-be-your-next-
json-database/)

------
awinter-py
Relying on the PG user/role system doesn't feel general enough to me.

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.

