PostgreSQL users are used to performing various operations in the database starting from the development to an administrator. In PostgreSQL, the database users and the Operating system users are separated and should be unique. The user name should not be starting with pg_. PostgreSQL creates the default superuser Postgres during the installation time and grants all privileges with the grant option. The superuser is capable of creating a new user in the PostgreSQL database or the one who has create role permission. All database users are global and can access the objects across the PostgreSQL cluster.
In this section of the PostgreSQL tutorial, we will see the different ways to create a PostgreSQL user.
1. Create User Using Psql
To create a PostgreSQL Database user, we can use the psql CREATE USER command. It provides various options that we can use per requirement.
The following is the syntax and the option of CREATE USER command.
Syntax:
postgresql=# CREATE USER User_name [ [ WITH ] option [ ... ] ]
The Options are :-
SYSID uid | CREATEDB | NOCREATEDB | CREATEUSER | NOCREATEUSER | IN GROUP groupname [, ...] | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password' | VALID UNTIL 'time'
Now let us see the various ways to create a user by using multiple options.
1.1 Create User Without Password Using Psql
We can create a PostgreSQL DB user without supplying the password. In the following example, we are creating a user named RAVI without providing the password.
Command:
postgresql=# CREATE USER RAVI;
postgresql=# \du+
The user has been created without a password and we can see the list of users using the \du+ command in the below figure.
Output:
1.2 Create User With Password Using Psql
In this section, we will create a user named DEV by supplying the password.
Command:
postgresql=# CREATE USER DEV WITH PASSWORD 'we43Y2';
postgresql=# \du+
In the below output we can see the DEV user is created.
Output:
1.3 Create User With Password Expiry Date Using Psql
In this example, we will create a user named MOHAN by providing the password expiry date.
Command:
postgresql=# CREATE USER MOHAN WITH PASSWORD 'qw63R2' VALID UNTIL '2023-01-01';
postgresql=# \du+
In the attributes section of the below output, we can see the password expiry date is mentioned.
Output:
1.4 Create User With Create Databases Permission Using Psql
In this section, we will create a user named WASI who has the create database permission.
Command:
postgresql=# CREATE USER WASI WITH PASSWORD 'xi8s0R4' CREATEDB;
postgresql=# \du+
We can verify the same from the below Attributes column. We can see that the Create DB permission is granted to user WASI.
Output:
2. Create User Using createuser utility
The PostgreSQL createuser utility is also used to create a new user. It works as a wrapper for the CREATE ROLE SQL command and performs the same operation.
Now let us see the following example to create a database user using the PostgreSQL createuser utility.
2.1 Create User Using createuser Utility
We will use the createuser Utility to create a new user named devan from the Unix command line. We will use the postgres super user to create a new user.
Command:
cloudduggu@ubuntu:~$ createuser -h localhost -p 5432 -U postgres devan
postgresql=# \du+
Once the new user is created we can verify it using the psql command \du+ as shown in the below figure.
Output:
2.2 Create User Using createuser interactive Mode
We can create a new user by using the createuser interactive option. When we use the interactive option, it asks if the user will be granted superuser permission, if the user can create the database and if the user can create new roles.
In the below example we are creating a new user named ram using the interactive option
Command:
cloudduggu@ubuntu:~$ createuser -h localhost -p 5432 -U postgres --interactive ram
postgresql=# \du+
In the below figure, we can see that the new user named ram has been created with create database permission as we selected it from the command prompt.
Output:
We can check all options of createuser utility command by using the createuser --help command.
3. Drop User Using Psql DROP USER Command
The PostgreSQL DROP USER is used to drop an existing user from the PostgreSQL.
In the following section, we will use the DROP USER command to drop an existing user named dev.
Command:
postgresql=# \du+
postgresql=# DROP USER DEV;
postgresql=# \du+
Output:
We can verify from the below figure, that the user DEV has been dropped.
4. Drop User Using dropuser Utility
The PostgreSQL dropuser Utility can also be used to drop an existing user from PostgreSQL.
In the following example, we will drop user ravi from PostgreSQL.
Command:
cloudduggu@ubuntu:~$ dropuser -h localhost -p 5432 -U postgres ravi
cloudduggu@ubuntu:~$ sudo -i -u postgres
postgresql=# \du+
Output:
The user RAVI has been dropped as we can verify from the below output.
