Before continuing our series on the set_user extension, let’s review what we have gone over so far:

If you haven’t completed the exercises in the previous articles you might want to backtrack. Otherwise, onward!

By default, the postgres user is created as the superuser of your PostgreSQL database. And… everyone knows it. Including those hackers who would like to own you and your data.

You want to prevent the hijacking of postgres and the performing of high jinks of disastrous proportions for you and your clients.

So, much like the best practice of disallowing remote ‘root’ logins on Linux machines, revoking postgres login privileges is advisable.

ALTER USER postgres NOLOGIN;

Once one or more unprivileged users are able to run set user(), the superuser account (normally postgres) can be altered to NOLOGIN, preventing any direct database connection by a superuser which would bypass the enhanced logging.

From the PostgreSQL documentation:

LOGIN / NOLOGIN

These clauses determine whether a role is allowed to log in; that is, whether the role can be given as the initial session authorization name during client connection. A role having the LOGIN attribute can be thought of as a user. Roles without this attribute are useful for managing database privileges, but are not users in the usual sense of the word. If not specified, NOLOGIN is the default, except when CREATE ROLE is invoked through its alternative spelling CREATE USER.

Exercise: Altering Postgres Login Privileges

1. Revoke postgres login privileges

The following exercise assumes you have completed the tasks laid out in the previous posts in our set_user series (linked to above).

Launch psql to connect to postgres database as postgres user:

psql -U postgres postgres 

Running the following query should list postgres as having superuser and login credentials:

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

Revoke the user postgres’ login privileges:

postgres=# ALTER USER postgres NOLOGIN;
    ALTER ROLE

The following query should now show that there are no superuser roles that can login:

postgres=# SELECT rolname FROM pg_authid WHERE rolsuper and rolcanlogin;
     rolname
    ---------
    (0 rows)

Exit out of this psql session:

postgres=# \q

Now, logging into the postgres database as the postgres user should not be permitted:

hollyorr$ psql -U postgres postgres
    psql: FATAL:  role "postgres" is not permitted to log in

Neither should logging into the testdb as the postgres user:

$ psql -U postgres testdb
    psql: FATAL:  role "postgres" is not permitted to log in

2. test the result of NOLOGIN

Connect to the testdb database as dba_user and enter your password when prompted:

$ 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)

Checking to see what roles have superuser and login credentials might result in a permission denied error. This is because by default only the superuser role can query postgresql system files.

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

If you wanted to allow dba_user access to the pg_authid file, you would need to grant permissions when logged in as the posgres user with a GRANT statement like:

GRANT SELECT ON pg_authid TO dba_user;

But set_user_u provides a better option 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
---------
(0 rows)

The result is expected since we revoked postgres user’s ability to login! If we break the where clause into 2 separate queries, this demonstrates that the role postgres is still a superuser:

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

But, because we set the postgres user as NOLOGIN, it no longer has login authorization. Only dba_user can login:

testdb=# SELECT rolname FROM pg_authid WHERE rolcanlogin;
 rolname
----------
 dba_user
(1 row)

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

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

Now rerun 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 re-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 

3. restore LOGIN to postgres

Remember up above, when you tried to login to the testdb as postgres user and you got this?

$ psql -U postgres testdb
        psql: FATAL:  role "postgres" is not permitted to log in

What if you want to give postgres user back the right to login, but you can’t login to postgres user to do that?!

It may feel like an infinite loop of misery, but it’s easy.

First, login to testdb as dba_user and enter password when prompted:

psql -U dba_user testdb

Check to see that current user is dba_user:

testdb=> SELECT CURRENT_USER, SESSION_USER;

     current_user | session_user
    --------------+--------------
     dba_user     | dba_user
    (1 row)

Using our new friend, set_user_u(), switch current user to postgres:

    testdb=> SELECT set_user_u('postgres');

         set_user_u
        ------------
         OK
        (1 row)

Check to see that current user is postgres:

testdb=# SELECT CURRENT_USER, SESSION_USER;

     current_user | session_user
    --------------+--------------
     postgres     | dba_user
    (1 row)

Now you have access to revert the postgres user back to LOGIN privileges:

testdb=# ALTER USER postgres LOGIN;
ALTER ROLE

Exit out of this psql session:

testdb=# \q 

Now try logging into testdb a postgres user:

psql -U postgres testdb
psql (9.6.5)
Type "help" for help.

testdb=#

Voila!

The Takeaway:

So, by setting the postgres role to NOLOGIN, you ensure that anyone conducting tasks with superuser privilege must login via a privileged role. This ensures that all activity conducted as superuser is logged as such and is available in your records as an audit trail.

One caveat from the project’s Readme:

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.

And the villains are foiled again!

Stay tuned for the next post: Understanding Advanced Control Features – Block Settings…