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.
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'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:
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:
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';
$$
It would be fairly trivial to extend that to support calling set_foo() once per transaction by checking against txid_current(). But if users can create plperl functions and the worry is sql injection-- It should be easy to write the same thing in C.
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.
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.
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.
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.
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:
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)
So inside a Connection / Transaction you could easily switch users and create 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.
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.
> 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].
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'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.
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?
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 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.
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.
> 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.
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.
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.
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.
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.
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...