-- 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.
so what is worse - I did one new entry in pg_class and one entry in pg_attributes. You wrote two entries in pg_proc function - more you have to ensure consistency of these functions.