Purpose of the article: Managing user access and permissions
Intended Audience: DevOps Engineers, Cloud Engineers
Tools and Technology: PostgreSQL, dbeaver
Keywords: User Access, Management Database, Permissions Role (Group), Users
What is PostgreSQL:
PostgreSQL is an open-source database management system. It can scale to very complex data tasks and has various characteristics that are safely stored.
Why Use PostgreSQL? For These Reasons:
- Data Integrity and Fault Tolerance: PostgreSQL empowers developers to construct robust applications with built-in mechanisms to ensure data integrity and fault tolerance, safeguarding critical data against corruption or loss.
- Open-Source Freedom: As a free and open-source database management system, PostgreSQL offers accessibility and flexibility without the constraints of proprietary licenses, enabling widespread adoption and innovation.
Creating Users and Roles (Group):
Creating users and roles in PostgreSQL is the first step towards controlling user access. The CREATE command can be used to create users, while the CREATE ROLE command can be used to define roles. The basis for controlling access levels and granting permissions to specific individuals or groups is set by these instructions.
Granting Privileges:
A wide range of rights can be assigned to roles or users in PostgreSQL to manage their behaviour inside the database. Among other things, privileges include the ability to execute stored procedures (EXECUTE), select, insert, update, and delete data (SELECT, INSERT, UPDATE, DELETE), and construct databases (CREATEDB).
Restricting Access to Specific Databases:
One of the key aspects of managing user access is restricting access to specific databases. PostgreSQL allows administrators to grant or revoke the CONNECT privilege on individual databases, ensuring that users can only connect to databases for which they have been explicitly granted access.
Why We Use Deaver:
Like a spreadsheet, Deaver offers an easy-to-use interface for data manipulation that enables users to carry out a variety of tasks like filtering, sorting, querying, and modifying data. Furthermore, it facilitates the generation of analytical reports by merging information from several data sources, giving consumers new perspectives on their data.
Steps for Creation of Roles
1. Creation of roles (groups)
CREATE ROLE hr;
CREATE ROLE finance;
CREATE ROLE marketing;
2. Creation of users
CREATE USER john WITH PASSWORD ‘*****’;
CREATE USER smith WITH PASSWORD ‘******’;
CREATE USER stephen WITH PASSWORD ‘*******’;
CREATE USER martin WITH PASSWORD ‘*******’;
3. To show the databases list in your databases
SELECT Dat name FROM pg_database;
To setup a database in PostgreSQL, you usually need to use a client or command-line tool to establish a direct connection to the database and provide the database name during that procedure. After you’ve connected, any SQL command you give after that will be executed within that database context.
We need to set the databases as per our requirement and, I set my database as ‘postgres.’
4. The groups need to be granted permission to connect to the database.
GRANT CONNECT ON DATABASE postgres TO hr;
GRANT CONNECT ON DATABASE postgres TO finance;
GRANT CONNECT ON DATABASE postgres TO marketing;
5. Assigning users to the roles (groups)
grant finance to john, smith
grant marketing to martin
grant hr to stephen
6. Assigning necessary privileges to the roles (groups)
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO stephen;
GRANT INSERT, UPDATE, SELECT, DELETE ON ALL TABLES IN SCHEMA public TO finance;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO marketing;
7. Login in as martin to check the privileges
The permission denied error was thrown up as we tried to access the ungranted privileges.
8. Revoking privileges from the roles (groups)
REVOKE select ON public.basket_a FROM finance ;
9. Dropping the user
drop user john;
Conclusion: Maintaining data integrity and security in PostgreSQL requires efficient user access management. Administrators can maintain proper access levels by assigning privileges, creating roles, and conducting routine monitoring.
Author Bio:
Kalyan PATHAKAMSETTY
Data Engineering-Analytics - Associate Software Engineer
For the last year and a half, I have been employed at MOURI Tech as an Associate Software Engineer. I'm proficient with SQL and Python. enthusiastic about learning about and utilising new technologies
