Обсуждение: Security Issues: Allowing Clients to Execute SQL in the Backend.
Hello!
I'm developing a web application that needs to display data from a postgres backend.[ 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.
************
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?
Thanks.
Hello World wrote: > Given this are there any security other issues about letting client applications execute arbitrary SQL > commands on the backend database? There shouldn't be any security problems, just be careful that you don't give the user more permissions than you want to. But a user who can execute arbitrary queries can easily bring the system down: You can write SQL queries that keep a CPU 100% busy, that exhaust disk space and possibly memory. Yours, Laurenz Albe
Hello,
Thank you very much.
Denial of service is indeed a problem. Is there a way to limit the execution time of a request?
I'm using libpq to communicate with the server.
PS. I've just taken a look, it seems I could do some asynchronous queries, time them, then cancel them if they take too long.Thank you very much.
Denial of service is indeed a problem. Is there a way to limit the execution time of a request?
I'm using libpq to communicate with the server.
http://www.postgresql.org/docs/8.4/static/libpq-cancel.html
On 30/04/14, Hello World (worldanizer@gmail.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
On Wed, Apr 30, 2014 at 12:32 AM, Hello World <worldanizer@gmail.com> wrote:
I'm thinking about the following architectureThe most convenient way for the app to get the data is by expressing the request in SQL.Hello!I'm developing a web application that needs to display data from a postgres backend.
[ 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.
Hello World wrote: > Denial of service is indeed a problem. Is there a way to limit the execution time of a request? Yes, setting statement_timeout. But if a client can exectue arbitrary statements, that could also be statements like: SET statement_timeout=0; SET work_mem=1024GB; > I'm using libpq to communicate with the server. > > PS. I've just taken a look, it seems I could do some asynchronous queries, time them, then cancel them > if they take too long. > > http://www.postgresql.org/docs/8.4/static/libpq-cancel.html That might be a way to avoid that people just reset statement_timeout. Of course someone could start a deadly query and then kill the client before it has a chance to cancel it... Yours, Laurenz Albe
SET statement_timeout=0;
SET work_mem=1024GB;
I just realized about the SET command.
Isn't it weird that any user can set parameters such as this that will apply server wide? to all future sessions?
I noticed that some of the parameters can only be set by superusers, and some require re-start, but still. Anybody can re-configure the server..... ??
They don't apply server-wide. They apply to that user's session:
Geoff
On Wed, Apr 30, 2014 at 6:19 AM, Hello World <worldanizer@gmail.com> wrote:
SET statement_timeout=0;
SET work_mem=1024GB;I just realized about the SET command.Isn't it weird that any user can set parameters such as this that will apply server wide? to all future sessions?I noticed that some of the parameters can only be set by superusers, and some require re-start, but still. Anybody can re-configure the server..... ??