Re: Use "CREATE USER" in plpgsql function

And dynamic SQL leads easily to SQL injection so quoting is required there.

execute 'create user ' || quote_ident(i_username) || ' password ' ||
quote_literal(i_password);

On Wed, Sep 15, 2010 at 5:26 PM, Igor Neyman <ineyman(at)perceptron(dot)com> wrote:

>
>
> > -----Original Message-----
> > From: Tatarnikov Alexander [mailto:cankrus(at)gmail(dot)com]
> > Sent: Wednesday, September 15, 2010 12:05 AM
> > To: pgsql-sql(at)postgresql(dot)org
> > Subject: Use "CREATE USER" in plpgsql function
> >
> > Hello!
> >
> > I have function wich check user credentials and if test
> > passed function must create new user with generated username
> > and password.
> >
> > Language is plpgsql.
> >
> > For example:
> >
> > ....
> > DECLARE creds RECORD;
> > ...
> > SELECT * INTO creds FROM ...
> >
> > creds is Record with fields userName(VARCHAR) and
> > userPassword(VARCHAR)
> >
> > so when i use CREATE USER creds."userName" WITH PASSWORD
> > creds."userPassword"
> >
> > i get an error, because creds."userName" is VARCHAR and thus
> > when function runs it will be look like this:
> > CREATE USER 'user_1'
> > but right command is
> > "CREATE USER user_1" OR " CREATE USER "user_1" "
> >
> > so question is how to "unembrace" this parameter (i mean
> > creds."userName")?
> >
> > Thanks
> > --
> > ------
> > Alexander
> >
>
> It is called "dynamic" sql:
>
> EXECUTE 'CREATE USER creds.' || userName || ' WITH PASSWORD creds.' ||
> userPassword;
>
>
> Read about "dynamic" sql in PG docs:
>
> http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html
>
> Specifically: "38.5.4. Executing Dynamic Commands"
>
> Regards,
> Igor Neyman
>
>
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>