Row Level Security with PostgreSQL 9.5(compose.io)
compose.io
Row Level Security with PostgreSQL 9.5
https://www.compose.io/articles/row-level-security-with-postgresql-95/
47 comments
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
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
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/[email protected]...
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.
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/[email protected]...
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.
Sure, use a language that has access to some kind of shared global variable. For example %_SHARED in plperl:
Something like:
Something like:
CREATE OR REPLACE FUNCTION set_foo(name text) returns void as $$
my $name = shift;
die "set_foo() has already been called" if ($_SHARED{'set_foo'});
$_SHARED{'set_foo'} = $name;
$$
LANGUAGE plperl;
CREATE OR REPLACE FUNCTION get_foo() returns text as $$
my $name = shift;
return $_SHARED{'set_foo'} || 'nobody';
$$
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).
After you get your connection from the pool
Use LDAP / AD? Sync users/groups/roles to pg: https://github.com/larskanis/pg-ldap-sync
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;
After you get your connection from the pool
set role alice;
reset role;
Use LDAP / AD? Sync users/groups/roles to pg: https://github.com/larskanis/pg-ldap-sync
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.
The LDAP/AD setup is what I'm most looking forward to trying out. Do you know of any examples of where it's being used currently?
You can actually use GSS authentication if you setup the correct Kerberos tickets and the correct service names so that Windows will try to communicate with the server via the user's tickets if a password isn't specified.
It's kind of a pain that the configuration options for that have to go in to the host-based authentication file and that it just assumes you know this.
It's kind of a pain that the configuration options for that have to go in to the host-based authentication file and that it just assumes you know this.
User www doesn't need to be a superuser for this to work btw
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.
Actually you could do that.
First your user needs the right to create roles and then you can switch the current user via: http://www.postgresql.org/docs/current/static/sql-set-role.h...
It's not really the use case for RLS, but its possible.
Edit: Updated
It's not really the use case for RLS, but its possible.
START TRANSACTION;
SET LOCAL ROLE rating_role1;
SELECT * FROM ratings2;
ROLLBACK;
Edit: Updated
Does it? If someone had SQLi and could execute statements then couldn't they do "RESET ROLE;" or "ROLLBACK;", then do "SET ROLE admin", or am I missing something?
I'm talking about something in-between having 1 database role per user and 1 per app, a configurable string/ID/etc that can be set only once per connection, right at the start. That way a SQLi could only pull records from their user and not all of them.
I'm talking about something in-between having 1 database role per user and 1 per app, a configurable string/ID/etc that can be set only once per connection, right at the start. That way a SQLi could only pull records from their user and not all of them.
as already said your application connects with user 'xyz' this guy needs the 'CREATEROLE' permission.
Than this guy could Create Roles and Switch to them:
Also you can't switch to a Role you aren't admin, the only odd thing is, is that they could DROP roles and that they could create roles with more permissions than they have.
loki=# CREATE ROLE demo CREATEROLE LOGIN PASSWORD '123';
CREATE ROLE
loki=# CREATE DATABASE demo123 OWNER demo;
CREATE DATABASE
loki=# \q
schmitch@SHANGHAI:~$ psql -h localhost -U demo -W demo123
Password for user demo:
psql (9.5.2)
Type "help" for help.
demo123=> CREATE ROLE hase123 WITH ADMIN demo;
CREATE ROLE
demo123=> SET ROLE hase123;
SET
demo123=> SELECT CURRENT_USER, SESSION_USER;
current_user | session_user
--------------+--------------
hase123 | demo
(1 row)
demo123=> RESET ROLE;
RESET
demo123=> SELECT CURRENT_USER, SESSION_USER;
current_user | session_user
--------------+--------------
demo | demo
(1 row)
Also you can't switch to a Role you aren't admin, the only odd thing is, is that they could DROP roles and that they could create roles with more permissions than they have.
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.
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.
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.
> 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.
> 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
[2] http://initd.org/psycopg/docs/usage.html#sql-injection
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
[2] http://initd.org/psycopg/docs/usage.html#sql-injection
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).
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?".
It looks like lack of quote_ident is a feature here because it makes you think "what the heck I'm doing?".
It's also for scenarios where you have a fat client implements the front-end logic itself and communicates directly with the database server, using database users to authenticate.
Or for reporting/BI contexts where anyone needs to be able to run a report on the database but sensitive data is hidden to unprivileged users
Please tell us more about this Merb workaround! Was it merged into Rails or did it fall away?
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?
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?
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
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
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)?
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.
IIRC you can `SET ROLE = ...`, query, `RESET ROLE` and be done with it.
Might need transactions, I'm not sure. I fiddled around with it a bit but it was too much overhead to work with.
Might need transactions, I'm not sure. I fiddled around with it a bit but it was too much overhead to work with.
Is this an observation or have you done/seen this in action?
I ask because for my next project I'd like to tackle the issue of having to keep lots of Databases up to date with their stored procedures. Kind of wanted a common library of procs that any DB can access. I've seen third party Software do DB versioning etc but too expensive for me. A few do it via package management, but keen to see how others are doing it!
I ask because for my next project I'd like to tackle the issue of having to keep lots of Databases up to date with their stored procedures. Kind of wanted a common library of procs that any DB can access. I've seen third party Software do DB versioning etc but too expensive for me. A few do it via package management, but keen to see how others are doing it!
I don't know if Postgres can do what you're describing but I know Oracle can and it's called Pluggable Databases (PDB) and it's designed exactly for this use case.
(Now I just hope I don't get flammed)
(Now I just hope I don't get flammed)
Interesting thanks, I'll look into it. Your earlier comment has sent me down a Postgresql logicdecode/Kafka/Hadoop rabbit hole. Funny where you end up.
In PostgreSQL extensions are the common way to solve it, you still have to manually install/uninstall in each database but writing them is pretty straightforward and a quick call to CREATE EXTENSION/DROP EXTENSION in your migrations isn't too much to ask in most cases.
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.
This is awesome.
> 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.
That's kind of a sticking point for web apps. Wonder if there's a way around that.
It's pretty easy to set the user's role after connecting:
https://stackoverflow.com/questions/2998597/switch-role-afte...
https://stackoverflow.com/questions/2998597/switch-role-afte...
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.
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.
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.
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.
Your hospital example is spot on, and in finance a department might be a trading group.
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, 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.
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.
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...
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...
I think the article means to say is JSONB modification.
Prior to 9.5 there weren't commands to edit values within a JSONB column.
Prior to 9.5 there weren't commands to edit values within a JSONB column.
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.
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.
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...