News for hackers. Stuff that matters.
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. ;)