Re: Question on utility statements and parameterization

On Tue, Jul 19, 2011 at 2:40 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:

>>
>
> If I understand well , a utility has no plan, and a parameters are
> implemented as plan's parameters.
>
> you can use a dynamic sql in plpgql
>
> DO $$
> BEGIN
>  EXECUTE 'ALTER USER test123 WITH PASSWORD 'foo' VALID UNTIL ' ||
> to_char(CURRENT_DATE + 1, 'YYYY-MM-DD');
> END;
> $$;

That's currently what I do. and if you are correct that answers my question.

The reason I was asking is that currently I maintain applications
which use pg roles as application users. Users are allowed to change
their passwords through the sorts of dynamic SQL you mention (with
liberal uses of quote_literal and quote_ident). These of course have
to run as security definer.

However, what this means is that frequently we have to review the code
in a detailed way to ensure that the quoting functions haven't been
omitted. If they are omitted, well, I am sure you can appreciate the
issues that could result from sql injection in a security definer
function. Parameterized statements would certainly make things more
robust on this side and less error prone, esp. where the error could
cause serious security problems.

Not that such code reviews are bad, but just that it wold be nice to
have the warning signs be a little more obvious.

Of course, if it can't change without major intrusive changes, it
can't change. I've been living with it for quite a while. Even with
the additional hassle this method of managing users still seems well
worth it.

Best Wishes,
Chris Travers