Re: [HACKERS] proposal: session server side variables
От | Fabien COELHO |
---|---|
Тема | Re: [HACKERS] proposal: session server side variables |
Дата | |
Msg-id | alpine.DEB.2.20.1612281643330.4911@lancre обсуждение исходный текст |
Ответ на | Re: [HACKERS] proposal: session server side variables (Craig Ringer <craig@2ndquadrant.com>) |
Ответы |
Re: [HACKERS] proposal: session server side variables
Re: [HACKERS] proposal: session server side variables |
Список | 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 existsalready 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.
В списке pgsql-hackers по дате отправления: