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
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.
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.
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.
> 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.
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].
It looks like lack of quote_ident is a feature here because it makes you think "what the heck I'm doing?".