pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: mbwiese@gmail.com
Subject: BUG #16519: SET SESSION ROLE in plpgsql requires string literal.
Date: Tue, 30 Jun 2020 16:20:03 +0000
Message-ID: <16519-9ef04828d058a319@postgresql.org> (raw)

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;



view thread (7+ messages)  latest in thread

Message-ID: <16519-9ef04828d058a319@postgresql.org>
Permalink:  ../16519-9ef04828d058a319@postgresql.org/
Also on:    postgresql.org/message-id/16519-9ef04828d058a319@postgresql.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-bugs@postgresql.org
  Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, mbwiese@gmail.com
  Subject: Re: BUG #16519: SET SESSION ROLE in plpgsql requires string literal.
  In-Reply-To: <16519-9ef04828d058a319@postgresql.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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