To work with databases, PostgreSQL provides a hierarchy of users with different access rights. In this article, we will discuss how to create and modify them, and explain how PostgreSQL relates the concepts of users, groups, and roles.
How to create a user in PostgreSQL
There are two main ways to create PostgreSQL users: from the console using the CREATE USER command or from the pgAdmin GUI using an SQL query. Below we'll take a closer look at each option.
However, to avoid giving anyone unnecessary access rights and endangering your business, we advise you to contact DB-serv to create users: our engineers specialize in working with PostgreSQL and can guarantee the protection of your data.
Create user
Use the psql terminal program to open a command line. In it, run one of the following commands, where name and pass are changed to the desired values:
● To create a superuser:
CREATE USER name WITH PASSWORD 'pass' SUPERUSER;
● To create a regular user:
CREATE USER name WITH PASSWORD 'pass';
● To create a user with additional parameters (for example, a limit on the number of simultaneous connections to the database):
CREATE USER name WITH PASSWORD 'pass' CONNECTION LIMIT 5;
In graphical user interface
There are many GUI programs for PostgreSQL, but the most common is pgAdmin. Here is the algorithm for creating a user:
1.In pgAdmin connect to the database, go to the Login / Group Roles section, call the context menu and select Create - Login / Group Role.
2.In the window that opens, in the General tab, enter the user's name and, if desired, a description in the Comments field.
3.In the Definition tab, specify a password and, optionally, its expiry date.
4.In the Privileges tab, set the necessary set of rights for the user with the help of toggle switches: the ability to log in, create other roles and bases, etc.
5.In the Membership tab, assign the necessary roles to the user, for example, the default PostgreSQL role pg_monitor, which allows you to view various tables and views related to database monitoring.
6.The SQL tab contains SQL commands responsible for applying the settings made in the previous tabs. You only need to click Save, after which the user will be created and available in the Login / Group Roles section.
Roles: users and their groups in PostgreSQL
In modern versions of PostgreSQL, the concept of role includes users and their groups. Both CREATE ROLE and CREATE USER commands can be used to create roles.
✅ CREATE ROLE is usually executed when you need to create a role with Read Only rights - without the ability to connect to the PostgreSQL server and make changes to the database.
✅ CREATE USER, on the other hand, is more often performed when creating a role with the ability to log in and perform operations in the database.
How to change PostgreSQL user
Changing users in PostgreSQL involves various actions such as changing the password, deleting users, and granting them additional privileges.
Password change
The password can be changed using one of three options:
➡️ By command =# ALTER USER postgres PASSWORD 'password'
➡️ By requesting the password: =# \password postgres
➡️ From the Linux console: sudo -u postgres psql -U postgres -d postgres -c "ALTER USER postgres PASSWORD 'password'"
Deleting PostgreSQL users
A PostgreSQL user can be deleted and de-provisioned by executing certain commands.
Deletion: =# DROP USER username;
Revocation: database1=# REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM "username";
H3: Granting of special rights
In addition to granting superuser permissions to ALL PRIVILEGES in PostgreSQL, you can grant permissions for specific operations and tables.
To grant permission to retrieve, update, and add data, execute: =# GRANT SELECT, UPDATE, INSERT ON ALL TABLES IN SCHEMA public TO "username";
To grant a set of permissions for a specific table, execute: "database_name" =# GRANT ALL PRIVILEGES ON "table_nsme" TO "username";
Backup account
One of the backup rules is to connect to the database from a user with minimal access.
1.Create a special role for this purpose with the command: =# CREATE USER "username" WITH PASSWORD "passwd";
2.Then grant permissions to connect to the database: =# GRANT CONNECT ON DATABASE database TO username;
3.Connect to the database: =# \c database
4.Grant public permissions to user bkpuser using the command =# GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO bkpuser; By default, PostgreSQL has a public schema; if you plan to use a different one, change this value in the command.
Summary
We would like to note once again that in PostgreSQL you can create users both from the console and using the pgAdmin utility. Both users and their groups in modern DBMS versions are united by the concept of roles. For certain tasks, for example, backup, it is necessary to create specific user roles with certain access rights.
