How To Create User in PostgreSQL

When we work with databases, in this case PostgreSQL. Whether working in a team that has many members who need access to the database or for testing purposes in building new applications, we must be able to create and delete database user who own the data.

In this short tutorial, we’ll cover the basics of creating database users in PostgreSQL. The commands used in this short tutorial, hopefully will make us ready to start creating users, granting database access and building the foundation for future role management.

To create a new user database in PostgreSQL we will have two options, namely :

• Using PSQL command : CREATE USER.
• Using client command-line utility : createuser.

Both options define a new database user account. In order to create users, we need to be a superuser role for the database cluster. Alternatively, any user with the CREATE ROLE privilege is also able to create new users.

Creating Database User in PostgreSQL

1. Using PSQL Command

By using this way, we will create a database user on postgresql via the PSQL shell so we must first login to the PostgreSQL database server instance. Once inside the PSQL shell, then we create a user by using the command: CREATE USER <user_name>.

1.1. Login to database instance by using postgres user and start the interactive terminal with

sudo -u postgres psql

Output :

ramans@infodiginet:~$ sudo su - postgres
postgres@infodiginet:~$ psql
psql (15.0 (Ubuntu 15.0-1.pgdg22.04+1))
Type "help" for help.

postgres=#

1.2. Create database user :

CREATE USER user_name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

postgres=# CREATE USER <user_name>;

Example :

In this example, we will create a user with the name cashier01. First, we will first check the list of users that already exist in the database.

postgres=# \du
                                  List of roles
Role name  |                        Attributes                          | Member of
-----------+------------------------------------------------------------+-----------
postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
root       | Superuser, Create role, Create DB                          | {}

Then create a new user, called as cashier01, with following command line:

postgres=# CREATE USER cashier01;
CREATE ROLE
postgres=# \du
                                   List of roles
Role name  |                        Attributes                          | Member of
-----------+------------------------------------------------------------+-----------
cashier01  |                                                            | {}
postgres   | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
root       | Superuser, Create role, Create DB | {}

List of PostgreSQL database user wihch was created by PSQL above.

2. Using Command Line Client Utility

By using this way, we will use client utility command line : createuser. This option avoids connecting to the PSQL shell. To create a new database user, we will use the following command line:

sudo -u postgres createuser <name>
sudo -u postgres createuser <name> -e
sudo -u postgres createuser --interactive <name>
sudo -u postgres createuser -h localhost -p 5432 -S -D -R -e <name>

Example :

On this scenario, we will create two database users, namely : cashier2, cashier3 and cashier4 by executing following command lines :

ramans@infodiginet:~$ sudo -u postgres createuser cashier02
ramans@infodiginet:~$ sudo -u postgres createuser cashier03 -e
SELECT pg_catalog.set_config('search_path', '', false);
CREATE ROLE cashier03 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;

ramans@infodiginet:~$ sudo -u postgres createuser --interactive cashier04
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) y

ramans@infodiginet:~$ sudo -u postgres createuser -h localhost -p 5432 -S -D -R -e
Password:
SELECT pg_catalog.set_config('search_path', '', false);
CREATE ROLE cashier05 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;

After we try several variations of creating a database user in postgresql, then we will check the user that has been created in our database.

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 cashier01 |                                                            | {}
 cashier02 |                                                            | {}
 cashier03 |                                                            | {}
 cashier04 | Create role                                                | {}
 cashier05 |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 root      | Superuser, Create role, Create DB                          | {}

List of database user ini PostgreSQL which was created using createuser command line.

Conclusion

In this short tutorial, we have learned how to create a user on a PostgreSQL database using PSQL and the command line client utility. Hopefully this simple article can be useful for those in need.