Re:Row Level Security with PostgreSQL 9.5
by snuxoll on Thursday April 21, 2016 @02:16AM
(#11540014)
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).
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?".
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
[2] http://initd.org/psycopg/docs/usage.html#sql-injection