Thread: Question on utility statements and parameterization

Hi all;

As I understand, utility statements are not generally parameterized, so:

postgres=# alter user test123 with password 'foo' valid until now() +
'1 day'::interval;
ERROR:  syntax error at or near "now"
LINE 1: ...lter user test123 with password 'foo' valid until now() + '1...

Is this by design?  If so, what is the reason?  If not, what would
have to be done to change this?

Best Wishes,
Chris Travers

Hello

2011/7/19 Chris Travers <chris.travers@gmail.com>:
> Hi all;
>
> As I understand, utility statements are not generally parameterized, so:
>
> postgres=# alter user test123 with password 'foo' valid until now() +
> '1 day'::interval;
> ERROR:  syntax error at or near "now"
> LINE 1: ...lter user test123 with password 'foo' valid until now() + '1...
>
> Is this by design?  If so, what is the reason?  If not, what would
> have to be done to change this?
>
> Best Wishes,
> Chris Travers
>

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;
$$;

Regards

Pavel

> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

On Tue, Jul 19, 2011 at 2:40 PM, Pavel Stehule <pavel.stehule@gmail.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