Alterable subprogram attributes

Configuration parameter

This term denotes parameters like timezone that you can set, within the scope of a single session, and for no longer than the session's duration, with the set statement. You observe the current value with the show statement or the current_setting() built-in function.

You can execute a set statement in the source text of a subprogram's implementation_definition. But you might prefer to make such a setting a property of the subprogram like this:

drop function if exists f() cascade;
create function f()
  returns text
  set timezone = 'America/New_York'
  language sql
as $body$
  select current_setting('timezone');
$body$;

select f();

This is the result:

        f
------------------
 America/New_York

Security

The security attribute determines the identity of the effective role (as is observed with the current_user built-in function) with which SQL issued by a subprogram executes. The allowed values are definer and invoker. The default is invoker.

• A security definer subprogram executes with the privileges of the subprogram's owner. And a security invoker subprogram executes with the privileges of the session_user.

It's important to understand how unqualified names in SQL statements in a subprogram's implementation are resolved. This is explained in the Writing SECURITY DEFINER Functions Safely section of the PostgreSQL documentation.

Try the following demonstration. It connects to the database demo and relies on two roles, u1 (which owns the schema called u1) and u2 (which doesn't need to own a schema).

First connect as u1 and create the artifacts that the demonstration needs:

\c demo u1
set client_min_messages = warning;
grant usage on schema u1 to u2;

drop table if exists u1.t cascade;
drop function if exists u1.security_definer_result(int) cascade;
drop function if exists u1.security_invoker_result(int) cascade;

create table u1.t(k int primary key, v text not null);
insert into  u1.t(k, v) values(42, 'Selected value of "v" in "u1.t"');

create function u1.security_definer_result(k_in in int)
  returns table(z text)
  language plpgsql
  security definer
as $body$
begin
  z := 'session_user: '||session_user;          return next;
  z := 'current_user: '||current_user;          return next;
  z := (select v from u1.t where k = k_in);     return next;
end;
$body$;

grant execute on function security_definer_result(int) to u2;

create function u1.security_invoker_result(k_in in int)
  returns table(z text)
  security invoker
  language plpgsql
as $body$
begin
  z := 'session_user: '||session_user;                               return next;
  z := 'current_user: '||current_user;                               return next;
  z := (select v from u1.t where k = k_in);                          return next;
exception
  when insufficient_privilege then
    z := current_user||' has no privilege to select from u1.t';      return next;
end;
$body$;

grant execute on function security_invoker_result(int) to u2;

Now connect as u2 to see the behavior difference between a subprogram that has security definer and one that has security invoker:

\c demo u2
set client_min_messages = warning;

select u1.security_definer_result(42);
select u1.security_invoker_result(42);

This is the result:

   security_definer_result
---------------------------------
 session_user: u2
 current_user: u1
 Selected value of "v" in "u1.t"

       security_invoker_result
-----------------------------------------
 session_user: u2
 current_user: u2
 u2 has no privilege to select from u1.t