Re: [HACKERS] proposal: session server side variables

My 0.02€ to try to illustrate a possible private session variable based implementation for this use case:

* Session starts

\c app

* app does SELECT setup_user('user-auth-key-data', 'some-other-blob')

SELECT setup_user('fjshdfjkshfjks', 'jklfsjfklsjfk');

**  setup_user is SECURITY DEFINER to 'appadmin'

-- appadmin did:
CREATE FUNCTION setup_user(TEXT, TEXT)
  RETURNS BOOLEAN SECURITY DEFINER AS $$

**  'appadmin' owns a variable IS_AUDITOR. Other roles have only read
access to it.

  not sure how it is used afterwards... is it the same as USER_IS_AUDITOR?

**  setup_user(...) does whatever expensive/slow work it has to do

    ... checks, updates, whatever

**   setup_user sets USER_IS_AUDITOR var

    -- declare a private session variable
    DECLARE @user_is_auditor BOOLEAN PRIVATE;
    -- set its value to whatever appropriate
    SET @user_is_auditor = ???;
    --- returns its value
    RETURN @user_is_auditor;
$$ LANGUAGE xxx;

* Later RLS policies simply reference USER_IS_AUDITOR var. They don't
need to know the 'user-auth-key-data', or do whatever expensive
processing that it does.

-- appadmin did:
CREATE FUNCTION isUserAuditor()
   RETURNS BOOLEAN SECURITY DEFINER AS $$
   -- say variable is just confirmed if it exists already in session?
   DECLARE @user_is_auditor BOOLEAN PRIVATE;
   RETURN @user_is_auditor;
$$ LANGUAGE xxx;

* Other later triggers, etc, also reference USER_IS_AUDITOR

The variable is not directly referenced, one would have to call isUserAuditor() to access the private session value, but then you can GRANT/REVOKE whatever you want on the access function.

* User cannot make themselves an auditor by SETting USER_IS_AUDITOR

Indeed, the user cannot access the private variable, only appadmin can, and probably root could.

The user could create its own private session variable @user_is_auditor, or a public session variable of the same name. That would be distinct variables which would not influence isUserAuditor which would use its own.

--
Fabien.