Re: pl/pgsql enabled by default

On 2005-05-07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Neil Conway <neilc(at)samurai(dot)com> writes:
>> Users can already define SQL functions by default, which certainly
>> provides "programmable features". I'm not quite sure what you mean by
>> "user exits."
>
>> I guess I'm missing how pl/pgsql is a fundamentally greater security risk.
>
> plpgsql has control structures (loops, IF); SQL functions don't.
> That makes for a fundamental difference in the power of the programming
> language ... at least according to CS theory as I was taught it.

SQL functions do have control structures: CASE WHEN and recursion. I have
even implemented generate_series() in pure SQL.

> Now
> admittedly the "primitive statements" of SQL are a lot more powerful
> than the primitive statements usually considered in programming language
> theory, but I think there is still a pretty significant difference in
> capability.

The only thing that makes pg's SQL functions not turing-complete is the
fact that recursion depth is not unlimited. In practice this isn't much
of a restriction, since you can do large-scale iterations by using SQL
sets and joins rather than recursion.

> An example of why this could be interesting from a security standpoint
> is that, given access to pg_shadow, it'd be pretty trivial to write
> a plpgsql function that tries to break user passwords by brute force
> (just generate possible passwords, hash them, and look for a match).
> I don't see any way to do that in plain SQL, at least not without a
> pre-existing SRF to generate the trial passwords for you.

Writing a pure SQL SRF that generates a range of trial passwords is in
fact not hard at all. The key point to note is that you can generate
sets of literal values of moderate size using UNION ALL, and you can
then cross-join those sets against themselves multiple times to generate
much larger and more complex sets.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services