CREATE FUNCTION setup_user(TEXT, TEXT) RETURNS BOOLEAN SECURITY DEFINER AS $$
CREATE FUNCTION isUserAuditor() RETURNS BOOLEAN SECURITY DEFINER AS $$
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.
You are not comparing the same perimeter, the setup_user() function is necessary to both approaches for the described use case where a read-only value is needed:
With your approach:
1. CREATE VARIABLE secure_stuff SESSION SCOPE ... 2. REVOKE/GRANT ... on VARIABLE secure_stuff 3. CREATE FUNCTION setup_user(...)
With this approach:
1. CREATE FUNCTION access_secure_stuff(...) 2. REVOKE/GRANT ... on FUNCTION access_secure_stuff 3. CREATE FUNCTION setup_user(...)
The REVOKE/GRANT are basically the same on VARIABLE and on FUNCTION.
So it is not really that different as far as catalog entry count is concerned.
The benefit is that it avoids a special concept and use a more generic one, i.e. basic session variables.
There is big difference - you concept missing any safe point. You have to specify same information more times.
I am sorry, this discussion is in cycle - there is no sense to continue.
Regards
Pavel
The added cost is that a two line function must be written, which does not look like a big issue to implement a pretty special use case.