From: | Fabien COELHO <coelho(at)cri(dot)ensmp(dot)fr> |
---|---|
To: | Craig Ringer <craig(at)2ndquadrant(dot)com> |
Cc: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>, Fabrízio de Royes Mello <fabriziomello(at)gmail(dot)com>, Joe Conway <mail(at)joeconway(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: proposal: session server side variables |
Date: | 2016-12-28 15:57:08 |
Message-ID: | alpine.DEB.2.20.1612281643330.4911@lancre |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2016-12-28 16:01:16 | Re: proposal: session server side variables |
Previous Message | Pavel Stehule | 2016-12-28 15:54:10 | Re: proposal: session server side variables |