> rjuju...@gmail.com wrote:
>
>> b...@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; --'
> - 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".
