Re: Limiting the operations that client-side code can perform upon its database backend's artifacts - Mailing list pgsql-general

From Peter J. Holzer
Subject Re: Limiting the operations that client-side code can perform upon its database backend's artifacts
Date
Msg-id 20220927222637.aolob6gk3kuyirfs@hjp.at
Whole thread Raw
In response to Re: Limiting the operations that client-side code can perform upon its database backend's artifacts  (Julien Rouhaud <rjuju123@gmail.com>)
Responses Re: Limiting the operations that client-side code can perform upon its database backend's artifacts
List pgsql-general
On 2022-09-27 14:58:58 +0800, Julien Rouhaud wrote:
> On Mon, Sep 26, 2022 at 11:18:34AM -0700, Bryn Llewellyn wrote:
> > My demo seems to show that when a program connects as "client", it can
> > perform exactly and only the database operations that the database design
> > specified.
> >
> > Am I missing something? In other words, can anybody show me a vulnerability?
>
> What exactly prevents the client role from inserting e.g.
>
> - 'robert''); drop table students; --'

It can do this but it won't do any harm since the client role doesn't
have permission to drop the table-

> - millions of 'cat' rows
> - millions of 1GB-large rows

That depends on "the database operations that the database design
specified", but if the client role is supposed to be able to insert
data, you can't really prevent it from inserting non-sensical or
enormous data. You can encapsulate the insert functionality in a
function or procedure and do some sanity checks there. But automatically
distinguishing between legitimate use and abuse is generally not simple.

> or just keep sending massive invalid query texts to fill the logs, or just
> trying to connect until there's no available connection slots anymore, and then
> keep spamming the server thousands of time per second to try to open new
> connections, or ...?

There are often several layers of defense. The database frequently won't
be accessible from the open internet (or even the company network)
directly. Only a middle tier of application servers running vetted
client code will connect directly. Even those servers may not be
accessible directly to end users. There may be a layer of proxy servers
above them. Each of these layers may implement additional checks, rate
limits and monitoring.

        hp

--
   _  | Peter J. Holzer    | Story must make more sense than reality.
|_|_) |                    |
| |   | hjp@hjp.at         |    -- Charles Stross, "Creative writing
__/   | http://www.hjp.at/ |       challenge!"

Attachment

pgsql-general by date:

Previous
From: Federico
Date:
Subject: Re: Order by in a sub query when aggregating the main query
Next
From: Bryn Llewellyn
Date:
Subject: Re: Limiting the operations that client-side code can perform upon its database backend's artifacts