Locking/unlocking a user's Database Access
A superuser can manually lock an existing database user's account with the ALTER USER statement. For example, the following command prevents user Fred from logging in to the database:
=> ALTER USER Fred ACCOUNT LOCK;
=> \c - Fred
FATAL 4974: The user account "Fred" is locked
HINT: Please contact the database administrator
To grant Fred database access, use UNLOCK syntax with the ALTER USER command:
=> ALTER USER Fred ACCOUNT UNLOCK;
=> \c - Fred
You are now connected as user "Fred".
Using CREATE USER to lock an account
Although not as common, you can create a new user with a locked account; for example, you might want to set up an account for a user who doesn't need immediate database access, as in the case of an employee who will join the company at a future date.
=> CREATE USER Bob ACCOUNT UNLOCK;
CREATE USER
CREATE USER also supports UNLOCK syntax; however, UNLOCK is the default, so you don't need to specify the keyword when you create a new user to whom you want to grant immediate database access.
Locking an account automatically
Instead of manually locking an account, a superuser can automate account locking by setting a maximum number of failed login attempts through the CREATE PROFILE statement. See Profiles.