5 ms·

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

by merb 11y ago

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.html 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

orf 11y ago
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.
- merb 11y ago
  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.
Jweb_Guru 11y ago
SET LOCAL ROLE will automatically roll back once the transaction is done.
- merb 11y ago
  Thanks I updated my upper example. ;)

by merb 11y ago

orf 11y ago

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.

merb 11y ago
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.

Jweb_Guru 11y ago

SET LOCAL ROLE will automatically roll back once the transaction is done.

merb 11y ago
Thanks I updated my upper example. ;)