How to Create a User in PostgreSQL | Step-by-Step

With software storing more and more data, database management systems keep adapting to the ongoing demand. PostgreSQL is a powerful and well-known database system that thrives in this field.

This how-to tutorial will teach you how to create a user in PostgreSQL. Additionally, you will learn how to install a PostgreSQL server on Ubuntu using Docker.

#What is PostgreSQL?

PostgreSQL is a free and open-source object-relational database management system (ORDBMS). It is trusted by many large-scale projects for its reliability, robustness, and performance. PostgreSQL is also ACID-compliant. ACID stands for atomicity, consistency, isolation, and durability. Those are transaction properties that are designed to guarantee data validity in the event of power failures or errors.

PostgreSQL also provides powerful features such as:

• storing, indexing, and querying geographic data;
• replication of data;
• robust access-control system;
• full-text search;
• multi-version concurrency control;
• key/value storage.

#Prerequisites

Ensure you have the latest version of Ubuntu installed on your system. Docker:Install Docker or follow Docker's official documentation. Make sure your user is a member of the Docker group. This gives you enough permission to use Docker commands without having to type the administrator password on every execution.

#How to create a user in PostgreSQL: Step-by-step

To create a PostgreSQL user, we will first need to create a PostgreSQL docker container and connect to it to create and set roles for the created user. Everything will be done directly from the docker container.

#Step 1: Install PostgreSQL

PostgreSQL can be installed by using the official PostgreSQL Docker image maintained by the PostgreSQL Docker Community.

Download and launch PostgreSQL using the following command:

docker run --name postgres_db -e POSTGRES_PASSWORD=mysecretpassword -d postgres

Replace mysecretpassword with a generated password for a safer environment:

docker run --name postgres_db -e POSTGRES_PASSWORD=8biQ$rVKnKj#Mck -d postgres

Docker will automatically download the PostgreSQL image and start a container with a default user with superuser rights.

The following command will confirm if the container has started and is running:

docker ps

#Step 2: Connect to PostgreSQL using psql

Now that PostgreSQL has been installed and is running, the connection to the database can be done using psql. psql is an interactive terminal and is the client of the PostgreSQL server.

We will use Docker exec command to run psql in the docker container. The following command runs psql and connects to the database:

docker exec -it postgres_db psql -U postgres

-U postgres here indicates that the connection request is sent as a user postgres.

Now that psql is connected to the PostgreSQL server, the list of users on the database is displayed with the following command:

\du

\du here lists all the users as follows:

By default, the official PostgreSQL Docker image creates the user postgres with Superuser option.

Note: Notice that PostgreSQL uses the term role instead of user. In fact, a role is an entity that can own database objects and have database privileges. A role can be considered a user or group.

#Step 3: Create a user

In PostgreSQL, a user is a role with the LOGIN option. In psql, the command CREATE USER can be used to create the user. CREATE USER is an alias for CREATE ROLE but sets the LOGIN option by default.

The syntax for CREATE USER is as follows:

CREATE USER name [ [ WITH ] option [ ... ] ]

Where option can be:

• SUPERUSER | NOSUPERUSER
• CREATEDB | NOCREATEDB
• CREATEROLE | NOCREATEROLE
• INHERIT | NOINHERIT
• LOGIN | NOLOGIN
• REPLICATION | NOREPLICATION
• BYPASSRLS | NOBYPASSRLS
• CONNECTION LIMIT connlimit
• [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
• VALID UNTIL 'timestamp'
• IN ROLE role_name [, ...]
• IN GROUP role_name [, ...]
• ROLE role_name [, ...]
• ADMIN role_name [, ...]
• USER role_name [, ...]
• SYSID uid

A user blog can be created with the following command:

CREATEUSER blog WITH PASSWORD '*Tybh2Bcox7qeee';

The command \du can then be used in psql to verify if the user blog has been created.

\du

The user blog has been created, but does not have any attributes yet.

#Step 4: Set role attributes

Now that the user blog has been created, the attribute CREATEDB can be set to the entity.

The command ALTER USER is used for that:

ALTER USER blog CREATEDB;

Using \du shows that CREATEDB has been granted to the user blog.

#Step 5: Switching to the new user

To switch to the new user, the command SET ROLE is used as follows:

SET ROLE blog;

As the user blog has the attribute CREATEDB, a database can be created using that role:

 CREATE DATABASE cms;

#Conclusion

This how-to guide covered what PostgreSQL is and its role in the modern Internet. We also showed how to download and install the PostgreSQL Docker image, create a user, and assign attributes to the role. You can find more information in the official PostgreSQL documentation.

Thanks for learning with Cherry Servers! Our open cloud infrastructure gives developers full control, stable workloads, and free technical support 24/7. We offer dedicated servers, VPS, VDS, anonymous hosting, GPU accelerators, customized servers, and more.

Learn more about us here.