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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Wed, Apr 30, 2014 at 12:32 AM, Hello World <worldanizer@gmail.com> wrote:

Hello!

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.
http://www.efficito.com/learn_more

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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:

http://www.postgresql.org/docs/8.4/interactive/sql-set.html

Geoff

On Wed, Apr 30, 2014 at 6:19 AM, Hello World <worldanizer@gmail.com> wrote: