3 ms·

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

by takeda 11y ago

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.

orf 11y ago
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.
- takeda 11y ago
  > 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 http://initd.org/psycopg/docs/usage.html [2] http://initd.org/psycopg/docs/usage.html#sql-injection http://initd.org/psycopg/docs/usage.html#sql-injection
  - snuxoll 11y ago
    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).
    - takeda 11y ago
      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?".
rietta 11y ago
Please tell us more about this Merb workaround! Was it merged into Rails or did it fall away?
kyllo 11y ago
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
caf 11y ago
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.

by takeda 11y ago

orf 11y ago

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.

takeda 11y ago
> 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 http://initd.org/psycopg/docs/usage.html [2] http://initd.org/psycopg/docs/usage.html#sql-injection http://initd.org/psycopg/docs/usage.html#sql-injection
- snuxoll 11y ago
  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).
  - takeda 11y ago
    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?".

rietta 11y ago

Please tell us more about this Merb workaround! Was it merged into Rails or did it fall away?

kyllo 11y ago

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

caf 11y ago

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.