2

With postgreSQL 9.5 and +, there is a policy feature that handle row-level access which is great.

My special case is to use this feature to control access to a multi-user postgres DB.

Here is what i'm trying to do:

I have a postgres table (let's say test_data) with a policy associated. The policy is defined as follows:

CREATE POLICY test_data_pol ON test_data FOR ALL USING ('conc' = get_pol())

conc is a column in test_data which contains integer representing id of user that have access to this row of data. get_pol() is a postgres function that retrieve the value of a session parameter (my.foo) set by another function set_pol() as follow:

set_pol()

CREATE OR REPLACE FUNCTION public.set_pol(foo_val integer)
  RETURNS void AS
$BODY$
BEGIN
  EXECUTE format('SET SESSION my.foo TO %I', foo_val) ;
END;
$BODY$

get_pol()

CREATE OR REPLACE FUNCTION public.get_pol()
  RETURNS character varying AS
$BODY$
DECLARE
  foo_val character varying;
BEGIN
  SHOW my.foo INTO foo_val;
  RETURN foo_val;
END;
$BODY$

With that system I can handle the access to the row by executing a SELECT set_pol(value) when I connect to the DB. After setting this Session parameter I can access only the relevant rows.

This works well with psycopg2. for example:

import psycopg2
conn = psycopg2.connect(host=DATABASE['host'],
                        database=DATABASE['dbname'],
                        user=DATABASE['user'],
                        password=DATABASE['password'],
                        port=DATABASE['port'])
cursor = conn.cursor()
cursor.execute("SELECT set_pol(%s)" % conc_value)
cursor.execute("""SELECT * from test_data""")
data = cursor.fetchall()

With that python snippet data only contains the row that are accessible with the relevant conc_value.

Now I try to add VectorLayer in QGIS using pyqgis with this code snippet:

uri = QgsDataSourceURI()
uri.setConnection(host,
                   port,
                   dbname,
                   user,
                   pasword)
uri.setParam('my.foo', 'conc_value')
uri.setDataSource('public', 'test_data', None, None, 'id')
lyr = QgsVectorLayer(uri.uri(), 'test_data', 'postgres')

But the setParam statement does not create the variable as expected in the connection Session and I get this error message:

ERROR:  unrecognized configuration parameter "my.foo"
CONTEXT:  SQL statement "SHOW my.foo"
PL/pgSQL function get_pol() line 5 at SQL statement
.
SQL : SELECT * FROM "public"."test_data" LIMIT 1

which make me think that the setParam function does not do what I need... I need to execute an SET SESSION my.foo TO 'conc_value' but setParam doesn't seems to create the variable the proper way as SHOW my.foo in get_pol() raise an error that it does not know the my.foo parameter...

So my question is:

How can I create the connection with pyqgis to handle the creation of my.foo parameter at the session level?

OR

How can I modify my get_pol() function to be able to access to the my.foo parameter create by the QgsDataSourceURI.setParam() pyqgis function?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.