Introduction
PostgreSQL is a powerful, enterprise-grade database that has an advanced system for managing users and access levels. In PostgreSQL, there is no such thing as a “User” in the traditional sense; everything is a single entity. Role which can have the behavior of a user or a group.
Proper use of roles and permissions increases security, better manages databases, and prevents unwanted access—especially when running PostgreSQL on a VPS.
1. Prerequisites for working with Roles and Permissions
To get started, you must have the following:
A Linux VPS (like Ubuntu 20.04 or 22.04)
PostgreSQL installed
Root or sudo user access
Basic introduction to the command line
Log in to the postgres account:
sudo -i -u postgres psql2. Viewing Roles in PostgreSQL
To view all available roles:
\duSample output:
Role name | Attributes | Member of -----------+---------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replicate | {}3. Create a new role
Method 1: Create a role from within psql
CREATE ROLE user_demo;This role does not yet have login permission.
Method 2: Creating a role with specific features
Example: Role that can be logged in with a password:
CREATE ROLE user_demo WITH LOGIN PASSWORD 'StrongPass123';
Method 3: Create a role via CLI
createuser --interactiveIn this method, you will be asked:
Is it a superuser?
Is it allowed to create a database?
Is it allowed to create Roles?
4. Delete a role in PostgreSQL
Delete role:
DROP ROLE user_demo;To avoid an error if the role does not exist:
DROP ROLE IF EXISTS user_demo;5. Settings when creating a Role
PostgreSQL allows you to specify important properties when creating a Role:
CREATE ROLE app_user WITH LOGIN CREATEDB NOCREATEROLE PASSWORD 'ExamplePass';The most important attributes:
| Feature | Explanation |
|---|---|
| LOGIN | Permission to access the database |
| CREATEDB | Permission to create database |
| CREATEROLE | Permission to create a role |
| INHERIT | Inheriting permission from the role of mother |
| SUPERUSER | Full access |
| PASSWORD | Password |
| VALID UNTIL | Set expiration date |
6. Change Role settings with ALTER ROLE
If you want to change a role's permissions later:
Adding Login:
ALTER ROLE app_user WITH LOGIN;Remove the ability to Login:
ALTER ROLE app_user WITH NOLOGIN;Change password:
ALTER ROLE app_user WITH PASSWORD 'NewPassword!';7. Log in to PostgreSQL with another role
1) First set the role password:
2) Exit psql:
3) Login as a new role:
8. Create a table to test permissions
9. Granting Permissions (GRANT) in PostgreSQL
Granting a specific permission:
INSERT permission:
Giving ALL Privileges:
Giving access to all users (Public):
10. View permissions for each table
11. Revoke permissions (REVOKE)
Revoke Insert permission from a specific role:
Revoke all permissions:
Remove general permission:
12. Group Role in PostgreSQL
A group role is a role that other roles are members of—like Linux groups.
1) Create a Group Role:
2) Adding users to the group:
3) Membership Check:
4) Using the group role:
From this moment on, all permissions that dev_team has will be activated.
5) Automatic inheritance with INHERIT
13. Transfer ownership of objects before deleting a role
If you want to delete a role but the table or database belongs to it:
Change table owner:
Then delete the role:
14. Important Security Tips When Using Roles on VPS
Each service (Backend, Cronjob, Worker) must have a separate Role.
As a security tip, use low-access roles.
In database architecture, no application has a role.
superuserDon't give.Role passwords must be strong and have an expiration date.
For example, always use groups to manage access.
Conclusion
Practically everything in PostgreSQL is Role-based. You can create users, groups, system roles, and application roles with a single administrative model. Also, tools GRANT and REVOKE They provide complete control over tables, schemas, databases, and functions.
Proper role management in PostgreSQL is very important, especially on a VPS, because the security and stability of your entire database system depends on it.
