> rjuju123@xxxxxxxxx wrote:
>
>> bryn@xxxxxxxxxxxx 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; --'
> - 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 available connection 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's backend can all be hidden from client code. The technique is simple and has been in use forever. That's why RDBMSs 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" owns nothing, lacks the "create" privilege on the database in question, and (for good measure) lacks "create" on all schemas in 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 out of scope in my toy demo because the requirements statement simply allows a session that's authorized as "client" to issue any SQL statement. I don't know why you picked the "students" table when there isn't one. I just ran my demo code to completion, 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 default in 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 millions of 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 care to in order to access the intended app functionality". This is why the usual paradigm is to grant only "execute" on a designed set of subprograms that each implements a specified *business* function. People have been banging on about this approach since the late eighties (and probably since before then). Of course, the approach depends on a designed use of a privilege scheme. PG supports all this nicely. It's easy to implement an upper limit (in if-then-else code) on the number of rows that a procedure that implements "insert" allows. I s'pose that you'll say that the bad guy could call the procedure time and again. But techniques are available there too. (They're roughly analogous to what stops you making withdrawals 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 for the 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. I don'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 in the hands of the session that authorizes as "client". I know of another RDBMS that has a robust, server-side, resource management scheme that can be set up so that, in my example, "client" could not change the rules. I've not looked to see if PG has anything native for this. But I dare say that somebody could implement an extension with a C implementation to 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 to allow 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".
