PostgreSQL – Create User

To create a user in PostgreSQL, you can use CRATE USER query. It also has many options to grant roles or enable encryption, set password and such for the user.

Syntax to Create User

Following is the syntax of CREATE USER query.

CREATE USER name [ [ WITH ] option [ ... ] ]
 
 where option can be:
     
       SYSID uid 
     | CREATEDB | NOCREATEDB
     | CREATEROLE | NOCREATEROLE
     | IN GROUP groupname [, ...]
     | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
     | VALID UNTIL 'abstime'

We will see the usage of the options using examples.

ADVERTISEMENT

Example – Create User in PostgreSQL (Without any options)

Let us create a user without any options. Just like that.

CREATE USER lini;
PostgreSQL - Create Users

Output

When you list the users from psql shell, the table contains our newly added user. Also, as we have not mentioned any options while creating this user, the List of roles Attributes column is empty for the user.

PostgreSQL - List Users

Example – Create User with Attribute to Create DB

This example shows to CREATE USER with attribute CREATEDB. This means, that this user has the privilege to create databases.

CREATE USER lini WITH CREATEDB;

Run this query in Query Tool.

PostgreSQL - Create User with Option CREATEDB

Check the users list in psql shell.

PostgreSQL - Output of Create User with Option CREATEDB

The user is created with the Create DB under the List of roles.

Note: If you do not mention the option CREATEDB while creating the user, NOCREATEDB will be applied to the user and the user cannot create databases.

Example – Create User with Attribute to Create Roles

We can create users with option to give them privilege to create roles.

CREATE USER lini WITH CREATEROLE;

Note: If you do not mention the option CREATEROLE while creating the user, NOCREATEROLE will be applied to the user and the user cannot create roles.

Example – Create User with multiple Attributes

You can provide multiple options while creating a user.

In the following example, we created user lini with options that enable the user to create database and create users.

CREATE USER lini WITH CREATEDB CREATEROLE;

Run the query in psql shell or Query Tool.

PostgreSQL - Create Users

When you list the user in psql shell, you would see the user created with the provided options.

PostgreSQL - Create Users

Conclusion

In this PostgreSQL Tutorial, we have created a user with different roles or options using CREATE USER query.