Re: [SQL] Use "CREATE USER" in plpgsql function
2010/9/15 Tatarnikov Alexander : > Thanks for response! > > Here is function > > CREATE USER creds."userName" WITH PASSWORD creds."userPassword" > IN GROUP ta_users; - there is error occured You can not use variables for non-data entities. Use the dynamic SQL instead: EXECUTE 'CREATE USER ' || creds."userName" || ' WITH PASSWORD ' || creds."userPassword" || ' IN GROUP ' || ta_users; Read more here http://www.postgresql.org/docs/8.4/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN p.s. I suggest you to read it with care http://wiki.postgresql.org/wiki/Guide_to_reporting_problems before reporting a problem next time. > > DECLARE cred VARCHAR; > DECLARE passed BOOLEAN; > DECLARE creds RECORD; > BEGIN > SELECT (ta_base.user_accounts."password" = $2) INTO passed > FROM ta_base.user_accounts > WHERE ta_base.user_accounts.user_id = $1; > if (passed) THEN > SELECT * INTO creds FROM "ta_base"."credTable" WHERE > "ta_base"."credTable"."inUse"=FALSE ORDER BY random() LIMIT 1; > INSERT INTO ta_base.logins VALUES (creds."userName", > creds."userPassword", current_timestamp(2), NULL, NULL, $1, TRUE); > UPDATE "ta_base"."credTable" SET "inUse"=TRUE WHERE > "credId"=creds."credId"; > CREATE USER creds."userName" WITH PASSWORD creds."userPassword" > IN GROUP ta_users; - there is error occured > cred:=N'pass'; > else > cred:=N'failed'; > end if; > return cred; > END; > > 2010/9/15 Sergey Konoplev >> >> Hi, >> >> On 15 September 2010 08:05, Tatarnikov Alexander >> wrote: >> > 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. >> > >> > so question is how to "unembrace" this parameter (i mean >> > creds."userName")? >> >> Show the whole function please. >> >> > >> > Thanks >> > -- >> > -- >> > Alexander >> > >> >> >> >> -- >> Sergey Konoplev >> >> Blog: http://gray-hemp.blogspot.com / >> Linkedin: http://ru.linkedin.com/in/grayhemp / >> JID/GTalk: gray...@gmail.com / Skype: gray-hemp / ICQ: 29353802 > > > > -- > -- > С уважением, > Татарников Александр > -- Sergey Konoplev Blog: http://gray-hemp.blogspot.com / Linkedin: http://ru.linkedin.com/in/grayhemp / JID/GTalk: gray...@gmail.com / Skype: gray-hemp / ICQ: 29353802 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Use "CREATE USER" in plpgsql function
> -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
Re: [SQL] 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 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 >
Re: [SQL] Use "CREATE USER" in plpgsql function - Found word(s) list error in the Text body
> -Original Message- > 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: > That's too "generic". I was answering specific question. Now, yes, dynamic sql could be used for SQL injection, if not used carefully. But, it exists for a reason. And in this particular case userName and userPassword retrieved from a table. So, care should be taken (appropriate checks to be done) when these values inserted into the table. Btw., do you have another answer to OP question? 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
Re: [SQL] Use "CREATE USER" in plpgsql function - Found word(s) list error in the Text body
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.
Re: [SQL] Use "CREATE USER" in plpgsql function - Found word(s) list error in the Text body
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] with queries
Hullo. I've been a Postgresql user for many years, so forgive me if I'm doing something really stupid. I cannot get 'with' queries to work at all. The test query that I've entered below (as an example) is direct from the section '7.8 WITH Queries' of the manual, so I'd expect that to work wouldn't I? test=# WITH RECURSIVE t(n) AS (VALUES (1) UNION ALL SELECT n+1 FROM t WHERE n < 100) SELECT sum(n) FROM t; ERROR: syntax error at or near "WITH RECURSIVE" LINE 1: WITH RECURSIVE t(n) AS (VALUES (1) UNION ALL SELECT n+1 FROM... ^ test=# Tried this with 8.4 and 9.0rc1, same result. Any help (even to tell me that I'm an idiot!) would be much appreciated. In the meantime my query will revert to Plan A, but it would be so much neater using a 'with'. -- Greg Quinn Australian Phenomics Facility greg.qu...@anu.edu.au (02) 61259407 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] with queries
Forget my previous post. I am stupid. It doesn't work with 8.3.7, sorry to trouble you. -- Greg Quinn Australian Phenomics Facility greg.qu...@anu.edu.au (02) 61259407 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
