4th gen AMD EPYC coming soon Pre-order now

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

August 23rd, 2023
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.

Run your PostgreSQL deployments in a high-performing and cost-effective open cloud infrastructure. Cherry Servers' bare metal cloud and virtual servers offer automatic scaling, pay-as-you-go pricing, and free 24/7 technical support.

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.

docker run postgres

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

docker ps

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

docker exec

-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:

psql \du

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';

CREATE USER

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

\du

psql \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;

ALTER USER

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

psql \du

Discover how MKC Limited leveraged Cherry Servers' secure cloud infrastructure to enhance data security, reduce cost, and increase system efficiency.

Step 5: Switching to the new user

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

SET ROLE blog;

SET ROLE

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

 CREATE DATABASE cms;

CREATE DATABASE

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.

With over 20 years in IT, Didier has been creating technical documentation for companies, catering to both technical and non-technical audiences. Didier is an expert in Linux system administration, DevOps, cloud computing, cybersecurity, IT consulting, management consulting, technical writing, Diataxis framework, Doc-as-Code, UX Writing, Jamstack, MkDocs, Docker, containers, open-source, SDLC, and Python programming. His hands-on technical expertise, coupled with his strong communication skills, enables him to bridge the gap between developers and end-users. Didier creates user guides, API References, end-user documentation, how-tos, and tutorials. He is an expert in authoring using modern technologies such as Markdown, Mermaid, and static-site generators. Didier also utilizes frameworks and methodologies such as Diaxiatis and Doc-as-code, applying structured writing techniques. Currently, Didier works as a freelance technical writer and documentation consultant, assisting organizations in creating comprehensive and easy-to-understand documentation for their software and web applications. In his previous roles, Didier worked as a system and applications engineer and implemented style guides, tone and voice best practices, and documentation processes that streamline their release cycles. Didier resides in Vacoas, Mauritius.

Cloud VPS - Cheaper Each Month

Start with $9.99 and pay $0.5 less until your price reaches $6 / month.

We use cookies to ensure seamless user experience for our website. Required cookies - technical, functional and analytical - are set automatically. Please accept the use of targeted cookies to ensure the best marketing experience for your user journey. You may revoke your consent at any time through our Cookie Policy.
build: 5a9fc6cd.612