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

On Tue, Sep 27, 2022 at 05:27:22PM -0700, Bryn Llewellyn wrote:
> > hjp-pgsql@hjp.at wrote:
> >
> >> rjuju123@gmail.com wrote:
> >>
> >>> bryn@yugabyte.com 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.

FTR it's a reference to https://xkcd.com/327/

Both of you are saying it's harmless because you're assuming that only the
client role may read the data and act on it, but the whole point of SQL
injection is to try to do actions that the role you have access to can't
already do.  And that's just a few out of dozens of examples of how having a
role connected to the database can do harm.

> >
> >> - 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.

Which is exactly what was the idea behind Tom's "if you don't trust another
session that is running as your userID, you have already lost".

> >
> >> 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.

If no one has direct SQL access to the database, then there's no problem with a
role being able to pg_terminate_backend() session for the same role, and this
thread shouldn't exist to begin with.

> I'm afraid that I didn't see this from you until I'd already replied to
> Julien's turn in this thread. Sorry that I caused thread divergence. Thanks,
> Peter, for addressing the contribution(s) that other tiers in the stack make
> (and uniquely are able to make) in order to deliver the intended application
> functionality to the end user.

Your whole argument in your other email was:

> Anyway... this kind of denial of service discussion is way outside the scope
> of what I addressed.

which looks like in total contradiction with your original email:

> Am I missing something? In other words, can anybody show me a vulnerability?

Again, don't give SQL access to untrusted users and you will avoid a lot of
problems, including someone abusing pg_terminate_backend().