Emulating Oracle’s SYS_CONTEXT in YugabyteDB

If you’ve worked with Oracle, you’ve probably used SYS_CONTEXT to grab runtime environment information—things like the current user, client IP, or host name, without joining catalog tables. It’s a simple but powerful tool for auditing, logging, and debugging.

YugabyteDB (and PostgreSQL) don’t ship with SYS_CONTEXT. But because YugabyteDB speaks Postgres, we can shim in a compatible function and make migrations a little smoother.

The Idea

Oracle’s SYS_CONTEXT(namespace, parameter) returns a single value. The most common namespace is USERENV. For example:

				
					SELECT SYS_CONTEXT('USERENV','CURRENT_USER') FROM dual;

To emulate this, we can create a PL/pgSQL function named sys_context that accepts the same two arguments and returns text. Behind the scenes, we map Oracle’s parameters to PostgreSQL/YugabyteDB equivalents.

The Implementation

Here’s a starter version you can install in your cluster:

				
					CREATE OR REPLACE FUNCTION sys_context(p_namespace text, p_parameter text)
RETURNS text
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
  ns text := upper(p_namespace);
  par text := upper(p_parameter);
  v  text;
BEGIN
  IF ns = 'USERENV' THEN
    /*
      Notes on fallbacks:
        - OS_USER:     custom GUC -> application_name -> current_user
        - CLIENT_*:    custom GUCs -> application_name -> current_user
        - SERVER_HOST: custom GUC -> DNS host(inet_server_addr()) -> server IP
        - IP_ADDRESS:  client IP, "local" if UNIX socket
    */
    CASE par
      -- Users & roles
      WHEN 'SESSION_USER'      THEN v := session_user;
      WHEN 'CURRENT_USER'      THEN v := current_user;
      WHEN 'ISDBA'             THEN v := CASE WHEN pg_is_superuser() THEN 'TRUE' ELSE 'FALSE' END;

      -- Identities (Oracle-like)
      WHEN 'SESSIONID'         THEN v := pg_backend_pid()::text;  -- SID analog
      WHEN 'SID'               THEN v := pg_backend_pid()::text;

      -- Database / schema
      WHEN 'DB_NAME'           THEN v := current_database();
      WHEN 'CURRENT_SCHEMA'    THEN SELECT current_schema INTO v;

      -- Server / network
      WHEN 'SERVER_IP'         THEN v := inet_server_addr()::text;
      WHEN 'SERVER_HOST'       THEN
        v := COALESCE(
               current_setting('yugabyte.server_host', true),     -- optional app-provided canonical name
               NULLIF(host(inet_server_addr()), ''),              -- DNS reverse (may return IP text)
               inet_server_addr()::text
             );
      WHEN 'IP_ADDRESS'        THEN v := COALESCE(inet_client_addr()::text, 'local');
      WHEN 'PORT'              THEN v := inet_client_port()::text;

      -- Locale-ish
      WHEN 'LANGUAGE'          THEN v := current_setting('lc_messages', true);
      WHEN 'DATESTYLE'         THEN v := current_setting('DateStyle',   true);

      -- Client/app telemetry (Oracle analogs)
      WHEN 'CLIENT_IDENTIFIER' THEN
        v := COALESCE(
               current_setting('yugabyte.client_identifier', true),
               current_setting('application_name', true),
               current_user
             );
      WHEN 'MODULE'            THEN
        v := COALESCE(
               current_setting('yugabyte.module', true),
               current_setting('application_name', true)
             );
      WHEN 'ACTION'            THEN
        v := COALESCE(
               current_setting('yugabyte.action', true),
               NULL
             );
      WHEN 'OS_USER'           THEN
        v := COALESCE(
               current_setting('yugabyte.os_user', true),
               current_setting('application_name', true),
               current_user
             );

      -- Timestamps (session start)
      WHEN 'BACKEND_START'     THEN
        SELECT to_char(a.backend_start, 'YYYY-MM-DD"T"HH24:MI:SSOF') INTO v
        FROM pg_stat_activity a
        WHERE a.pid = pg_backend_pid();

      -- Not implemented -> NULL (Oracle does this)
      ELSE
        v := NULL;
    END CASE;

    RETURN v;
  END IF;

  -- Unknown namespace => NULL
  RETURN NULL;
END$$;

Usage Examples

Once created, you can use sys_context almost exactly as in Oracle:

				
					-- Who am I logged in as?
SELECT sys_context('USERENV','CURRENT_USER');

-- What server is this session connected to?
SELECT sys_context('USERENV','SERVER_HOST');

-- Get a compound auditing string
SELECT sys_context('USERENV','OS_USER')
       || '-' ||
       sys_context('USERENV','CURRENT_USER')
       || '-' ||
       sys_context('USERENV','SERVER_HOST');

Example Output:

				
					yugabyte=# -- Who am I logged in as?
yugabyte=# SELECT sys_context('USERENV','CURRENT_USER');
 sys_context
-------------
 yugabyte
(1 row)

yugabyte=# -- What server is this session connected to?
yugabyte=# SELECT sys_context('USERENV','SERVER_HOST');
 sys_context
-------------
 127.0.0.1
(1 row)

yugabyte=# -- Get a compound auditing string
yugabyte=# SELECT sys_context('USERENV','OS_USER')
yugabyte-#        || '-' ||
yugabyte-#        sys_context('USERENV','CURRENT_USER')
yugabyte-#        || '-' ||
yugabyte-#        sys_context('USERENV','SERVER_HOST');
 ?column?
----------

(1 row)

Wait… why was that result of the last query NULL?

Any SYS_CONTEXT value that Oracle used to derive from the client (OS user, client identifier, module, action, etc.) must be explicitly set in YugabyteDB via SET or connection options. Server-derived values (DB name, current user, IP address, server host, session ID, etc.) work automatically.

				
					yugabyte=# SET yugabyte.os_user='appserver1';
SET

yugabyte=# SELECT SYS_CONTEXT('USERENV', 'OS_USER')
yugabyte-#             || '-'
yugabyte-#             || SYS_CONTEXT('USERENV', 'CURRENT_USER')
yugabyte-#             || '-'
yugabyte-#             || SYS_CONTEXT('USERENV', 'SERVER_HOST');
           ?column?
-------------------------------
 appserver1-yugabyte-127.0.0.1
(1 row)

Why it matters?

● Migration: If you’re porting Oracle applications, you don’t need to rewrite every SYS_CONTEXT call—just implement this function.
● Auditing: Store session metadata in tables without messy joins.
● Flexibility: Extend the function to cover more parameters (NLS_*, PROXY_USER, etc.) as needed.

👉 Pro tip: If you’d like a fully expanded version that covers even more Oracle parameters, you can extend this function with additional CASE branches or even hook it into Postgres’ GUC system for custom values.

Have Fun!