How to create user in PostgreSQL

In this PostgreSQL tutorial, you will learn how to use createuser in PostgreSQL.

You will know about the command createuser to create a new PostgreSQL user:

• Using command line
• Using interactive mode

How to create user in PostgreSQL using Command Line

PostgreSQL has a utility called createuser to create a new role or user account. The use of the command createuser can only be used by the superusers, also if any user in PostgreSQL has the privilege of CREATE ROLE can create a new user in PostgreSQL.

Privileges like SUPERUSER, BAYPASSRLS, and REPLICATION can only be assigned by superusers. A user with CREATE ROLE permission can not make a new user with that privilege.

The syntax is given below.

createuser [-arguments] username

• createuser: The core utility to make a new user in PostgreSQL.

Below is given the arguments accepted by the createuser command.

• –createdb or -d: It gives permission to a new user to create a database.
• username: A new name of the user that will be created.
• –no-create or -D: It doesn’t allow a new user to create a new database.
• -i or –inherit: Assign all the privileges of the member to which a new user belongs.
• -I or –no-inherit: It doesn’t assign all the privileges of the member to which a new user belongs.
• -l or –login: It is used to assign login permission for the new PostgreSQL user means the new user can log in.
• -L or –no-login: The new PostgreSQL user doesn’t have login permission.
• -r or –createrole: The new PostgreSQL user has permission to create a new role.
• -R or –no-createrole: The new user hasn’t any permission to create the new role.
• -s or –superuser: Using the argument, the new PostgreSQL is going to be the superuser
• -S or –no-superuser: It is applied by default that the new PostgreSQL user is not going to be the superuser.
• –replication: The new PostgreSQL user is going to have the REPLICATION permission.
• –no-replication: The new PostgreSQL user is not going to have the REPLICATION permission.

There are many arguments that can be used with the command createuser, for more information about the arguments, you can refer to the official documentation of PostgreSQL.

Let’s take an example and know how to create a different type of PostgreSQL user.

Open the command prompt on Windows and use the below command to create a user David with default privileges.

createuser -h localhost -U postgres David

After running the above command, enter the new password for the user David as shown in the below picture.

To view the newly created user, login into the PostgreSQL psql prompt with the specific user.

psql -U postgres

Now run the command ‘\du’ in the psql prompt as shown in the below picture.

\du

In the above output, you can see the newly created user ‘David’ but this user doesn’t have any role defined.

Now take one more example and create the user with roles of an already existing user, so execute the below command.

createuser -h localhost -U postgres --role=postgres James

Here in the above command, the option ‘–role=postgres’ means assign roles the same as the user postgres.

Now view the newly created user with the role.

\du

In the above output, you can see that the new user ‘James’ is a member of the user ‘postgres’, which means the user ‘James’ has all the list of roles of the user ‘postgres’ because in the command you have used the option ‘–role=postgres’.

How to create user in PostgreSQL using Interactive Mode

There is a command line argument ‘–interactive’ that is used with the command createuser, it asks for additional information or privileges for the new user.

The syntax is given below.

createuser -h localhost -U postgresql_username --interactive new_username

Let’s take an example by following the below steps:

Open the command prompt on your computer, and type the below command to create a new PostgreSQL user ‘Danny’.

createuser -h localhost -U postgres --interactive Danny

When you run the above command it asks for three permission that is assigned to the new user ‘Danny’, the permissions are Shall the new role be a superuser? (y/n), Shall the new role be allowed to create databases? (y/n) and Shall the new role be allowed to create more new roles? (y/n).

From the above output, what kind of permission should be assigned to the new user, depends upon you.

To view the user, login into psql command prompt and run the below command.

\du

In the above output, you can see the list of roles attributes to the user ‘Danny’ that you assigned while creating this user.

Conclusion

In this PostgreSQL tutorial, you have covered how to create a new user in PostgreSQL using the command createuser command with different arguments that can be used with this command. Also, learned how to create a new user using the interactive mode argument.

You may like to read:

• How to Set PostgreSQL User Password
• How to connect to PostgreSQL database [Complete tutorial]