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

Поиск
Список
Период
Сортировка
От Chris Travers
Тема Re: Security Issues: Allowing Clients to Execute SQL in the Backend.
Дата
Msg-id CAKt_ZftVzNpYdwQPL=pbCGd+BUDXv8_TUT5f-qZDrnbTpEenVQ@mail.gmail.com
обсуждение исходный текст
Ответ на Security Issues: Allowing Clients to Execute SQL in the Backend.  (Hello World <worldanizer@gmail.com>)
Список 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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Alberto Cabello Sánchez
Дата:
Сообщение: Re: importing a messy text file
Следующее
От: David Noel
Дата:
Сообщение: Re: SQL query runs fine on one platform (FreeBSD), but hangs on another (Windows)