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]
***********
I would simply use the roles/permssion system inside Postgres to determine what users can do and cannot do. Clients have to authenticate as one of the roles (not superusers) defined in the database. ************
Given this are there any security other issues about letting client applications execute arbitrary SQL commands on the backend database?
In an ideal world, no. In the real world, absolutely.
1. Are all queries equivalent? I.e. can stuff be inserted, then updated, then changed back in arbitrary ways?
For example, if you rely on something like document.approved going from false to true but never the other, then absolutely this could be a problem.
2. Are you confident that your database logic will never become more complex with things like security definer triggers to pose issues there?
Thanks.
Now, you do probably want a managed interface. This could be some combination of views and/or functions. I prefer the latter (with the PGObject Perl framework) but the former is more common. That allows you to separate what your applications expect to see from how your data is laid out in your database. That avoids having to rewrite your application when you change the physical table layout.
--
Best Wishes,
Chris Travers
Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in.
