Have attribute set to specific value on every query

Question

The setup:

I use psycopg2 to access a (PostgreSQL) database I constructed in SQL. I wanted to have different users, which can only access their own rows in a table. Therefore I added a UserId attribute to every table in the scheme. The value of UserId is available as a variable (let us name it pyUserId) to the program on execution. All my calls to curs.execute() go through one function defined by me.

What I want to achieve:

Is it possible that I centrally set the value for an attribute, like UserId=pyUserId, for each query? Else I had to pass the pyUserId as data on every query I already constructed, which also violates DRY, I suspect.

For example:
SELECT UserName FROM Users WHERE Age < 30 becomes:
SELECT UserName FROM Users WHERE Age < 30 AND UserId = pyUserId
or
INSERT INTO Profiles (Name, Bio, SoAccount) VALUES ('jon', 'blah...', '22656') becomes
INSERT INTO Profiles (Name, Bio, SoAccount, UserId) VALUES ('jon', 'blah...', '22656', pyUserId)

Same for DELETE etc.

What I tried:

• For INSERT it is possible to dynamically ALTER the PostgreSQL DEFAULT constraint. But this would only apply to inserts.
• According to string composition in psycopg2 docs, I can manipulate the SQL query string. But I guess there are a lot of pitfalls, operating on SQL strings.
• However, maybe there is a way to do this by manipulation of the prepared statement or the parse tree.
• I am not sure, if the idea is possible at all. If it is not, please explain why.

Answer 1

Snakecharmer's comment helped me implement the following.

Have an attribute UserId in all tables where specific rows belong to specific users:

CREATE TABLE Users (
    UserId INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_attributes ...

CREATE TABLE some_table (
    UserId INTEGER REFERENCES Users(UserId) 
    DEFAULT current_setting('my_app.CurrentUserId')::INTEGER NOT NULL,
    some_attribute INTEGER, ...

Based on this tutorial: Define a session variable which is set, when a user is authenticated and is above used as DEFAULT:

SQL = "SET my_app.CurrentUserId = %s"
data = (self.__userId, )
cur.execute(SQL, data)

Implement Row-Level Security, to create a policy which only allows access where UserId is equal to the session variable:

DO -- Create a loop over all tables to create a policy and enable it for them
$$
DECLARE
    row record;
BEGIN
    FOR row IN SELECT tablename FROM pg_tables AS t
        WHERE t.schemaname = 'public' 
        AND NOT t.tablename ~* 'exclude|tables|regex'
    LOOP
        EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY;', row.tablename); 
        EXECUTE format('CREATE POLICY isolateUserId ON %I TO some_user 
                            USING (UserId = current_setting(''my_app.CurrentUserId'')::INTEGER);', row.tablename); -- Escape ' !
    END LOOP;
END;
$$;

How it works:

• This works well, because only a few tables do not get RLS, so they can easily be removed in the regex.
• some_user is the one you use in psycopg2 to connect to the database.
• I copied this answer for the loop.
• The way this works is explained well in the linked article. TL;DR: It is made sure that a user always creates items with their own UserId, by the combination of the session variable and default value. Session variable and policy make sure that a user only sees/alters/deletes rows where their own UserId is present.