A question to all software security specialists of devRant. Please, take it serious.

Is it fundamentally possible to restrict a SQL database like Postgres in a way that unintended SQL queries are impossible to execute? Perhaps in some kind of whitelist fashion. Is it possible to achieve the kind of security that will be just fine exposed to the outside world akin to "SQL queries in onClick handlers" scenario?

Or is this an uphill battle of never being able to moderate an infinite set of possible fraudulent queries?

Can't you just restrict user access to particular tables? If you're concerned about too wide set of 'where' conditions -- create views with subsets of those tables enforcing restrictions you want, and only allow users to access them.

I seriously doubt you could whitelist queries. That's what applications are there for.

The closest thing that comes to my mind is grant and deny commands

https://sqlstudies.com/2016/05/...

You could always do everything as stored procedures only and disallow arbitrary SQL execution. Of course, you'll almost certainly need to be able to pass parameter into them anyway, which would need to be sanitized too, though maybe a little easier.

But, I've lived this dream... had an app many years ago with about 400 stored procs. I don't recommend it.

But it works. Nominally.

Yes, it is possible.

In the best form, proxying - several exist afaik.

If you want postgres only, I'm not entirely sure, but I think you could handle it via an plugin. If I remember correctly a plugin is in a nutshell unrestricted in postgres regarding it's access to internal stuff....

I would say that without a full sql parser that can rewrite any query to ensure the restrictions you need to have in place it will be all but impossible to provide a public sql interface.

Odata and similar does just this. Its a query format that you then map to your underlying data, enforcing any restrictions by adding them to the actual generated sql query.

And sure, if you first parse the query to some abstract form and then add the filters required, it should be doable, but unless your going to spend a fortune on it it at best will be a small subset of sql

Just using permissions might work on paper, but a single known vulnerability and an attacker gets through.