Recording the invoker of a Postgres function that is set to SECURITY DEFINER

Question

Is there a way for a function in Postgres (using 9.4) to find out the user that invoked it if the function is set to SECURITY DEFINER?

The design problem that I have is that I want to do user authentication via my web app (so that I can share a connection pool) but still maintain audit records within the database that reference the authenticated end user from the web app.

The current flow is:

• Users connect to a web app, and it does the authentication to verify that they are who they say they are.
• The web app then connects to Postgres via an app user.
• When any queries are executed on behalf of a user, the web app runs a SET ROLE [username], then the relevant query, then a RESET ROLE before returning the connection to the connection pool.
• All user queries are done via functions. The users have privileges to run the functions but do not have privileges to make changes to tables directly (the web app account has no permissions except for the permission to impersonate certain users). The functions also maintain columns for concepts like "created by", "last updated", etc. by inserting/updating the appropriate values with CURRENT_USER and CURRENT_TIMESTAMP.

The problem that I'm running into is that the CURRENT_USER always returns the function owner, and SESSION_USER always returns the web app account, whereas what I really want is the name of the account that invoked the function.

Is this possible? I'm not seeing anything particularly promising in the documentation. Conversely, how have other people solved this design problem?

Answer 1

You can define a DOMAIN which constrains NAME values to CURRENT_USER:

CREATE DOMAIN whoami AS NAME
CHECK( VALUE = CURRENT_USER )
;

then apply this DOMAIN to a parameter of your function. The parameter can be defined as optional, with a default value of CURRENT_USER (the default value is computed in the context of the caller):

CREATE FUNCTION restricted_area( caller whoami DEFAULT CURRENT_USER )
RETURNS TABLE ( caller NAME, owner NAME )
SECURITY DEFINER
LANGUAGE SQL
AS $SQL$
  SELECT caller, CURRENT_USER;
$SQL$
;

CREATE ROLE restricted_area_owner;
ALTER FUNCTION restricted_area( whoami )
OWNER TO restricted_area_owner
;

CREATE ROLE web_user;
GRANT EXECUTE ON FUNCTION restricted_area( whoami )
TO web_user
;

SET ROLE web_user;
SELECT *
FROM restricted_area()
;

which gives the expected result:

  caller  |         owner         
----------+-----------------------
 web_user | restricted_area_owner
(1 row)

the CURRENT_USER can be also be provided explicitly:

SELECT *
FROM restricted_area('web_user')

  caller  |         owner         
----------+-----------------------
 web_user | restricted_area_owner
(1 row)

but no other value is accepted:

SELECT *
FROM restricted_area('postgres')
;

ERROR:  value for domain whoami violates check constraint "whoami_check"