Re: Security Issues: Allowing Clients to Execute SQL in the Backend.

On 30/04/14, Hello World (worldanizer(at)gmail(dot)com) wrote:
> I'm developing a web application that needs to display data from a postgres
> backend.
>
> The most convenient way for the app to get the data is by expressing the
> request in SQL.
>
> I'm thinking about the following architecture
>
> [ App/Client ] -----> query in SQL ---> [Web server] ---> same SQL query
> --> [PG database]
...
> Given this are there any security other issues about letting client
> applications execute arbitrary SQL commands on the backend database?

If you wrap your queries into plpgsql functions you can provide a
managed interface to clients using Postgres security which provide calls
which are unlikely to badly affect server performance. For instance you
can not only control the complexity of the query performed but also fix
hard limits such as the number of rows returned. This approach partly
meets your criteria of allowing SQL commands from client apps, but not
arbitrary ones.

--
Rory Campbell-Lange