How to Create a PostgreSQL User

To perform operations with databases in PostgreSQL, users with different access rights are created. This PostgreSQL tutorial will explain how to create users in PostgreSQL using different methods.

You can create a PostgreSQL user in various ways: for example, by entering the CREATE USER (or CREATE ROLE) command in the command line or by executing an SQL query through an administrative interface such as pgAdmin. Let's take a closer look at these methods.

Prerequisites

To follow this guide, you will need PostgreSQL installed on your local machine or a cloud server. 

If you are using Debian or Ubuntu, this tutorial will guide you through the installation. Alternatively, you can run PostgreSQL in a Docker container. 

Creating Users in PostgreSQL with CREATE USER

Let’s see how to create a superuser in Postgres with a password and administrator rights., 

Open the command line using the psql utility.

psql

In psql, enter the CREATE USER statement, replacing name and pass with the actual username and password you wish to use:

CREATE USER name WITH PASSWORD 'pass' SUPERUSER;

Note that you will need an administrator role with privileges to create users to execute this command.

Executing this command will create a user with administrator rights, granting them full control over the database.

To create a regular user, enter the following command, also replacing name and pass with your desired values:

CREATE USER name WITH PASSWORD 'pass';

You can also specify additional parameters can when creating a user, such as connection restrictions or database access rights. This is done as follows:

CREATE USER name WITH PASSWORD 'pass' CONNECTION LIMIT 5;

The CONNECTION LIMIT option specifies the maximum number of simultaneous connections this user can establish. In this case, the user will be limited to 5 simultaneous connections to the database.

Creating a PostgreSQL User through pgAdmin

If you prefer to use an administrative interface such as pgAdmin, the creation of new users is done as follows:

1. Open pgAdmin and connect to the PostgreSQL server.

2. In the left navigation panel, select the database where you want to create a user.

3. Right-click on Login/Group Roles and select Create — Login/Group Role.

4. In the window that appears, fill in the following information:

• In the Name field on the General tab, enter the username.

• In the Password field on the Definition tab, enter the password for the new account. Additional settings such as the limit on the number of simultaneous connections (CONNECTION LIMIT—see the example above) and the expiration date and time of the account can also be configured on this tab. However, if the user is set to login without a password, the value specified in Account expires will not be applied. To set an unlimited number of simultaneous connections, set the CONNECTION LIMIT to -1.

• In the Privileges section, select the necessary privileges. For example, you can activate the Can login? option (by toggling the switch on the right) to allow the user to log in. You can also grant the account superuser rights (Superuser? option).

• On the Membership tab, you can assign users specific roles offered by the system. For example, the pg_monitor role is provided for tracking and diagnosing the database.

5. Review the generated SQL code on the far right tab, then click Save to create the user with the selected parameters.

The user will now be created and displayed in the Login/Group Roles list in pgAdmin.

CREATE USER vs CREATE ROLE

Roles in Postgres represent both individual users (with or without login rights) and entire groups. The commands CREATE ROLE and CREATE USER are used to create roles in the database. They perform the same function, but there is a slight difference in syntax.

The CREATE ROLE command in Postgres is often (but not exclusively) used to create a role without login capability. This means the created role will not be able to connect to the PostgreSQL server and perform operations in the database (adding, modifying, and deleting data). This can be useful if you want to create a PostgreSQL Read Only user, for example:

CREATE ROLE readonly_user;

The CREATE USER command we showed above is often used to create a role with login capability. This means the created user will be able to connect to the PostgreSQL server and perform various operations in the database.

Thus, the CREATE USER command is essentially synonymous with the CREATE ROLE command with the LOGIN option. That is, the commands CREATE USER readonly_user and CREATE ROLE readonly_user WITH LOGIN are equivalent.

That's all: now you know how to create users in various ways through the console and graphical interface and assign them different rights. Happy working!