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

> 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
operationsthat 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; --'
> - millions of 'cat' rows
> - millions of 1GB-large rows
>
> or just keep sending massive invalid query texts to fill the logs, or just trying to connect until there's no
availableconnection slots anymore, and then keep spamming the server thousands of time per second to try to open new
connections,or ...? 

My little code example was meant to show the basic principle: that the within-database artifacts that implement an
application'sbackend can all be hidden from client code. The technique is simple and has been in use forever. That's
whyRDBMSs like PG have a privilege scheme. Object ownership and "current_role" are a central notions in any such
scheme.

My demo depended upon an ordinary human regime of password secrecy. The key point that my demo made was that "client"
ownsnothing, lacks the "create" privilege on the database in question, and (for good measure) lacks "create" on all
schemasin the database. Therefore, a session that authorizes as "client" is limited in what it can do. 

I'm not sure what you mean to say with this fragment:

 'robert''); drop table students; --'

It rather looks like something that you see in an essay about SQL injection. But the entire SQL injection discussion is
outof scope in my toy demo because the requirements statement simply allows a session that's authorized as "client" to
issueany SQL statement. I don't know why you picked the "students" table when there isn't one. I just ran my demo code
tocompletion, re-connected as "client", and did this: 

drop table students;

It produces the "42P01: error: table "students" does not exist. Of course, the message isn't lying. So this is a better
test:

drop view s.v;

This produces the "42501" error: must be owner of view v. This isn't a lie either. The hacker has now learned that, at
least,such a view does exist. Arguably, the different between the two errors is a bad thing. And famously, in Oracle
Database,you get a more generic "computer says no" in both cases. But PG is the way it is here and won't change in my
lifetime.So, playing the hacker role, I tried this: 

select definition from pg_views where schemaname = 's';

It caused the "42501" error: permission denied for view pg_views. And why shouldn't it? I didn't mention that I'd
revoked"select" on every "pg_catalog" relation (and every "information_schema" relation) from public and then granted
"select"explicitly on each to "u1" but not to "client". 

This is the text-book principle of least privilege: you start with nothing and add what you need. For historical
reasons,very few systems honor this principle by default. But it's an excellent feature of PG that you can overrule the
defaultin the way that I described. The present toy demo works fine (all the tests behave the same) after my hardening
intervention.

About inserting millions of rows, well... that's a word-game. The spec for my toy demo never mentioned that inserting
millionsof rows should be prevented. 

There's only so far that you can go if you decide to articulate the hard-shell API as "use any SQL statement that you
careto in order to access the intended app functionality". This is why the usual paradigm is to grant only "execute" on
adesigned set of subprograms that each implements a specified *business* function. People have been banging on about
thisapproach since the late eighties (and probably since before then). Of course, the approach depends on a designed
useof a privilege scheme. PG supports all this nicely. It's easy to implement an upper limit (in if-then-else code) on
thenumber of rows that a procedure that implements "insert" allows. I s'pose that you'll say that the bad guy could
callthe procedure time and again. But techniques are available there too. (They're roughly analogous to what stops you
makingwithdrawals from a bank account when the credit limit is reached.) Blocking a single "huge" row is trivial.
Probably,a constraint that uses a SQL expression would suffice. But you can always implement the user-defined function
forthe hugeness test if you need to. 

This leaves us with some kind of denial of service attack that uses a flavor of busy work or similar, like you mention.
Idon't think that there's any way that PG can prevent a connected role doing this: 

do $body$
begin
  loop
    <an operation that can't be prevented>
  end loop;
end;
$body$;

or, say, a "select" with a recursive CTE with no stopping condition. There's always "set statement_timeout"—but that's
inthe hands of the session that authorizes as "client". I know of another RDBMS that has a robust, server-side,
resourcemanagement scheme that can be set up so that, in my example, "client" could not change the rules. I've not
lookedto see if PG has anything native for this. But I dare say that somebody could implement an extension with a C
implementationto do something pretty useful in this space. 

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

« The main point of a database is to store your data, to keep it in compliance with all the specified data rules, and
toallow authorized client-side code to modify the data by using only a set of specified business functions. » 

I should have added "preventing busy work that has no effect on the persisted data is out of scope".