Hello Craig, and happy new year,
> Someone asked me off-list what use cases such a thing would have,
> since it seems not to be spelled out very clearly in this discussion.
> I think we're all assuming knowledge here.
>
> So.
>
> * Session starts
> * app does SELECT setup_user('user-auth-key-data', 'some-other-blob')
> ** setup_user is SECURITY DEFINER to 'appadmin'
> ** 'appadmin' owns a variable IS_AUDITOR. Other roles have only read
> access to it.
> ** setup_user(...) does whatever expensive/slow work it has to do
> ** setup_user sets USER_IS_AUDITOR var
> * 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.
> * Other later triggers, etc, also reference USER_IS_AUDITOR
> * User cannot make themselves an auditor by SETting USER_IS_AUDITOR
>
> That's the general idea.
After giving it some thoughts, I have a question about this use case wrt
to transactions:
What if setup_user() succeeds as a function but the transaction it belongs
to fails for some reason (eg deferred constraints, other operation related
to setting user up but outside of this function fails, there is
replication issue... whatever, a transaction may fail by definition)?
ISTM that the security models requires that USER_IS_AUDITOR is reverted,
so although it is definitely a session variable, it must be transactional
(MVCC) nevertheless.
--
Fabien.