2016-12-28 16:57 GMT+01:00 Fabien COELHO <coe...@cri.ensmp.fr>:
>
> 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.
>
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.
Regards
Pavel
>
> --
> Fabien.
