PostgreSql SET configuration_parameter from Stored Procedure

Question

I am implementing Row Level Security (RLS) on a postgres table. To use a single user, I am setting a configuaration_parameter for each session that maps to the row's identity and it works. I can test this using SQL but since I am using Mulesoft for the application, I don't have control over setting up connections and setting the parameter then. I tried to create a stored procedure where I try to set the value in the beginning but postgres doesn't like it. The docs don't share whether I can implement it in the SP or not so wondering if you guys know for sure so I can look at alternate solutions.

https://www.postgresql.org/docs/12/sql-set.html

The below does not work if I uncomment the line SET doc.provider=$1;

CREATE OR REPLACE PROCEDURE getParticipants(text)
LANGUAGE plpgsql
AS $$
    BEGIN
    --SET doc.provider=$1;

    SELECT * FROM disclosure.participantsxref;

    END;
$$;

What is the error you get?

Adrian Klaver
– Adrian Klaver

2020-06-15 20:33:30 +00:00
Commented Jun 15, 2020 at 20:33 — Adrian Klaver
– Adrian Klaver, Commented Jun 15, 2020 at 20:33

Pavel Stehule · Accepted Answer · 2020-06-15 20:37:03Z

2

Statement SET doesn't allow parametrization.Instead you can use a function set_config:

CREATE OR REPLACE FUNCTION foo(text)
RETURNS SETOF disclosure.participantsxref AS $$
BEGIN
  PERFORM set_config('doc.provider', $1, true);
  RETURN QUERY SELECT * FROM disclosure.participantsxref;
END $$
LANGUAGE plpgsql;

In your example, there is another issue - PostgreSQL's procedures cannot to returns tables - if you want to return table, then you have to use function. Functions can return tables with statement RETURN QUERY.

answered Jun 15, 2020 at 20:37

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

pifor · Accepted Answer · 2020-06-15 20:42:21Z

1

You can try to use dynamic SQL :

create or replace procedure myproc ()
as $$
begin 
    execute format('set lc_numeric=%L', 'fr_FR') ;
end;
$$ 
language 'plpgsql';
CREATE PROCEDURE

show lc_numeric;
 lc_numeric  
-------------
 en_US.UTF-8
(1 row)

call myproc();
CALL

show lc_numeric;
 lc_numeric 
------------
 fr_FR
(1 row)

answered Jun 15, 2020 at 20:42

pifor

7,9722 gold badges11 silver badges18 bronze badges

Collectives™ on Stack Overflow

PostgreSql SET configuration_parameter from Stored Procedure

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related