News for hackers. Stuff that matters.
Re:Row Level Security with PostgreSQL 9.5 by orf on Wednesday April 20, 2016 @03:09PM (#11536902)
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.
Re:Row Level Security with PostgreSQL 9.5 by merb on Wednesday April 20, 2016 @03:36PM (#11537116)
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.

    START TRANSACTION;
    SET LOCAL ROLE rating_role1;
    SELECT * FROM ratings2;
    ROLLBACK;
Works just fine.

Edit: Updated

Re:Row Level Security with PostgreSQL 9.5 by orf on Wednesday April 20, 2016 @03:44PM (#11537179)
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.

Re:Row Level Security with PostgreSQL 9.5 by merb on Wednesday April 20, 2016 @04:02PM (#11537305)
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.

Re:Row Level Security with PostgreSQL 9.5 by Jweb_Guru on Wednesday April 20, 2016 @04:13PM (#11537367)
SET LOCAL ROLE will automatically roll back once the transaction is done.
Re:Row Level Security with PostgreSQL 9.5 by merb on Wednesday April 20, 2016 @04:15PM (#11537385)
Thanks I updated my upper example. ;)
Re:Row Level Security with PostgreSQL 9.5 by takeda on Wednesday April 20, 2016 @03:58PM (#11537290)
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.

Re:Row Level Security with PostgreSQL 9.5 by orf on Wednesday April 20, 2016 @04:16PM (#11537391)
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.

Re:Row Level Security with PostgreSQL 9.5 by takeda on Wednesday April 20, 2016 @08:00PM (#11538687)
> 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

Re:Row Level Security with PostgreSQL 9.5 by snuxoll on Thursday April 21, 2016 @02:16AM (#11540014)
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).
Re:Row Level Security with PostgreSQL 9.5 by takeda on Thursday April 21, 2016 @06:37PM (#11545828)
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?".

Re:Row Level Security with PostgreSQL 9.5 by caf on Wednesday April 20, 2016 @09:24PM (#11539024)
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.
Re:Row Level Security with PostgreSQL 9.5 by kyllo on Wednesday April 20, 2016 @07:52PM (#11538635)
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
Re:Row Level Security with PostgreSQL 9.5 by rietta on Wednesday April 20, 2016 @04:21PM (#11537436)
Please tell us more about this Merb workaround! Was it merged into Rails or did it fall away?