02.10.2023

How To Manage Users in the PostgreSQL?

Introduction

The information systems represented in various sphere of business and our life cycle: health care, production, food growing, entertainment and etc. All of that needed to save, share, pull, get and update data more effectiveness. For that community developed storage for structured data and create different type of DB for distinguish purposes. In that instructions we will consider one of that example PostgreSQL and how to manage user on it.

Cloud Servers from $5 / moIntel Xeon Gold 6254 3.1 GHz CPU, SLA 99,9%, 100 Mbps channel

Management

We have installed the PostgreSQL Database Management System (DBMS) on our machine, our initial step involves switching the user to postgres:

sudo -i -u postgres

psql

Screenshot №1 — Login

For checking user in the DBMS we can use command below:

SELECT * FROM pg_catalog.pg_user;

Screenshot №2 — Show users

Now we can add new account, for that we need to indicate label and password:

CREATE USER serverspace WITH PASSWORD 'mypassword.com';

And for make sure that our command work properly we can highlight caption CREATE ROLE as confirmation and we type command for checking user again:

SELECT * FROM pg_catalog.pg_user;

Screenshot №3 — Create user

In the table represent all parameters of new added user, now we need to GRANT privileges also by the command:

GRANT ALL ON ALL TABLES IN SCHEMA public TO serverspace;

Let's explain syntax of that complex queries:

GRANT ALL — it's a syntax command forces to grant all or administrative privileges for user;
ON ALL TABLES — that means grant privilege for all tables;
IN SCHEMA — for postgreSQL we use organization plan of tables in DB;
TO serverspace — that indicate account for escalate privilege.

Screenshot №4 — Grant privileges

In the same way we can revoke that:

REVOKE ALL ON ALL TABLES IN SCHEMA public FROM serverspace;

Screenshot №5 — Revoke privileges

As you can notice syntax of command remind human-like and intuitive understandable language. Also we can assign role for the user, that mean special group for easily management:

CREATE ROLE tigers;

GRANT tigers to serverspace;

Screenshot №6 — Create and grant role

There is event when we need to restore password or update it due to human factors or technical issue, for that we can use command:

ALTER USER serverspace WITH PASSWORD 'newpassphrase';

Screenshot №7 — Change password

For deleting we account we can use SQL query DROP:

DROP USER serverspace;

And check list of currently user in the DBMS:

SELECT * FROM pg_catalog.pg_user;

Screenshot №8 — Delete user

Conclusion

The information systems play a pivotal role in various aspects of our lives, spanning across industries such as healthcare, production, agriculture, entertainment, and beyond. The need to efficiently store, share, retrieve, and update data within these systems has prompted the development of specialized storage solutions for structured data. One such example is PostgreSQL, a powerful and versatile Database Management System (DBMS).