A RetroSearch Logo

Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Search Query:

Showing content from https://github.com/pgaudit/set_user below:

pgaudit/set_user: PostgreSQL extension allowing privilege escalation with enhanced logging and control

PostgreSQL set_user Extension Module 
set_user(text rolename) returns text
set_user(text rolename, text token) returns text
set_user_u(text rolename) returns text
reset_user() returns text
reset_user(text token) returns text
set_session_auth(text rolename) returns text

rolename is the role to be transitioned to. token if provided during set_user is saved, and then required to be provided again for reset.

This PostgreSQL extension allows switching users and optional privilege escalation with enhanced logging and control. It provides an additional layer of logging and control when unprivileged users must escalate themselves to superuser or object owner roles in order to perform needed maintenance tasks. Specifically, when an allowed user executes set_user(text) or set_user_u(text), several actions occur:

Only users with EXECUTE permission on set_user_u(text) may escalate to superuser. Additionally, all rules in Superuser Allowlist apply to set_user.superuser_allowlist and set_user_u(text).

Postgres roles calling set_user(text) can only transition to roles listed or included in set_user.nosuperuser_target_allowlist (defaults to all roles). Additionally the logic in Nosuperuser Allowlist applies to current_user when set_user() is invoked.

Additionally, with set_user('rolename','token') the token is stored for the lifetime of the session.

When finished with required actions as rolename, the reset_user() function is executed to restore the original user. At that point, these actions occur:

If set_user, was provided with a token, then reset_user('token') must be called instead of reset_user():

When set_session_auth(text) is called, the effective session and current user is switched to the rolename supplied, irrevocably. Unlike set_user() or set_user_u(), it does not affect logging nor allowed statements. If set_user.exit_on_error is "on" (the default), and any error occurs during execution, a FATAL error is thrown and the backend session exits.

Typical use of the set_user extension is as follows:

GRANT EXECUTE to Functions

In order to make use of the set_user functions, some database roles must be able to execute the functions. Allow these privileges by GRANTing EXECUTE on the appropriate functions to their intended users.

GRANT EXECUTE ON FUNCTION set_user(text) TO dbclient,dbclient2;
GRANT EXECUTE ON FUNCTION set_user(text, text) to dbclient,dbclient2;
GRANT EXECUTE ON FUNCTION set_user_u(text) TO dbadmin;

This example assumes that there are three users of set_user:

  1. dbclient is an unprivileged user that can run as dbclient2 through calls to set_user.
  2. dbclient2 is an unprivileged user that can run as dbclient through calls to set_user.
  3. dbadmin is the privileged (non-superuser) role, which is able to escalate privileges to superuser with Enhanced Logging.
Call set_user to Transition

Transitioning to other roles through use of set_user provides the ability to change the session's current_user.

Transitions can be made to unprivileged users through use of set_user (with optional token, as described above).

SELECT set_user('dbclient2');

Alternatively, transitions can be made to superusers through use of set_user_u:

SELECT set_user_u('postgres');

Note: See rules in Superuser Allowlist for logic around calling set_user_u(text). See Nosuperuser Allowlist for reference logic around calling set_user(text).

Once one or more unprivileged users are able to run set_user_u() in order to escalate their privileges, the superuser account (typically postgres) can be altered to NOLOGIN, preventing any direct database connection by a superuser which would bypass the enhanced logging.

Naturally for this to work as expected, the PostgreSQL cluster must be audited to ensure there are no other PostgreSQL roles existing which are both superuser and can log in. Additionally there must be no unprivileged PostgreSQL roles which have been granted access to one of the existing superuser roles.

set_user.superuser_allowlist Rules and Logic

The following rules govern escalation to superuser via the set_user_u(text) function:

set_user.nosuperuser_target_allowlist Rules and Logic

The following rules govern non-superuser role transitions through use of set_user(text) or set_user(text, text) function (for simplicity, only set_user(text) is used):

Perform Actions With Enhanced Logging

Once a transition has been made, the current session behaves as if it has the privileges of the new current_user. The optional enhanced logging creates an audit trail upon transition to an alternate role, ensuring that any privilege escalation/alteration does not go unmonitored.

This audit trail is tagged with the value of set_user.superuser_audit_tag, such that actions after superuser escalation are easily identifiable.

If set_user() was initially called with a token, the same token must be provided in order to reset back to the previous user.

SELECT set_user('dbclient2', 'some_token_string');
SELECT reset_user('some_token_string');
Blocking ALTER SYSTEM and COPY PROGRAM

Note that for the blocking of ALTER SYSTEM and COPY PROGRAM to work properly, you must include set_user in shared_preload_libraries in postgresql.conf and restart PostgreSQL.

Notes:

If set_user.block_log_statement is set to "off", the log_statement setting is left unchanged.

For the blocking of ALTER SYSTEM and COPY PROGRAM to work properly, you must include set_user in shared_preload_libraries in postgresql.conf and restart PostgreSQL.

Neither set_user(text) nor set_user_u(text) may be executed from within an explicit transaction block.

Typical use of the set_session_auth function is as follows:

GRANT EXECUTE to Functions

In order to make use of the set_session_auth function, some database roles must be able to execute the function. Allow these privileges by GRANTing EXECUTE on the function to their intended users.

GRANT EXECUTE ON FUNCTION set_session_auth(text) TO dbclient,dbclient2;

In its current state, this extension cannot prevent rolename from performing a variety of nefarious or otherwise undesireable actions. However, these actions will be logged providing an audit trail, which could also be used to trigger alerts.

This extension supports PostgreSQL versions 12 and higher. Prior versions of PostgreSQL are supported by prior versions of set_user.

set_user exposes two hooks that may be used to control post-execution behavior for set_user and reset_user.

The following hooks are called (if set) directly before returning from successful calls to set_user and reset_user. These hooks are meant to give other extensions awareness of set_user actions. This is helpful, for instance, to keep track of dynamic user switching within a session.

To avoid order-dependency in shared_preload_libraries, these hooks are registered in the rendezvous hash table of core Postgres. The header defines a utility function for doing all of the necessary setup.

Allows another extension to take action after calls to set_user. This hook takes the username as an argument so that the hook implementation is aware of the username.

Allows another extension to take action after calls to reset_user. This hook does not take any arguments, since the resulting username will always be the session_user.

Follow the instructions below to implement set_user and reset_user post-execution hooks in another extension:

Configuration is described in more detail in the post-execution hooks subsection of the Install documentation.

If another extension implements the post-execution hooks, post_set_user_hook and post_reset_user_hook, set_user must be listed before that extension in shared_preload_libraries. This is due to the way shared_preload_libraries are opened and loaded into memory by Postgres: the hooks need to be loaded into memory before their implementations can access them.

Clone PostgreSQL repository:

$> git clone https://github.com/postgres/postgres.git

Checkout REL_15_STABLE (for example) branch:

$> git checkout REL_15_STABLE

Make PostgreSQL:

$> ./configure
$> make install -s

Change to the contrib directory:

Clone set_user extension:

$> git clone https://github.com/pgaudit/set_user

Change to set_user directory:

Build set_user:

Install set_user:

If an instance of PostgreSQL is already installed, then PGXS can be utilized to build and install set_user. Ensure that PostgreSQL binaries are available via the $PATH environment variable then use the following commands.

$> make USE_PGXS=1
$> make USE_PGXS=1 install

The following bash commands should configure your system to utilize set_user. Replace all paths as appropriate. It may be prudent to visually inspect the files afterward to ensure the changes took place.

Initialize PostgreSQL (if needed): 
$> initdb -D /path/to/data/directory
Create Target Database (if needed): Install set_user functions:

Edit postgresql.conf and add set_user to the shared_preload_libraries line, optionally also changing custom settings as mentioned above.

First edit postgresql.conf in your favorite editor:

$> vi $PGDATA/postgresql.conf

Then add these lines to the end of the file:

# Add set_user to any existing list
shared_preload_libraries = 'set_user'
# The following lines are only required to modify the
# blocking of each respective command if desired
set_user.block_alter_system = off       #defaults to "on"
set_user.block_copy_program = off       #defaults to "on"
set_user.block_log_statement = off      #defaults to "on"
set_user.superuser_allowlist = ''       #defaults to '*'
set_user.nosuperuser_target_allowlist = ''       #defaults to '*'

Finally, restart PostgreSQL (method may vary):

$> service postgresql restart

Install the extension into your database:

psql <database>
CREATE EXTENSION set_user;
Install set_user post-execution hooks:

Ensure that set_user.h is copied to $(includedir).

This can be done automatically upon normal installation:

$> make USE_PGXS=1 install

There is also an explicit make target available to copy the header file to the appropriate directory:

$> make USE_PGXS=1 install-headers

Ensure that the implementing extension adds -I$(includedir) to CPPFLAGS in its Makefile:

# Add -I$(includedir) to CPPFLAGS so the set_user header is included
override CPPFLAGS += -I$(includedir)

Ensure that the implementing extension includes the set_user header file in the appropriate C file:

/* Include set_user hooks in whichever C file implements the hooks */
#include "set_user.h"

Create your set_user hooks and register them in the rendezvous_variable hash:

void _PG_Init(void)
{
	/*
	 * Your _PG_Init code here
	 */

	 register_set_user_hooks(extension_post_set_user, extension_post_reset_user);

	/*
	 * more _PG_Init code
	 */
}

/*
 * extension_post_set_user
 *
 * Entrypoint of the set_user post-exec hook.
 */
static void
extension_post_set_user(void)
{
	/* Some magic */
}

/*
 * extension_post_reset_user
 *
 * Entrypoint of the reset_user post-exec hook.
 */
static void
extension_post_reset_user(void)
{
	/* Some magic */
}

set_user() and related:

#################################
# OS command line, terminal 1
#################################
psql -U postgres <dbname>

---------------------------------
-- psql command line, terminal 1
---------------------------------
SELECT rolname FROM pg_authid WHERE rolsuper and rolcanlogin;
 rolname
----------
 postgres
(1 row)

CREATE EXTENSION set_user;
CREATE USER dba_user;
GRANT EXECUTE ON FUNCTION set_user(text) TO dba_user;
GRANT EXECUTE ON FUNCTION set_user_u(text) TO dba_user;

#################################
# OS command line, terminal 2
#################################
psql -U dba_user <dbname>

---------------------------------
-- psql command line, terminal 2
---------------------------------
SELECT set_user('postgres');
ERROR:  Switching to superuser only allowed for privileged procedure:
'set_user_u'
SELECT set_user_u('postgres');
SELECT CURRENT_USER, SESSION_USER;
 current_user | session_user
--------------+--------------
 postgres     | dba_user
(1 row)

SELECT reset_user();
SELECT CURRENT_USER, SESSION_USER;
 current_user | session_user
--------------+--------------
 dba_user     | dba_user
(1 row)

\q

---------------------------------
-- psql command line, terminal 1
---------------------------------
ALTER USER postgres NOLOGIN;
-- repeat terminal 2 test with dba_user before exiting
\q

#################################
# OS command line, terminal 1
#################################
tail -n 6 <postgres log>
LOG:  Role dba_user transitioning to Superuser Role postgres
STATEMENT:  SELECT set_user_u('postgres');
LOG:  statement: SELECT CURRENT_USER, SESSION_USER;
LOG:  statement: SELECT reset_user();
LOG:  Superuser Role postgres transitioning to Role dba_user
STATEMENT:  SELECT reset_user();

#################################
# OS command line, terminal 2
#################################
psql -U dba_user <dbname>

---------------------------------
-- psql command line, terminal 2
---------------------------------
-- Verify there are no superusers that can login directly
SELECT rolname FROM pg_authid WHERE rolsuper and rolcanlogin;
 rolname
---------
(0 rows)

-- Verify there are no unprivileged roles that can login directly
-- that are granted a superuser role even if it is multiple layers
-- removed
DROP VIEW IF EXISTS roletree;
CREATE OR REPLACE VIEW roletree AS
WITH RECURSIVE
roltree AS (
  SELECT u.rolname AS rolname,
         u.oid AS roloid,
         u.rolcanlogin,
         u.rolsuper,
         '{}'::name[] AS rolparents,
         NULL::oid AS parent_roloid,
         NULL::name AS parent_rolname
  FROM pg_catalog.pg_authid u
  LEFT JOIN pg_catalog.pg_auth_members m on u.oid = m.member
  LEFT JOIN pg_catalog.pg_authid g on m.roleid = g.oid
  WHERE g.oid IS NULL
  UNION ALL
  SELECT u.rolname AS rolname,
         u.oid AS roloid,
         u.rolcanlogin,
         u.rolsuper,
         t.rolparents || g.rolname AS rolparents,
         g.oid AS parent_roloid,
         g.rolname AS parent_rolname
  FROM pg_catalog.pg_authid u
  JOIN pg_catalog.pg_auth_members m on u.oid = m.member
  JOIN pg_catalog.pg_authid g on m.roleid = g.oid
  JOIN roltree t on t.roloid = g.oid
)
SELECT
  r.rolname,
  r.roloid,
  r.rolcanlogin,
  r.rolsuper,
  r.rolparents
FROM roltree r
ORDER BY 1;

-- For example purposes, given this set of roles
SELECT r.rolname, r.rolsuper, r.rolinherit,
  r.rolcreaterole, r.rolcreatedb, r.rolcanlogin,
  r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
        FROM pg_catalog.pg_auth_members m
        JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
        WHERE m.member = r.oid) as memberof
, r.rolreplication
, r.rolbypassrls
FROM pg_catalog.pg_roles r
ORDER BY 1;
                                    List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+------------
 bob       |                                                            | {}
 dba_user  |                                                            | {su}
 joe       |                                                            | {newbs}
 newbs     | Cannot login                                               | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 su        | No inheritance, Cannot login                               | {postgres}

-- This query shows current status is not acceptable
-- 1) postgres can login directly
-- 2) dba_user can login and is able to escalate without using set_user()
SELECT
  ro.rolname,
  ro.roloid,
  ro.rolcanlogin,
  ro.rolsuper,
  ro.rolparents
FROM roletree ro
WHERE (ro.rolcanlogin AND ro.rolsuper)
OR
(
    ro.rolcanlogin AND EXISTS
    (
      SELECT TRUE FROM roletree ri
      WHERE ri.rolname = ANY (ro.rolparents)
      AND ri.rolsuper
    )
);
 rolname  | roloid | rolcanlogin | rolsuper |  rolparents
----------+--------+-------------+----------+---------------
 dba_user |  16387 | t           | f        | {postgres,su}
 postgres |     10 | t           | t        | {}
(2 rows)

-- Fix it
REVOKE postgres FROM su;
ALTER USER postgres NOLOGIN;

-- Rerun the query - shows current status is acceptable
SELECT
  ro.rolname,
  ro.roloid,
  ro.rolcanlogin,
  ro.rolsuper,
  ro.rolparents
FROM roletree ro
WHERE (ro.rolcanlogin AND ro.rolsuper)
OR
(
    ro.rolcanlogin AND EXISTS
    (
      SELECT TRUE FROM roletree ri
      WHERE ri.rolname = ANY (ro.rolparents)
      AND ri.rolsuper
    )
);
 rolname | roloid | rolcanlogin | rolsuper | rolparents
---------+--------+-------------+----------+------------
(0 rows)

set_session_auth():

# psql -U postgres test
psql (15.4)
Type "help" for help.

test=# grant EXECUTE on FUNCTION set_session_auth(text) to dbclient;
\q

# psql -U dbclient test
psql (15.4)
Type "help" for help.

test=> select session_user, current_user, user, current_role;
 session_user | current_user |   user   | current_role
--------------+--------------+----------+--------------
 dbclient     | dbclient     | dbclient | dbclient
(1 row)

test=> select set_session_auth('jeff');
 set_session_auth
------------------
 OK
(1 row)

test=> select session_user, current_user, user, current_role;
 session_user | current_user | user | current_role
--------------+--------------+------+--------------
 jeff         | jeff         | jeff | jeff
(1 row)

test=> -- the role switch is irrevocable
test=> reset role;
RESET
test=> select session_user, current_user, user, current_role;
 session_user | current_user | user | current_role
--------------+--------------+------+--------------
 jeff         | jeff         | jeff | jeff
(1 row)

test=> reset session authorization;
RESET
test=> select session_user, current_user, user, current_role;
 session_user | current_user | user | current_role
--------------+--------------+------+--------------
 jeff         | jeff         | jeff | jeff
(1 row)

test=> set role none;
SET
test=> select session_user, current_user, user, current_role;
 session_user | current_user | user | current_role
--------------+--------------+------+--------------
 jeff         | jeff         | jeff | jeff
(1 row)

Please see the LICENSE file.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4