In our last blog post, set_user: Installation and Set-Up, we introduced you to the set_user component, installed the extension in a database, created a new user, and granted it set_user function execution privileges.

If you recall, the set_user documentation states the main goal is to:

grant the EXECUTE privilege to the set_user() and/or set_user_u() function to otherwise unprivileged postgres users. These users can then transition to other roles, possibly escalating themselves to superuser through use of set_user_u(), when needed to perform specific actions.

In an upcoming post we will address the set_user function and what it allows you.

In this post, we are focusing on the set_user_u function and how its magic happens. Wave this magic wand and poof! Your normal worker bee user has become a superuser!

But not so fast! We don’t trust you without some parental controls – enhanced logging – to make sure you aren’t taking advantage of your new powers.

How it works

Syntax

set_user_u(text rolename) returns text
reset_user() returns text

Inputs

“rolename” is the role to be transitioned to.

What happens

When an allowed user executes set user_u(’rolename’), several actions occur:

  • The current effective user becomes rolename.
  • The role transition is logged, with specific notation if rolename is a superuser.
  • log statement setting is set to ’all’, meaning every SQL statement executed while in this state will also get logged.

When finished with required actions as rolename, the reset_user() function is executed and these actions occur:

  • User role is restored the original user.
  • Role transition is logged.
  • log statement setting is set to its original value.
  • Blocked command behaviors return to normal.

So what’s the diff: current vs session user?

The session user is the user that started the session. The current user is the user that has been set via set local session authorization, or via the SECURITY DEFINER attribute of a function. The current user is what is used for permission checks. So, to put it in the context, calling the set_user_u function allows you to set switch the session user (e.g. user_dba) to a superuser as the current user (e.g. postgres).

Now, let’s put this concept to practical use.

Exercise: Making the switch – Clark Kent to Superman

In subsequent postings we will get into the nitty-gritty of logging and advanced control features (block setting and whitelists). So stay tuned for that.

For now, let’s just see what set_user_u does in its default configuration.

Connect to the testdb database as dba_user

When prompted, enter the password you set for dba_user in the last exercise:

psql -U dba_user testdb 

Run the following command to see what your current and session users are:

testdb=# SELECT CURRENT_USER, SESSION_USER;
     current_user | session_user
    --------------+--------------
     dba_user     | dba_user
    (1 row)

Check to see what roles have superuser and login credentials. Permission should be denied because dba_user does not have the credential to access system catalogs (e.g. pg_authid):

testdb=> SELECT rolname FROM pg_authid WHERE rolsuper and rolcanlogin;
    ERROR:  permission denied for relation pg_authid

Now, run the set_user_u function to switch from dba_user to postgres:

testdb=> SELECT set_user_u('postgres');
     set_user_u
    ------------
     OK
    (1 row)

Now rerun the command to verify the switch was made:

testdb=# SELECT CURRENT_USER, SESSION_USER;
     current_user | session_user
    --------------+--------------
     postgres     | dba_user
    (1 row)

Check to see what roles have superuser and login credentials:

testdb=# SELECT rolname FROM pg_authid WHERE rolsuper and rolcanlogin;
     rolname
    ----------
     postgres
    (1 row)

Run the reset_user function to return current user back to dba_user:

testdb=# SELECT reset_user();
     reset_user
    ------------
     OK
    (1 row)

Now rerun the command to verify the switch was reset:

testdb=# SELECT CURRENT_USER, SESSION_USER;
     current_user | session_user
    --------------+--------------
     dba_user     | dba_user
    (1 row)

Now when you run the command to see what roles have superuser and login credentials, you should be unable to select the pg_authid table due to inadequate permissions:

testdb=> SELECT rolname FROM pg_authid WHERE rolsuper and rolcanlogin;
    ERROR:  permission denied for relation pg_authid

Exit out of this psql session:

testdb=# \q 

In this little exercise, we’ve demonstrated how you can switch to and from the postgres user (superuser!) by calling the set_user_u() function.

In our next post, Understanding Logfile Output, we will discuss how set_user creates an audit trail of your superusers to monitor their behavior and determine if they are acting like super villains…