[SQL] Use "CREATE USER" in plpgsql function

[Tatarnikov Alexander]

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

Re: [SQL] Use "CREATE USER" in plpgsql function - Found word(s) list error in the Text body

[Tatarnikov Alexander]

Finally i'm using:

EXECUTE 'CREATE USER '|| quote_ident(creds."userName") ||' WITH PASSWORD '||
quote_literal(creds."userPassword") || ' IN GROUP ta_users';

and it works perfect.

Thanks!


2010/9/16 Igor Neyman 

>
>
>  --
> *From:* Asko Oja [mailto:asc...@gmail.com]
> *Sent:* Wednesday, September 15, 2010 2:29 PM
> *To:* Igor Neyman
> *Cc:* Tatarnikov Alexander; pgsql-sql@postgresql.org
> *Subject:* Re: [SQL] Use "CREATE USER" in plpgsql function - Found word(s)
> list error in the Text body
>
> 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 wrote:
>
>>
>>
>> > -Original Message-
>> > From: Tatarnikov Alexander [mailto:cank...@gmail.com]
>> > Sent: Wednesday, September 15, 2010 12:05 AM
>> > To: pgsql-sql@postgresql.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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-sql
>>
>
>
> [I.N.] Opps.
> Missed  quote_ident() in your message, sorry.
>
>


-- 
--
С уважением,
Татарников Александр

[SQL] Different plan for one query problem

[Tatarnikov Alexander]

->  Index Scan
using register_pkey on tbl_register  (cost=0.00..8.18 rows=1 width=35)
    Index
Cond: (tbl_register.register_id = tbl_inventory.register_id)
"->  Index Scan using
table3_pkey on ""fileObjects""  (cost=0.00..0.28 rows=1 width=32)"
"  Index Cond:
(""fileObjects"".""objectId"" = tbl_rights.object_id)"
"  ->  Index Scan using
""invFiles_new_pkey"" on ""invFiles""  (cost=0.00..0.30 rows=1 width=16)"
"Index Cond: (""invFiles"".id =
""fileObjects"".""fileId"")"
"Filter:
((""invFiles"".""placeStore"")::text = '12'::text)"
->  Index Scan using lnk_owners_right_id_key
on lnk_owners  (cost=0.00..0.41 rows=1 width=32)
  Index Cond: (lnk_owners.right_id =
tbl_rights.right_id)
  ->  Index Scan using powners_pkey on tbl_powners
(cost=0.00..0.50 rows=1 width=75)
Index Cond: (tbl_powners.powners_id =
lnk_owners.powners_id)
->  Index Scan using objects_main_pkey1 on
tbl_objects_main  (cost=0.00..1.38 rows=1 width=36)
  Index Cond: (tbl_objects_main.object_id =
tbl_rights.object_id)
  ->  Index Scan using tbl_position_idx on tbl_position
(cost=0.00..8.28 rows=1 width=21)
Index Cond: (tbl_position.object_id =
tbl_objects_main.object_id)
->  Seq Scan on tbl_location  (cost=0.00..22516.85 rows=464985
width=356)
"  ->  Index Scan using ""kladrCache_pkey"" on ""kladrCache""
(cost=0.00..0.28 rows=1 width=104)"
"Index Cond: ((""kladrCache"".code)::text =
(tbl_location.kladr_id)::text)"


What caused changing plan? Can i force to use one (first in expamples) plan
for any values in where clause?

Thanks
-- 
--
Regards,
Tatarnikov Alexander