PostgreSQL is one of the most powerful open-source databases in the world. But what makes it truly enterprise-class is its robust permission and security system, built on top of roles and role attributes.
If you have ever run this command:
ALTER USER postgres WITH SUPERUSER CREATEDB CREATEROLE LOGIN;
You can also see the list of roles that exist in your current database by using this shorthand command
postgres=# \du+
List of roles
Role name | Attributes | Description
-------------+------------------------------------------------------------+-------------
marc_demo | Superuser, Create DB |
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS |
…your terminal probably displayed a list of attributes like:
- SUPERUSER / NOSUPERUSER
- CREATEDB / NOCREATEDB
- CREATEROLE / NOCREATEROLE
- LOGIN / NOLOGIN
- INHERIT / NOINHERIT
- BYPASSRLS / NOBYPASSRLS
- REPLICATION
- CONNECTION LIMIT
- PASSWORD / ENCRYPTED PASSWORD
- VALID UNTIL
- SET / RESET
For beginners, this list can be confusing. This blog breaks everything down in simple language with clear examples, so even a newcomer to PostgreSQL can understand role attributes from the ground up.
1. What Are Roles in PostgreSQL?
A role is simply an account inside PostgreSQL that can:
- own objects (tables, functions, schemas)
- connect to the database (if it has LOGIN)
- be granted permissions
- be used as a group for other roles.
2. What Are Role Attributes?
Role attributes define the capabilities and high-level permissions of a role, such as:
- Can it create databases?
- Can it manage other roles?
- Can it log in?
- Can it bypass Row-Level Security?
These attributes shape how the role behaves inside the system.
You can assign them when creating or altering a role:
Example :
CREATE ROLE analyst LOGIN CREATEDB;
ALTER ROLE analyst CREATEROLE;
3. LOGIN vs NOLOGIN
LOGIN
Allows a role to connect to PostgreSQL.
CREATE ROLE app_user LOGIN PASSWORD 'secret';
NOLOGIN
Used for group roles or permission containers.
CREATE ROLE read_only NOLOGIN;
GRANT read_only TO app_user;
4. CREATEDB / NOCREATEDB
Controls whether a role can create new databases.
ALTER ROLE app_user CREATEDB;
Usually given to developers or DB admins in dev environments, but rarely in production.
5. CREATEROLE / NOCREATEROLE
Allows a role to create, modify, or delete other roles.
CREATE ROLE hr_admin LOGIN CREATEROLE;
Useful when you want someone to manage users without giving full superuser powers.
6. SUPERUSER / NOSUPERUSER
SUPERUSER
A superuser can bypass all permission checks. It can:
- read/write any table
- install extensions
- create/drop databases
- alter system settings
- bypass RLS
- replicate data
ALTER ROLE postgres SUPERUSER;
IMPORTANT:
Grant this only to trusted administrators.
Even a single mistake by a superuser can damage your entire database.
7. INHERIT / NOINHERIT
Defines how a role receives privileges from other roles.
INHERIT
Automatically receives privileges from granted roles.
CREATE ROLE app_user LOGIN INHERIT;
GRANT read_only TO app_user;
NOINHERIT
The user must manually switch into a role.
SET ROLE read_only;
Useful when you want controlled privilege escalation.
8. BYPASSRLS / NOBYPASSRLS
Before learning BYPASSRLS, let's understand Row-Level Security (RLS).
8.1 What is RLS?
RLS allows you to restrict which rows a user can see.You can define policies like:
- a user can see only their own orders
- sales managers can see orders from their region
- analysts can see only anonymized rows
Example:
Create two login roles (users)
CREATE ROLE app_user LOGIN PASSWORD 'secret';
CREATE ROLE reporting_user LOGIN PASSWORD 'report123';
Create the orders table with an owner column
This column will store which user owns each row.
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
name TEXT,
price INT,
owner TEXT -- this stores the PostgreSQL username
);
Enable Row-Level Security on the table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
Insert some sample data with different ownersINSERT INTO orders (name, price, owner)
VALUES
('Keyboard', 1000, 'app_user'),
('Mouse', 500, 'app_user'),
('Monitor', 15000, 'reporting_user'),
('Laptop', 40000, 'reporting_user');
Create an RLS policy so users see only their own rows
Use current_user to match the table’s owner column:
CREATE POLICY orders_policy
ON orders
FOR SELECT
USING (owner = current_user);
Grant SELECT access to both users
RLS filters rows automatically, but PostgreSQL still requires SELECT privilege:
GRANT SELECT ON orders TO app_user;
GRANT SELECT ON orders TO reporting_user;
TESTING THE RLS POLICY
Test as app_user
SET ROLE app_user;
SELECT * FROM orders;
Expected output:
id | name | price | owner
----+----------+-------+----------
1 | Keyboard | 1000 | app_user
2 | Mouse | 500 | app_user
Test as reporting_user
RESET ROLE;
SET ROLE reporting_user;
SELECT * FROM orders;
Expected output:
id | name | price | owner
----+-----------+-------+----------------
3 | Monitor | 15000 | reporting_user
4 | Laptop | 40000 | reporting_user
8.2 What is BYPASSRLS?
BYPASSRLS allows a role to ignore RLS policies completely.
BYPASSRLS
Role sees all rows, no filtering.
NOBYPASSRLS
Role must respect RLS.
Example
CREATE ROLE auditor LOGIN BYPASSRLS;
The auditor role can now view every row even in tables with RLS enabled.
This attribute is extremely sensitive—similar to a mini superuser capability.
9. REPLICATION / NOREPLICATION
Allows a role to start streaming replication for backups or failover.
CREATE ROLE replica_user LOGIN REPLICATION PASSWORD 'replica_pass';
You will see this role used in pg_hba.conf entries such as:
host replication replica_user 10.0.0.10/32 md5
10. CONNECTION LIMIT
Sets how many concurrent connections a role is allowed.
ALTER ROLE app_user CONNECTION LIMIT 5;
Useful for:
- controlling app server overload
- multitenant environments
- preventing connection storms
CONNECTION LIMIT -1 means no limit.
11. PASSWORD / ENCRYPTED PASSWORD
Sets or changes the password for a role.
ALTER ROLE app_user PASSWORD 'new_secret';
PostgreSQL always stores passwords in hashed form — not plain text.
12. VALID UNTIL
Defines password expiry.
ALTER ROLE hr_user VALID UNTIL '2025-12-31';
After expiry, login will fail unless the password is updated.
13. SET / RESET
Assigns default session settings (GUCs) to the role.
Example: set custom search path
ALTER ROLE app_user SET search_path TO my_schema, public;
Remove the setting:
ALTER ROLE app_user RESET search_path;
This is helpful when you want different roles to have different behaviors automatically.
14. RENAME TO
Rename a role:
ALTER ROLE temp_user RENAME TO final_user;
Privileges remain the same.
15. Practical Example (Putting Everything Together)
Create a secure application user
CREATE ROLE app_user
LOGIN
NOBYPASSRLS
NOINHERIT
NOCREATEDB
NOCREATEROLE
PASSWORD 'secure123';
Create a read-only group
CREATE ROLE read_only NOLOGIN;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;
Add app user to read-only group
GRANT read_only TO app_user;
Create an auditor role that bypasses RLS
CREATE ROLE auditor LOGIN BYPASSRLS PASSWORD 'audit@2024';
Now your system has:
- an app user that obeys RLS
- a read-only group
- an auditor that bypasses RLS
Just like a production-ready setup.
Conclusion
Roles and role attributes are the backbone of PostgreSQL’s permission system. Understanding them gives you the power to:
- create secure environments
- separate privileges between users
- enforce security policies
- control resource usage
- manage multi-user and multi-application access
- and protect sensitive data with RLS
PostgreSQL offers a level of control that very few databases match.
Once you master role attributes, you will be able to design production-grade permission models with confidence and precision.
Mastering these attributes is one of the most important steps toward becoming a PostgreSQL professional