Thread: BUG #16519: SET SESSION ROLE in plpgsql requires string literal.
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;
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.
"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
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.
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.1David J.
--
Marianne B. Wiese
Reventlowsgade 30
1651 København V
+45 51 92 69 18
Reventlowsgade 30
1651 København V
+45 51 92 69 18
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.
Attachment
I've moved this over to -hackers and the commitfest.
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.1Concretely, 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.