Re: [HACKERS] Updating column on row update

On 23/11/2009 11:35 PM, Tom Lane wrote:
> Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk> writes:
>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>> Tom> Well, that's pretty much exactly the question --- are there? It
>> Tom> would certainly make it easier for someone to exploit any other
>> Tom> security weakness they might find.
>
>> Loops in plain SQL are no problem: see generate_series. The last time
>> we discussed this I demonstrated reasonably straightforward SQL
>> examples of how to do things like password-cracking (and that was long
>> before we had CTEs, so it would be even easier now); my challenge to
>> anyone to produce examples of malicious plpgsql code that couldn't be
>> reproduced in plain SQL went unanswered.
>
> The fact remains though that the looping performance of anything you can
> cons up in straight SQL will be an order of magnitude worse than in
> plpgsql; and it's a notation the average script kiddie will find pretty
> unfamiliar. So I think this still does represent some barrier. Whether
> it's enough of a barrier to justify keeping plpgsql out of the default
> install is certainly debatable.

"the average script kiddie" doesn't write their own exploits. They tend
to use proofs of concept created by very, very smart people involved in
active security research (be it malicious or not) that've been wrapped
up in easy-to-use "click to exploit" tools. They'll no more learn SQL to
explot Pg than they learn x86 asm to write their payload injectors, or
learn details about the Linux kernel to use a local root exploit.

Just making it a bit harder doesn't stop determined attackers, such as
security researchers, criminals seeking confidential information (credit
card databases etc) or commercially-motivated black hats seeking to
build botnets. Once the determined attackers find a way, the script
kiddies and the botnet builders tend to follow.

Any attack relying on the presence of PL/PgSQL will have to be an attack
by an already-authenticated user* with an established connection. Mass
botnet- or worm- style exploits are out. That said, PL/PgSQL does
undeniably increase the available attack surface for a rogue authorized
user, simply by being more code that has to be free of security issues.
An authenticated user might seek to escalate to DB superuser priveleges,
gain access to other DBs, or execute code as the "postgres" user to
trigger a local root exploit on the hosting machine. So there is some
concern, since not all authenticated users are necessarily fully trusted.

It's for that reason that I proposed it being made available by default
only to superusers and to the owner of the current database. If either
of those are executing malicious code, you're already well and truly
screwed. Thinking about it some more, though, there's nothing that'll
let the DB owner (unlike the superuser) execute supplied code as the
"postgres" user or break into other DBs, so an exploit against PL/PgSQL
might still give them a way to escalate priveleges.

I don't see making PL/PgSQL available by default only to a superuser as
particularly useful. Anything else does increase the attack surface
available for potential exploit. So the question becomes "is that
increase a sufficient worry to block the installation of PL/PgSQL by
default" ?

Personally, I think not. Default installing PL/PgSQL doesn't increase
the risk of a worm, which would be my main worry about enabling a
feature by default. PL/PgSQL is so widely used that any security issue
with it is already about as critical as it can be. Those few with
significant databases who do NOT use it can drop it if they are
concerned, but I sincerely doubt there are many significant production
DBs out there without it installed and in use, as it's effectively a
requirement for the use of triggers.

So - I say go ahead and install it by default, available to all users.
It's meant to be a trusted language, it's basically required for
triggers, it's nearly universal anyway, and it's a pain not having it
installed.

If it's not to be installed by default, then a cleaner way to ensure
it's installed it would be strongly desirable.

(I'm also honestly not sure what relevance performance has here. If it
takes an attacker 10 minutes to exploit a server rather than 1 minute,
is it any less cracked? Performance is only an issue if it renders an
attack impossible due to memory/storage requirements, or non-linear
computation time growth. Anyway, I frequently seek to avoid PL/PgSQL,
using pure SQL loops etc instead, because it's *faster* that way.)

* It could, I guess, be a hijacked connection, but if you have
connection hijacking going on you've already lost and have bigger things
to worry about. Otherwise it's going to be a stolen username/password,
or an authorized user gone rogue.