Augmenter Session Variables

Some of the functionality implemented by Augmenter will require session variables.

For example, by default the auditing and history table functions can only know the database username and the IP address of the database client making changes. However, many web applications use a single database user to connect to the database and have a different application-level username that indicates the user initiating the transaction. Similarly, the web server will know the IP address of the client browser.

Auditing will be better if the database client (i.e. the web application) can make this information available to the database to be stored in session variables. That way the audit functions, e.g. get_audit_user() can return the SESSION_USER by default, but can also access the application-level username if it has been set by the application.

Normally a web application will set this information by calling a registration function immediately after connecting to the database.

The implementation of session variables used by Augmenter relies on a custom variable class being available. For Postgresql 9.2+ installations no additional configuration is necessary. For Postgresql 9.1 or earlier, postgresql.conf must be edited to included augmenter in the list of custom_variable_classes as described in Customized Options.

dbaugment will check that the augmenter configuration variable is available and will display an appropriate error message if it is not.

The following plpgsql functions are required to implement session variables and will be generated automatically by dbaugment when required:

CREATE OR REPLACE FUNCTION aug_set_session_variable(var_name character varying, var_value character varying)
  RETURNS void AS
$BODY$
  BEGIN
    PERFORM pg_catalog.set_config('augmenter.' || var_name, var_value, false);
  END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE OR REPLACE FUNCTION aug_get_session_variable(var_name character varying)
  RETURNS character varying AS
$BODY$
  BEGIN
    RETURN pg_catalog.current_setting('augmenter.' || var_name);
  END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE OR REPLACE FUNCTION aug_get_session_variable(var_name character varying, default_value character varying)
  RETURNS character varying AS
$BODY$
  BEGIN
    RETURN pg_catalog.current_setting('augmenter.' || var_name);
  EXCEPTION
    WHEN undefined_object THEN
      RETURN default_value;
  END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

An example of a function that could be used to register application-level details

CREATE OR REPLACE FUNCTION aug_set_context(controller_name character varying, action_name character varying, ip_address character varying, username character varying, sessionid character varying)
  RETURNS void AS
$BODY$
BEGIN
  PERFORM aug_set_session_variable('audit_controller', controller_name);
  PERFORM aug_set_session_variable('audit_action', action_name);
  PERFORM aug_set_session_variable('audit_ip_address', ip_address);
  PERFORM aug_set_session_variable('audit_user', username);
  PERFORM aug_set_session_variable('audit_session', sessionid);
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Then a web application can call:

BEGIN
  PERFORM aug_set_context('example_controller', 'example_action', '10.11.12.134', 'real_name', 'app_session_id');
END;

And the correct details will be picked up the the auditing and history functions.

Note that while dbaugment will use a custom variable class by default, other approaches to session variables are possible. In the event that a different approach is required, suitable functions called aug_set_session_variable and aug_get_session_variable (together with any specified schema and prefix) can be created and the dbaugment code will use those instead. Other common approaches include python, tcl or perl global variables and temporary tables.

For information on various approaches to session variables see:

http://blog.xzion.net/2011/02/04/postgres-guc-as-session-transaction-variables/‎

http://www.depesz.com/index.php/2009/08/20/getting-session-variables-without-touching-postgresql-conf/

http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#Any_other_session_variables

http://www.postgresql.org/docs/8.4/static/runtime-config-custom.html

http://www.postgresql.org/docs/8.4/static/plperl-global.html

http://translate.google.pl/translate?prev=hp&hl=pl&js=y&u=http%3A%2F%2Fwww.truesolutions.pl%2Fblog%2Fbenchmark-procedur-w-postgresql&sl=pl&tl=en&history_state0=