While working with PostgreSQL, sometimes the database administrators or superusers need to lock a user for a specific time period. For this purpose, the Postgres “ALTER USER” statement can be used with the “NOLOGIN” clause. Locking the users allows us to prevent the databases without removing the roles or databases. However, a user can be unlocked when needed by executing the “ALTER USER” command with the “LOGIN” attribute.

This post will present a stepwise guide on how to lock or unlock the PostgreSQL user.

How to Lock or Unlock a PostgreSQL User?

This post will let you understand how to:

- Create a New User.
- Lock a Particular User.
- Confirm the User Attributes.
- Unlock a Particular User.

Step 1: Create a New User

Type the CREATE ROLE or USER command to define a new user:

CREATE USER Joseph LOGIN PASSWORD 'xyz';

A new user named “joseph” has been created successfully.

Step 2: Lock the User

Type the following ALTER command to lock the user:

ALTER USER joseph NOLOGIN;

The specified role has been locked. Use the following “psql” command to confirm the user attributes:

\du;

The above snippet shows that the user named “joseph” can’t log in.

Step 3: Login With the Locked User

Let’s re-launch the terminal and try to log in as the locked user, i.e., “Joseph”:

The above snippet shows that we can not log in as a user “joseph”.

Step 4: Unlock the User

Now, type the following ALTER command to unlock the selected user:

ALTER USER joseph LOGIN;

The output snippet proves that the “ALTER USER” command was executed successfully. For confirmation, type the following command:

\du;

The output shows that the “Can’t log in” attribute of the user “joseph” has been removed.

Step 5: Login With the Unlocked User

Now re-open the terminal window, and logged in as the user “joseph”:

The above snippet proves that we have successfully logged in as the user “joseph”.

That was all regarding the locking or unlocking the Postgres users.

Conclusion

In PostgreSQL, the database administrators or superusers can lock or unlock a user for a specific time period. To lock a Postgres user, the “ALTER USER” statement can be used with the “NOLOGIN” clause. While a user can be unlocked when needed by executing the “ALTER USER” command with the “LOGIN” attribute. This article has presented a practical guide on how to lock or unlock a user in Postgres.