Use SYS_CONTEXT to return parameters for the current namespace - PolarDB

When you migrate applications from Oracle or need to implement fine-grained auditing and application tracking at the database level, the SYS_CONTEXT function provides an Oracle-compatible interface. You can use this function to retrieve context information about the current session, such as user, network, and application details. This information supports advanced features such as security policies, performance diagnostics, and business tracking.

Core example

The following example shows how to query key information for the current session in a single query. This information includes the database name, session user, client IP address, and application module. This example demonstrates the core usage of the function.

SELECT
  SYS_CONTEXT('USERENV', 'DB_NAME') AS db_name,
  SYS_CONTEXT('USERENV', 'SESSION_USER') AS session_user,
  SYS_CONTEXT('USERENV', 'IP_ADDRESS') AS client_ip,
  SYS_CONTEXT('USERENV', 'MODULE') AS app_module;

The following is an example of the expected output. The actual output depends on your session environment.

 db_name | session_user |    client_ip    | app_module 
---------+--------------+-----------------+------------
 testdb  | dev_user     | 172.xx.xx.xx/xx |

Applicability

This feature is available for PolarDB for PostgreSQL (Compatible with Oracle) clusters with a minor engine version of 2.0.14.18.37.0 or later.

Note

You can view the minor engine version in the console or by running the SHOW polardb_version; statement. If your cluster does not meet the version requirement, upgrade the minor engine version.

Syntax

SYS_CONTEXT(namespace VARCHAR2, attribute VARCHAR2) RETURN VARCHAR2

Parameters

namespace: The namespace. Currently, only 'USERENV' is supported.
attribute: The name of the attribute to retrieve.

Return value

The attribute value of the VARCHAR2 type.

Scenarios

Configure and track application information

In complex applications, it is important to track which module and operation triggers a specific database behavior. You can use SYS_CONTEXT with the DBMS_APPLICATION_INFO package to set and retrieve the application context. This lets you create a complete tracking chain.

Goal: Set the application module and operation information for the current database session so that it can be used for later auditing or performance analysis.

Set the application context information.
After your application code connects to the database, you can call a procedure in the DBMS_APPLICATION_INFO package to set the module and operation for the current session.
```
-- Assume this is a backend service that processes orders
CALL DBMS_APPLICATION_INFO.SET_MODULE('OrderProcessing', 'CreateNewOrder');
```
Query the context information in the database.
After you set the information, you can use SYS_CONTEXT to retrieve it from anywhere in the database, for example, within a trigger or a logging function.
```
SELECT
  SYS_CONTEXT('USERENV', 'MODULE') AS current_module,
  SYS_CONTEXT('USERENV', 'ACTION') AS current_action;
```
The expected output is as follows:
```
 current_module  | current_action 
-----------------+----------------
 OrderProcessing | CreateNewOrder
```

Implement an operational audit in a trigger

SYS_CONTEXT is especially useful for automated audits. You can capture the operator's environment information within a trigger and record it in an audit log. This eliminates the need for the application layer to pass extra parameters.

Goal: When a record in the employees table is updated, automatically record the operator's operating system user, client IP address, and session ID in the audit_log table.

Create the target table and insert sample data.

Create the employees table and insert a sample record for testing.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name TEXT,
    salary NUMERIC
);
INSERT INTO employees VALUES (101, 'test_user', 50000);

Create the audit log table.

This table stores the audit information.

CREATE TABLE audit_log (
    log_id SERIAL PRIMARY KEY,
    table_name TEXT NOT NULL,
    os_user TEXT,
    client_ip TEXT,
    session_id TEXT,
    change_time TIMESTAMPTZ DEFAULT NOW()
);

Create the audit trigger function.

This function retrieves context information from the USERENV namespace and inserts it into the log table.

CREATE OR REPLACE FUNCTION fn_audit_employees_update()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO audit_log (table_name, os_user, client_ip, session_id)
    VALUES (
        TG_TABLE_NAME,
        SYS_CONTEXT('USERENV', 'OS_USER'),
        SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
        SYS_CONTEXT('USERENV', 'SESSIONID')
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Attach the trigger to the target table.

This trigger will execute after each update on the employees table.

CREATE TRIGGER trg_audit_employees_update
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION fn_audit_employees_update();

Verify the audit feature.

Perform an update operation, and then query the audit log table.

-- Simulate an update
UPDATE employees SET salary = salary * 1.1 WHERE employee_id = 101;

Query the audit_log table to verify that a new record was automatically added. This record contains the environment information of the user who performed the update.

SELECT * FROM audit_log;

The expected output is as follows:

 log_id | table_name | os_user  |    client_ip        | session_id |          change_time           
--------+------------+----------+---------------------+------------+--------------------------------
      1 | employees  | postgres | 172.xxx.xxx.xxx/xxx | 30         | 2025-10-17 07:41:50.666905 +00

Attribute details

The SYS_CONTEXT function retrieves various types of session information using different parameters. Attribute names are not case-sensitive.

User and session information

Attribute name	Description	Example
`CURRENT_USER`	The current effective user.	`SELECT SYS_CONTEXT('USERENV', 'CURRENT_USER');`
`SESSION_USER`	The initial user of the session. This is the username used for authentication when the client first connects. It remains unchanged throughout the session.	`SELECT SYS_CONTEXT('USERENV', 'SESSION_USER');`
`CURRENT_USERID`	The ID of the current effective user.	`SELECT SYS_CONTEXT('USERENV', 'CURRENT_USERID');`
`SESSION_USERID`	The ID of the initial session user.	`SELECT SYS_CONTEXT('USERENV', 'SESSION_USERID');`
`OS_USER`	The operating system username of the client that is connected to the database server.	`SELECT SYS_CONTEXT('USERENV', 'OS_USER');`
`ISDBA`	Checks if the current user is a database super administrator (Superuser).	`SELECT SYS_CONTEXT('USERENV', 'ISDBA');`

Session identification information

Attribute name	Description	Example
`SESSIONID`	The session identifier. This is usually the process ID (PID) of the database backend. Both attributes have the same function and are aliases for each other.	`SELECT SYS_CONTEXT('USERENV', 'SESSIONID');`
`SID`		`SELECT SYS_CONTEXT('USERENV', 'SESSIONID');`
`CLIENT_IDENTIFIER`	The client identifier.	`SELECT SYS_CONTEXT('USERENV', 'CLIENT_IDENTIFIER');`
`CLIENT_INFO`	The client information.	`SELECT SYS_CONTEXT('USERENV', 'CLIENT_INFO');`

Database and schema information

Attribute name	Description	Example
`DB_NAME`	The name of the currently connected database.	`SELECT SYS_CONTEXT('USERENV', 'DB_NAME');`
`DB_UNIQUE_NAME`	The unique name of the database.	`SELECT SYS_CONTEXT('USERENV', 'DB_UNIQUE_NAME');`
`CURRENT_SCHEMA`	The name of the current default schema.	`SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA');`
`SESSION_SCHEMA`	The session schema name.	`SELECT SYS_CONTEXT('USERENV', 'SESSION_SCHEMA');`
`INSTANCE`	The instance number.	`SELECT SYS_CONTEXT('USERENV', 'INSTANCE');`
`INSTANCE_NAME`	The instance name.	`SELECT SYS_CONTEXT('USERENV', 'INSTANCE_NAME');`

Network and connection information

Attribute name	Description	Example
`IP_ADDRESS`	The IP address of the client.	`SELECT SYS_CONTEXT('USERENV', 'IP_ADDRESS');`
`SERVER_HOST`	The host address of the database server.	`SELECT SYS_CONTEXT('USERENV', 'SERVER_HOST');`
`HOST`	The hostname of the client.	`SELECT SYS_CONTEXT('USERENV', 'HOST');`
`TERMINAL`	The terminal identifier of the client.	`SELECT SYS_CONTEXT('USERENV', 'TERMINAL');`

Application information

Attribute name	Description	Example
`MODULE`	The name of the current application module.	`SELECT SYS_CONTEXT('USERENV', 'MODULE');`
`ACTION`	The name of the currently executing operation.	`SELECT SYS_CONTEXT('USERENV', 'ACTION');`
`DATABASE_ROLE`	The database role.	`SELECT SYS_CONTEXT('USERENV', 'DATABASE_ROLE');`

Language and localization information

Attribute name	Description	Example
`LANG`	The current encoding.	`SELECT SYS_CONTEXT('USERENV', 'LANG');`
`LANGUAGE`	The complete language and locale settings.	`SELECT SYS_CONTEXT('USERENV', 'LANGUAGE');`
`NLS_DATE_FORMAT`	The date display format for the current session.	`SELECT SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT');`

Version information

Attribute name	Description	Example
`SESSION_EDITION_ID`	The session edition ID.	`SELECT SYS_CONTEXT('USERENV', 'SESSION_EDITION_ID');`
`CURRENT_EDITION_ID`	The current edition ID.	`SELECT SYS_CONTEXT('USERENV', 'CURRENT_EDITION_ID');`