5 ms·

INSERT ON CONFLICT and Row Level Security are awesome new additions. If you don't know about RLS I recommend you read up on them[1], as they would allow you to

INSERT ON CONFLICT and Row Level Security are awesome new additions. If you don't know about RLS I recommend you read up on them[1], as they would allow you to handle select/update/delete rights on rows directly on the data itself. [1]http://michael.otacoo.com/postgresql-2/postgres-9-5-feature-highlight-row-level-security/ http://michael.otacoo.com/postgresql-2/postgres-9-5-feature-...

rectangletangle 11y ago
I wish they referred to it as UPSERT in the syntax.
porker 11y ago
> Row Level Security Awesome! Though having read the explanation I can't think of a secure way to use this in a multi-tenanted app, as once the DB user has permission to execute "SET ROLE", it's got the same security issues with accessing other user data as the current "WHERE company_id=3" style of separation? Would it help this use-case, and does it add security?
- jerven 11y ago
  It does help this use case, as you can restrict which role's a user/connection can have. i.e. you can't set role to anything you want if you are not a superuser.
- blackbeard 11y ago
  If someone builds this into a database engine it'd be a big win.
- masklinn 11y ago
  > as once the DB user has permission to execute "SET ROLE", it's got the same security issues with accessing other user data as the current "WHERE company_id=3" style of separation? DB users can only SET ROLE to roles they belong to. If each tenant has its own role it isn't possible to switch between them. # CREATE ROLE bob; CREATE ROLE # CREATE ROLE alice; CREATE ROLE # SET SESSION AUTHORIZATION bob; SET > SET ROLE alice; ERROR: permission denied to set role "alice" "SET SESSION AUTHORIZATION <role>" changes the possible SET ROLE targets to those allowed for <role> (so only the roles it's a member of), whereas SET ROLE keeps the original possible roleset (which is every role if you SET ROLE from an admin account), so SET SESSION AUTHORIZATION is a better proxy for "logged as <role>". The biggest annoyance is with connection pooling and the inability (to my knowledge) to prevent RESET SESSION AUTHORIZATION at the DB-level: when pooling connections you'll want to have a "base" role which is a NOINHERIT member of all "effective" (tenant) roles, so it can only SET ROLE/SET SESSION AUTHORIZATION but can't do anything (so it can't be exploited), the first thing you do when you get a connection from the pool is SSA to the effective user you want, and the last thing you do when you put the connection back in the pool is a RSA. # CREATE TABLE foo ( id serial, c1 varchar, c2 varchar ); # GRANT SELECT (id, c1, c2) ON foo TO public; # GRANT UPDATE (c1) ON foo TO bob; # GRANT UPDATE (c2) ON foo TO alice; # INSERT INTO foo (c1, c2) VALUES ('a', 'b'); # SELECT * FROM foo; id | c1 | c2 ----+----+---- 1 | a | b (3 rows) # SET SESSION AUTHORIZATION base; > UPDATE foo SET c1 = 'd' WHERE id = 1; ERROR: permission denied for relation foo > SET SESSION AUTHORIZATION bob; > UPDATE foo SET c1 = 'd' WHERE id = 1; UPDATE 1 > SELECT * FROM foo; id | c1 | c2 ----+----+---- 1 | d | b (3 rows) That'll prevent honest mistakes, though an attacker could still RESET SESSION AUTHORIZATION then switch the connection to a different role and go to town. It might be possible to mitigate that at the application level, but that's less convenient.
  - elchief 11y ago
    MS does this with a magic cookie value. https://msdn.microsoft.com/en-us/library/ms181362.aspx https://msdn.microsoft.com/en-us/library/ms181362.aspx It's been discussed on the pg mailing lists but nothing has happened so far
    - masklinn 11y ago
      An output parameter seems somewhat inconvenient to use from an application context, but yes that's a feature which — as the MSDN correctly notes — would be extremely useful for multiuser with connection pooling environment.
- kawera 11y ago
  For small multi-tenant apps, we use one database per customer. It's simple and works pretty well.
- elchief 11y ago
  by multi-tenant you mean tenant info in table column or by schema, or db?
  - porker 11y ago
    I'm confused by your question, but I think "table column". Think generic SaaS application: one app, one (sharded) database, one set of tables, used by many different companies.

rectangletangle 11y ago

I wish they referred to it as UPSERT in the syntax.

porker 11y ago

> Row Level Security Awesome! Though having read the explanation I can't think of a secure way to use this in a multi-tenanted app, as once the DB user has permission to execute "SET ROLE", it's got the same security issues with accessing other user data as the current "WHERE company_id=3" style of separation? Would it help this use-case, and does it add security?

jerven 11y ago
It does help this use case, as you can restrict which role's a user/connection can have. i.e. you can't set role to anything you want if you are not a superuser.
blackbeard 11y ago
If someone builds this into a database engine it'd be a big win.
masklinn 11y ago
> as once the DB user has permission to execute "SET ROLE", it's got the same security issues with accessing other user data as the current "WHERE company_id=3" style of separation? DB users can only SET ROLE to roles they belong to. If each tenant has its own role it isn't possible to switch between them. # CREATE ROLE bob; CREATE ROLE # CREATE ROLE alice; CREATE ROLE # SET SESSION AUTHORIZATION bob; SET > SET ROLE alice; ERROR: permission denied to set role "alice" "SET SESSION AUTHORIZATION <role>" changes the possible SET ROLE targets to those allowed for <role> (so only the roles it's a member of), whereas SET ROLE keeps the original possible roleset (which is every role if you SET ROLE from an admin account), so SET SESSION AUTHORIZATION is a better proxy for "logged as <role>". The biggest annoyance is with connection pooling and the inability (to my knowledge) to prevent RESET SESSION AUTHORIZATION at the DB-level: when pooling connections you'll want to have a "base" role which is a NOINHERIT member of all "effective" (tenant) roles, so it can only SET ROLE/SET SESSION AUTHORIZATION but can't do anything (so it can't be exploited), the first thing you do when you get a connection from the pool is SSA to the effective user you want, and the last thing you do when you put the connection back in the pool is a RSA. # CREATE TABLE foo ( id serial, c1 varchar, c2 varchar ); # GRANT SELECT (id, c1, c2) ON foo TO public; # GRANT UPDATE (c1) ON foo TO bob; # GRANT UPDATE (c2) ON foo TO alice; # INSERT INTO foo (c1, c2) VALUES ('a', 'b'); # SELECT * FROM foo; id | c1 | c2 ----+----+---- 1 | a | b (3 rows) # SET SESSION AUTHORIZATION base; > UPDATE foo SET c1 = 'd' WHERE id = 1; ERROR: permission denied for relation foo > SET SESSION AUTHORIZATION bob; > UPDATE foo SET c1 = 'd' WHERE id = 1; UPDATE 1 > SELECT * FROM foo; id | c1 | c2 ----+----+---- 1 | d | b (3 rows) That'll prevent honest mistakes, though an attacker could still RESET SESSION AUTHORIZATION then switch the connection to a different role and go to town. It might be possible to mitigate that at the application level, but that's less convenient.
- elchief 11y ago
  MS does this with a magic cookie value. https://msdn.microsoft.com/en-us/library/ms181362.aspx https://msdn.microsoft.com/en-us/library/ms181362.aspx It's been discussed on the pg mailing lists but nothing has happened so far
  - masklinn 11y ago
    An output parameter seems somewhat inconvenient to use from an application context, but yes that's a feature which — as the MSDN correctly notes — would be extremely useful for multiuser with connection pooling environment.
kawera 11y ago
For small multi-tenant apps, we use one database per customer. It's simple and works pretty well.
elchief 11y ago
by multi-tenant you mean tenant info in table column or by schema, or db?
- porker 11y ago
  I'm confused by your question, but I think "table column". Think generic SaaS application: one app, one (sharded) database, one set of tables, used by many different companies.