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.
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 postgrespsql
For checking user in the DBMS we can use command below:
SELECT * FROM pg_catalog.pg_user; 
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;
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.
In the same way we can revoke that:
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM serverspace;
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;
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';
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;
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).
