On 2017-01-11 00:49:19 -0800, Guyren Howe wrote:
> I’m not following. What I would like is just a lightweight way to switch the
> connections to use a different role, or some moral equivalent, that would
> prevent an SQL injection from wrecking havoc. I’m not proposing anything that
> will change anything else about how the application is using the database.
>
> SET ROLE doesn’t work, because an SQL injection can just SET ROLE back to the
> privileged user.
But then you are no worse off than with the commonly used scheme of
executing all queries as the same (necessarily "privileged") user.
In both cases the attacker can execute queries as a privileged user IF
he succeeds at sql injections.
But as others have already noted this is relatively easy to prevent.
Just preparing all queries is sufficient, even if you don't actually
parametrize them. Perl DBI does this, so this dangerous-looking line of
code (assume that the string wasn't hardcoded but the result of an SQL
injection):
$r = $dbh->selectall_arrayref("select * from twoqueries; insert into twoqueries(t) values('b')");
will fail with
DBD::Pg::db selectall_arrayref failed: ERROR: cannot insert
multiple commands into a prepared statement at ./twoqueries line 21.
So I think just using
set local role
at the beginning of each transaction should work well with session
pooling. It doesn't protect you against sql injections, but you won't
have to reinvent the authorization system.
> I would like a regime where there is no permanent privileged relationship
> between the client application and the database; a user would need to supply
> validating information that we can use to switch a connection to something with
> minimal privileges for just that role, for the duration of one session or
> transaction.
I haven't read the blog post referenced in this thread yet, so maybe
this is covered there, but I think "supplying validating information"
would be the hard part. In general you wouldn't want a web-frontend to
cache plain-text passwords to resubmit them for each transaction, but to
use something more ethereal, like session cookies or kerberos tickets.
hp
--
_ | Peter J. Holzer | A coding theorist is someone who doesn't
|_|_) | | think Alice is crazy.
| | | hjp@hjp.at | -- John Gordon
__/ | http://www.hjp.at/ | http://downlode.org/Etext/alicebob.html