[menssen]

> you just need to parse incoming sql to sanitize it and make sure there is no role escalation

"Just"? I guess I'm skeptical of a statement that begins "you just have to parse sql".

Is this actually easier than I'm imagining it? I'd be curious to hear more about the security and authorization model of this approach.

[CuriouslyC]

You don't have to do full statement parsing, you basically just have to do a limited parse that looks for the various ways that someone could execute a set role statement. As long as you don't let a user execute set role, you use db roles for user accounts, you have a reasonable statement timeout in place to prevent DOS, and your postgres security model is tight (the big gotcha is not to allow access to untrusted code), this approach works fine.

A good tool for this purpose is https://github.com/JavaScriptor/js-sql-parser as it will fail to parse complex statements that are likely to include an attack vector.

In terms of authorization, you can either create per user connection pools if using web sockets and log the user in directly that way (which makes things easy) or if you must use rest, use a single connection pool with a master user then use some form of token to tell the shim who to set role to before executing the query.