PostgreSQL CREATE USER

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.

Example Create User in PostgreSQL Without any options

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

CREATE USER lini;

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.

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.

Check the users list in psql shell.

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.

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

Conclusion

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