pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
BUG #16519: SET SESSION ROLE in plpgsql requires string literal.
7+ messages / 4 participants
[nested] [flat]

* BUG #16519: SET SESSION ROLE in plpgsql requires string literal.
@ 2020-06-30 16:20  PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 7+ messages in thread

From: PG Bug reporting form @ 2020-06-30 16:20 UTC (permalink / raw)
  To: pgsql-bugs@lists.postgresql.org; +Cc: mbwiese@gmail.com

The following bug has been logged on the website:

Bug reference:      16519
Logged by:          Marianne B. Wiese
Email address:      mbwiese@gmail.com
PostgreSQL version: 10.12
Operating system:   Ubuntu 18.04
Description:        

The documentation says: 
This command sets the current user identifier of the current SQL session to
be role_name. The role name can be written as either an identifier or a
string literal. After SET ROLE, permissions checking for SQL commands is
carried out as though the named role were the one that had logged in
originally.

However, the loop in below function gives me an error:
[22023] ERROR: role "inuser" does not exist Where: SQL statement "SET
SESSION ROLE TO InUser" PL/pgSQL function change_user(name) line 8 at SQL
statement.

I can only make it work with string literals, as in the commented out
section.

CREATE OR REPLACE FUNCTION public.change_user(InUser name) RETURNS void AS
$$
    DECLARE
    r RECORD;
    BEGIN
        FOR r IN SELECT role FROM public.editors
            LOOP
                IF ( InUser = r.role ) THEN
                    SET SESSION ROLE TO InUser;
                end if;
            END LOOP;
/*        IF ( InUser = 'mbw@geus.dk' ) THEN
            SET SESSION ROLE TO 'mbw@geus.dk';
        ELSEIF ( InUser = 'joth@geus.dk') THEN
            SET SESSION ROLE TO 'joth@geus.dk';
        ELSE raise invalid_role_specification using message = 'authenticator
can not be || ' + InUser;
        END IF;
*/
    END
$$ LANGUAGE plpgsql;



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: BUG #16519: SET SESSION ROLE in plpgsql requires string literal.
@ 2020-06-30 16:39  David G. Johnston <david.g.johnston@gmail.com>
  parent: PG Bug reporting form <noreply@postgresql.org>
  0 siblings, 1 reply; 7+ messages in thread

From: David G. Johnston @ 2020-06-30 16:39 UTC (permalink / raw)
  To: mbwiese@gmail.com <mbwiese@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

On Tuesday, June 30, 2020, PG Bug reporting form <noreply@postgresql.org>
wrote:

> The following bug has been logged on the website:
>
> Bug reference:      16519
> Logged by:          Marianne B. Wiese
> Email address:      mbwiese@gmail.com
> PostgreSQL version: 10.12
> Operating system:   Ubuntu 18.04
> Description:
>
> However, the loop in below function gives me an error:
> [22023] ERROR: role "inuser" does not exist Where: SQL statement "SET
> SESSION ROLE TO InUser" PL/pgSQL function change_user(name) line 8 at SQL
> statement.
>
>                     SET SESSION ROLE TO InUser;
>
>
The SET command cannot be parameterized so using variables in the statement
is not supported and the attempt to do so is treated as writing an
identifier.  You will need to use the format function and the execute
plpgsql command to create and execute the statement.

David J.

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: BUG #16519: SET SESSION ROLE in plpgsql requires string literal.
@ 2020-06-30 17:18  Tom Lane <tgl@sss.pgh.pa.us>
  parent: David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2020-06-30 17:18 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: mbwiese@gmail.com <mbwiese@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> The SET command cannot be parameterized so using variables in the statement
> is not supported and the attempt to do so is treated as writing an
> identifier.  You will need to use the format function and the execute
> plpgsql command to create and execute the statement.

While this is documented (last para of "42.11.1. Variable Substitution"),
it's not exactly prominent.  Should we move that to somewhere more
visible?  If so where?

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: BUG #16519: SET SESSION ROLE in plpgsql requires string literal.
@ 2020-06-30 17:50  David G. Johnston <david.g.johnston@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 2 replies; 7+ messages in thread

From: David G. Johnston @ 2020-06-30 17:50 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: mbwiese@gmail.com <mbwiese@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

On Tuesday, June 30, 2020, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > The SET command cannot be parameterized so using variables in the
> statement
> > is not supported and the attempt to do so is treated as writing an
> > identifier.  You will need to use the format function and the execute
> > plpgsql command to create and execute the statement.
>
> While this is documented (last para of "42.11.1. Variable Substitution"),
> it's not exactly prominent.  Should we move that to somewhere more
> visible?  If so where?
>

I think the docs are acceptable as-is - especially given the numerous
cross-references to that section.  If anything i’d maybe call out that most
non-result returning commands are actually not parameterized in “42.2.5
Executing a Command with No Result” just before the link to 42.11.1

David J.

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: BUG #16519: SET SESSION ROLE in plpgsql requires string literal.
@ 2020-07-01 09:26  Marianne B. Wiese <mbwiese@gmail.com>
  parent: David G. Johnston <david.g.johnston@gmail.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Marianne B. Wiese @ 2020-07-01 09:26 UTC (permalink / raw)
  To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

Thanks.
I had forgotten the Execute / Format way and I thought a parameter is an
identifier.

Generally the docs are concise and easy to understand.

Maybe the doc for SET ROLE doc could say something like 'See <link example>
for execution inside a function'

Marianne BW

On Tue, Jun 30, 2020 at 7:50 PM David G. Johnston <
david.g.johnston@gmail.com> wrote:

> On Tuesday, June 30, 2020, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> "David G. Johnston" <david.g.johnston@gmail.com> writes:
>> > The SET command cannot be parameterized so using variables in the
>> statement
>> > is not supported and the attempt to do so is treated as writing an
>> > identifier.  You will need to use the format function and the execute
>> > plpgsql command to create and execute the statement.
>>
>> While this is documented (last para of "42.11.1. Variable Substitution"),
>> it's not exactly prominent.  Should we move that to somewhere more
>> visible?  If so where?
>>
>
> I think the docs are acceptable as-is - especially given the numerous
> cross-references to that section.  If anything i’d maybe call out that most
> non-result returning commands are actually not parameterized in “42.2.5
> Executing a Command with No Result” just before the link to 42.11.1
>
> David J.
>
>

-- 
Marianne B. Wiese
Reventlowsgade 30
1651 København V

+45 51 92 69 18

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: BUG #16519: SET SESSION ROLE in plpgsql requires string literal.
@ 2020-10-02 20:11  David G. Johnston <david.g.johnston@gmail.com>
  parent: David G. Johnston <david.g.johnston@gmail.com>
  1 sibling, 1 reply; 7+ messages in thread

From: David G. Johnston @ 2020-10-02 20:11 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: mbwiese@gmail.com <mbwiese@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

On Tue, Jun 30, 2020 at 10:50 AM David G. Johnston <
david.g.johnston@gmail.com> wrote:

> On Tuesday, June 30, 2020, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>> "David G. Johnston" <david.g.johnston@gmail.com> writes:
>> > The SET command cannot be parameterized so using variables in the
>> statement
>> > is not supported and the attempt to do so is treated as writing an
>> > identifier.  You will need to use the format function and the execute
>> > plpgsql command to create and execute the statement.
>>
>> While this is documented (last para of "42.11.1. Variable Substitution"),
>> it's not exactly prominent.  Should we move that to somewhere more
>> visible?  If so where?
>>
>
> I think the docs are acceptable as-is - especially given the numerous
> cross-references to that section.  If anything i’d maybe call out that most
> non-result returning commands are actually not parameterized in “42.2.5
> Executing a Command with No Result” just before the link to 42.11.1
>
>
Concretely, as attached.

I found the comment about the 9.0 behavior to be unnecessary at this point
and so removed it.

The wording in the PL/SQL conversion comments is a bit out-of-scope but
took a look at it too as it also redirects the user to the variable
discussion and I looked at all 4 cross-references.

David J.

Attachments:

v1-doc-plpgsql-variable-usage-cleanup.patch (application/octet-stream, 4.5K) download commit 678834d2fc1d74710ca36b075be0989832e15cec Author: David G. Johnston <david.g.johnston@gmail.com> Date: Fri Oct 2 20:02:00 2020 +0000 Minor cleanup and rewording of plpgsql docs diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 815912666d..4af66a637c 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -856,7 +856,7 @@ SELECT <replaceable>expression</replaceable> </synopsis> to the main SQL engine. While forming the <command>SELECT</command> command, any occurrences of <application>PL/pgSQL</application> variable names - are replaced by parameters, as discussed in detail in + are replaced by query parameters, as discussed in detail in <xref linkend="plpgsql-var-subst"/>. This allows the query plan for the <command>SELECT</command> to be prepared just once and then reused for subsequent @@ -945,11 +945,13 @@ my_record.user_id := 20; </para> <para> - Any <application>PL/pgSQL</application> variable name appearing - in the command text is treated as a parameter, and then the + Any <application>PL/pgSQL</application> variables appearing + in the command text are replaced by query parameters, and then the current value of the variable is provided as the parameter value at run time. This is exactly like the processing described earlier for expressions; for details see <xref linkend="plpgsql-var-subst"/>. + If the SQL command being executed is incapable of returning a result + it does not accept query parameters. </para> <para> @@ -2507,7 +2509,7 @@ $$ LANGUAGE plpgsql; </para> <para> - <application>PL/pgSQL</application> variables are substituted into the query text, + <application>PL/pgSQL</application> variables are converted into query parameters, and the query plan is cached for possible re-use, as discussed in detail in <xref linkend="plpgsql-var-subst"/> and <xref linkend="plpgsql-plan-caching"/>. @@ -4583,9 +4585,9 @@ CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE FUNCTION snitch(); SQL statements and expressions within a <application>PL/pgSQL</application> function can refer to variables and parameters of the function. Behind the scenes, <application>PL/pgSQL</application> substitutes query parameters for such references. - Parameters will only be substituted in places where a parameter or - column reference is syntactically allowed. As an extreme case, consider - this example of poor programming style: + Query parameters will only be substituted in places where syntactically allowed + (in particular, identifiers can never be replaced with a query parameter.) + As an extreme case, consider this example of poor programming style: <programlisting> INSERT INTO foo (foo) VALUES (foo); </programlisting> @@ -4597,13 +4599,6 @@ INSERT INTO foo (foo) VALUES (foo); variable. </para> - <note> - <para> - <productname>PostgreSQL</productname> versions before 9.0 would try - to substitute the variable in all three cases, leading to syntax errors. - </para> - </note> - <para> Since the names of variables are syntactically no different from the names of table columns, there can be ambiguity in statements that also refer to @@ -5253,13 +5248,14 @@ HINT: Make sure the query returns the exact list of columns. <itemizedlist> <listitem> <para> - If a name used in a SQL command could be either a column name of a - table or a reference to a variable of the function, - <application>PL/SQL</application> treats it as a column name. This corresponds - to <application>PL/pgSQL</application>'s + If a name used in a SQL command matches a known column name and also + a named variable within the function, + <application>PL/SQL</application> treats it as a column name. + By default, <application>PL/pgSQL</application> will treat it as a variable, + but one can specify <literal>plpgsql.variable_conflict</literal> = <literal>use_column</literal> - behavior, which is not the default, - as explained in <xref linkend="plpgsql-var-subst"/>. + (see <xref linkend="plpgsql-var-subst"/> for specifics) + to change the behavior to match <application>PL/SQL</application>. It's often best to avoid such ambiguities in the first place, but if you have to port a large amount of code that depends on this behavior, setting <literal>variable_conflict</literal> may be the
^ permalink raw reply [nested|flat] 7+ messages in thread

* Re: BUG #16519: SET SESSION ROLE in plpgsql requires string literal.
@ 2020-10-21 15:23  David G. Johnston <david.g.johnston@gmail.com>
  parent: David G. Johnston <david.g.johnston@gmail.com>
  0 siblings, 0 replies; 7+ messages in thread

From: David G. Johnston @ 2020-10-21 15:23 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: mbwiese@gmail.com <mbwiese@gmail.com>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>

I've moved this over to -hackers and the commitfest.

https://www.postgresql.org/message-id/CAKFQuwYvMKucM5fnZvHSo-ah4S%3D_n9gmKeu6EAo%3D_fTrohunqQ%40mail...

David J.


On Fri, Oct 2, 2020 at 1:11 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:

> On Tue, Jun 30, 2020 at 10:50 AM David G. Johnston <
> david.g.johnston@gmail.com> wrote:
>
>> On Tuesday, June 30, 2020, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>>
>>> "David G. Johnston" <david.g.johnston@gmail.com> writes:
>>> > The SET command cannot be parameterized so using variables in the
>>> statement
>>> > is not supported and the attempt to do so is treated as writing an
>>> > identifier.  You will need to use the format function and the execute
>>> > plpgsql command to create and execute the statement.
>>>
>>> While this is documented (last para of "42.11.1. Variable Substitution"),
>>> it's not exactly prominent.  Should we move that to somewhere more
>>> visible?  If so where?
>>>
>>
>> I think the docs are acceptable as-is - especially given the numerous
>> cross-references to that section.  If anything i’d maybe call out that most
>> non-result returning commands are actually not parameterized in “42.2.5
>> Executing a Command with No Result” just before the link to 42.11.1
>>
>>
> Concretely, as attached.
>
> I found the comment about the 9.0 behavior to be unnecessary at this point
> and so removed it.
>
> The wording in the PL/SQL conversion comments is a bit out-of-scope but
> took a look at it too as it also redirects the user to the variable
> discussion and I looked at all 4 cross-references.
>
> David J.
>
>

^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2020-10-21 15:23 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-06-30 16:20 BUG #16519: SET SESSION ROLE in plpgsql requires string literal. PG Bug reporting form <noreply@postgresql.org>
2020-06-30 16:39 ` David G. Johnston <david.g.johnston@gmail.com>
2020-06-30 17:18   ` Tom Lane <tgl@sss.pgh.pa.us>
2020-06-30 17:50     ` David G. Johnston <david.g.johnston@gmail.com>
2020-07-01 09:26       ` Marianne B. Wiese <mbwiese@gmail.com>
2020-10-02 20:11       ` David G. Johnston <david.g.johnston@gmail.com>
2020-10-21 15:23         ` David G. Johnston <david.g.johnston@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox