How to create user in PostgreSQL

I’ve created hundreds of database users across various environments while working with PostgreSQL. In this comprehensive guide, I’ll walk you through everything you need to know about how to create a user in PostgreSQL.

How to create user in PostgreSQL

Let’s discuss each approach individually.

Approach-1 Creating Users via GUI Tools

To create user in PostgreSQL, follow the steps below.

1. Open pgAdmin and connect to your PostgreSQL server.
2. Right-click on “Login/Group Roles”.
3. Select “Create” then “Login/Group Role” as shown in the screenshot below.

4. Fill in the required details in the following tabs:

• General: Enter the username

• Definition: Set password and expiration

• Privileges: Configure user permissions

• Membership: Assign to existing groups

Finally, click on the Save button.

Check out the screenshot below, which shows the user has been created successfully.

Approach-2 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-creatorole: The new user doesn’t have 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.

Several arguments can be used with the createuser command. For more information about the arguments, refer to the official PostgreSQL documentation.

Let’s take an example and learn 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 picture below.

To view the newly created user, log in to the PostgreSQL psql prompt with the specific user.

psql -U postgres

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

\du

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

Now, take one more example and create a user with the same roles as an existing user, so execute the command below.

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 roles of the user ‘postgres’ because you used the option ‘–role=postgres’ in the command.

Approach-3 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 steps below:

Open the command prompt on your computer, and type the following 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 permissions that are 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).

Based on the above output, the type of permission to be assigned to the new user depends on you.

To view the user, log in to the psql command prompt and run the following command.

\du

In the above output, you can see the list of role attributes assigned 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 createuser command with different arguments that can be used with this command, and also learned how to create a new user using the interactive mode argument.

You may like to read:

• How To Delete User In PostgreSQL
• How to Set PostgreSQL User Password
• How to connect to PostgreSQL database [Complete tutorial]
• PostgreSQL list users – Helpful Guide