How to Use Session-Related Parameters in PostgreSQL

Postgres provides hundreds of configuration parameters that control different aspects of server and session behaviour. Among them, session-related parameters influence how an individual database session behaves without necessarily affecting other connected sessions. Understanding these parameters is useful not only for database administrators but also for developers who want to learn how postgres internally manages configuration settings.

In postgres, there are mainly three parameters related to the session.

Let’s look one by one in more detail.

1. session authorization

Check the current value like this.

show session authorization ;

Result:

 session_authorization 
-----------------------
 postgres
(1 row)

Check the metadata from pg_settings like this.

select * from pg_settings where name = 'session_authorization';

Result:

(0 rows)

This parameter is hidden because the Postgres source code uses the flag named GUC_NO_SHOW_ALL.

This is from the file named guc_parameters.dat in the Postgres source code.

Path:

src/backend/utils/misc

Check this parameter definition in the guc_parameters.dat file

Note: In Postgres version 19, you can find this definition in the file guc_parameters.dat.

In postgres version < 19, these definitions are placed in guc_tables.c.

# Not for general use --- used by SET SESSION AUTHORIZATION
{ name => 'session_authorization', type => 'string', context => 'PGC_USERSET', group => 'UNGROUPED',
  short_desc => 'Sets the session user name.',
  flags => 'GUC_IS_NAME | GUC_REPORT | GUC_NO_SHOW_ALL | GUC_NO_RESET_ALL | GUC_NOT_IN_SAMPLE | GUC_DISALLOW_IN_FILE | GUC_NOT_WHILE_SEC_REST',
  variable => 'session_authorization_string',
  boot_val => 'NULL',
  check_hook => 'check_session_authorization',
  assign_hook => 'assign_session_authorization',
},

In postgres, each configuration parameter has a context.

 select distinct(context) from pg_settings ;

Result:

      context      
-------------------
 postmaster
 superuser-backend
 user
 internal
 backend
 sighup
 superuser
(7 rows)

The context values in pg_settings are the SQL-friendly names that correspond to the internal PGC_* constants.

PGC_USERSET - This parameter context is the least restrictive.

It means:

• Any user can change the parameter.
• It can be changed at any time during a session.
• No server restart or reload is required.

The default value for this parameter is null.

This parameter has both hooks named assign hooks and check hooks.

The check_hook is mainly used to verify that the new value is valid before accepting it.

The assign_hook’s main purpose is to apply the accepted value to postgresql's internal state.

This parameter shows the name of the role that originally authenticated to the postgres server for the current session.

When you try to log in to psql with a different role, it looks like this:

psql -U cybrosys
psql 
Type "help" for help.
cybrosys=# show session authorization ;
 session_authorization 
-----------------------
 cybrosys
(1 row)

2.session_preload_libraries

Check the value of this parameter.

show session_preload_libraries ;

Result:

 session_preload_libraries 
---------------------------
 
(1 row)

Check the metadata from pg_settings like this.

select * from pg_settings where name = 'session_preload_libraries';

Result:

-[ RECORD 1 ]---+-------------------------------------------------------
name            | session_preload_libraries
setting         | 
unit            | 
category        | Client Connection Defaults / Shared Library Preloading
short_desc      | Lists shared libraries to preload into each backend.
extra_desc      | 
context         | superuser
vartype         | string
source          | default
min_val         | 
max_val         | 
enumvals        | 
boot_val        | 
reset_val       | 
sourcefile      | 
sourceline      | 
pending_restart | f

Check the parameter definition in the file named guc_parameters.dat from the postgres source code.

{ name => 'session_preload_libraries', type => 'string', context => 'PGC_SUSET', group => 'CLIENT_CONN_PRELOAD',
  short_desc => 'Lists shared libraries to preload into each backend.',
  flags => 'GUC_LIST_INPUT | GUC_LIST_QUOTE | GUC_SUPERUSER_ONLY',
  variable => 'session_preload_libraries_string',
  boot_val => '""',
},

This parameter is mainly used to automatically load one or more postgres shared library extensions into every new session (backend process).

• name => 'session_preload_libraries' – specifies the name of this parameter.
• type => 'string' - specifies the data type of this parameter.
• context => 'PGC_SUSET' - context of this parameter. It decides who can change this parameter value and when.
• group => 'CLIENT_CONN_PRELOAD', - specifies the category of this parameter.
• short_desc => 'Lists shared libraries to preload into each backend.' - short description of this parameter we can see from pg_settings.

Flags define additional behaviour for the parameter.

• GUC_LIST_INPUT: The value is interpreted as a comma-separated list.
• GUC_LIST_QUOTE: This flag allows proper parsing of list elements that require quoting.
• GUC_SUPERUSER_ONLY: This flag allows only superusers to assign a value to this parameter.

We can set the value for this parameter session-wise like this.

SET session_preload_libraries = 'auto_explain';

3.session_replication_role

Check the current value of this parameter.

show session_replication_role ;

Result:

 session_replication_role 
--------------------------
 origin
(1 row)

Check the metadata from pg_settings like this.

select * from pg_settings where name = 'session_replication_role';

Result:

-[ RECORD 1 ]---+------------------------------------------------------------
name            | session_replication_role
setting         | origin
unit            | 
category        | Client Connection Defaults / Statement Behavior
short_desc      | Sets the session's behavior for triggers and rewrite rules.
extra_desc      | 
context         | superuser
vartype         | enum
source          | default
min_val         | 
max_val         | 
enumvals        | {origin,replica,local}
boot_val        | origin
reset_val       | origin
sourcefile      | 
sourceline      | 
pending_restart | f

Check this parameter definition in the file named guc_parameters.dat from the postgres source code.

{ name => 'session_replication_role', type => 'enum', context => 'PGC_SUSET', group => 'CLIENT_CONN_STATEMENT',
  short_desc => 'Sets the session\'s behavior for triggers and rewrite rules.',
  variable => 'SessionReplicationRole',
  boot_val => 'SESSION_REPLICATION_ROLE_ORIGIN',
  options => 'session_replication_role_options',
  assign_hook => 'assign_session_replication_role',
},

This parameter controls how the current session treats triggers and rewrite rules. It is primarily intended for logical replication, data loading, and maintenance operations.

Here, the data type of this parameter is enum.

Check the structure of this enum named session_replication_role_options in the Postgres source code.

static const struct config_enum_entry session_replication_role_options[] = {
	{"origin", SESSION_REPLICATION_ROLE_ORIGIN, false},
	{"replica", SESSION_REPLICATION_ROLE_REPLICA, false},
	{"local", SESSION_REPLICATION_ROLE_LOCAL, false},
	{NULL, 0, false}
};

The session_replication_role_options array defines the valid values that can be assigned to the session_replication_role configuration parameter. It is declared as a static const array of config_enum_entry structures, meaning it is accessible only within the current source file and remains read-only during execution.

Each entry in the array consists of three fields:

the user-visible string (such as "origin", "replica", or "local"), the corresponding internal enum constant (SESSION_REPLICATION_ROLE_ORIGIN, SESSION_REPLICATION_ROLE_REPLICA, or SESSION_REPLICATION_ROLE_LOCAL), and a Boolean hidden flag.

Since the hidden flag is set to false for all entries, all three values are visible to users and can be assigned to the parameter.

session_authorization, session_preload_libraries, and session_replication_role serve very different purposes in postgres, and they all demonstrate how Postgres implements configuration parameters through its generalized unified configuration (GUC) framework. By examining each parameter in pg_settings and tracing its source code definition, we can understand not only what the parameter does but also who is allowed to modify it, when it can be changed, how postgres validates new values through check hooks, applies them using assign hooks, and enforces additional behaviour through GUC flags.`