KAHIBARO
Discord Login Register ☰
☰

11.4. Users and Roles

Table of Contents

Why Users and Roles Matter in PostgreSQL

When you move from local experiments to real applications, you must control who can do what in your database. PostgreSQL uses a powerful system of roles to manage this.

In PostgreSQL, users are just roles that can log in. Everything is built on top of roles, not a separate user concept.

In PostgreSQL:

  • A user is a role with LOGIN privilege.
  • Permissions are granted to roles, not directly to connections.

You will use users and roles to:

Roles vs Users

The basic idea

PostgreSQL has one concept: roles. Each role can have different attributes, for example:

If a role has the LOGIN attribute, you usually call it a user. If it has no LOGIN, you usually call it a group role.

Examples

sql 
  CREATE ROLE app_user LOGIN PASSWORD 'strong-password';
sql 
  CREATE ROLE readonly;
sql 
  GRANT readonly TO app_user;

Now, app_user has all privileges that are granted to readonly.

Role attributes overview

Some common role attributes:

AttributeMeaning
LOGINCan connect to the database
SUPERUSERBypasses all permission checks, full power
CREATEDBCan create databases
CREATEROLECan create, alter, and drop other roles
INHERITInherits privileges from roles it is a member of
NOINHERITDoes not automatically inherit privileges
REPLICATIONCan initiate replication and create replication connections
BYPASSRLSCan bypass row level security

You combine these attributes when creating or altering roles.

Creating Roles and Users

Creating a simple user

To create a user that can log in, you add LOGIN:

sql 
CREATE ROLE alice LOGIN PASSWORD 'alice_secret';

This is equivalent to:

sql 
CREATE USER alice WITH PASSWORD 'alice_secret';

CREATE USER is just a shorthand for CREATE ROLE ... LOGIN.

Creating a user for your application

Imagine you have a database named myapp. You can create a user just for that app:

sql 
CREATE USER myapp_user WITH PASSWORD 'change-this';

Later you will grant this user specific rights on the myapp database and its tables.

Creating group roles

Group roles are meant to hold privileges, not to log in.

sql 
CREATE ROLE editors;
CREATE ROLE viewers;

They have no LOGIN attribute, so nobody can connect as editors or viewers directly. Instead, you grant these group roles to real users.

Example:

sql 
CREATE USER bob WITH PASSWORD 'bob_secret';
CREATE USER carol WITH PASSWORD 'carol_secret';
GRANT editors TO bob;
GRANT viewers TO carol;

Now:

Role Attributes in Practice

Creating roles with attributes

You can set attributes when creating a role.

sql 
CREATE ROLE db_admin
  LOGIN
  CREATEDB
  CREATEROLE
  PASSWORD 'admin_secret';

This role:

Another example, a read only application role:

sql 
CREATE ROLE app_readonly
  LOGIN
  PASSWORD 'readonly_secret'
  INHERIT;

Later you will grant SELECT privileges to this role on needed tables.

SUPERUSER and why to avoid it

SUPERUSER bypasses all permissions. It can:

Example:

sql 
CREATE ROLE root_admin SUPERUSER LOGIN PASSWORD 'do-not-use-in-prod';

In real applications you should avoid using superuser roles for daily operations.

Rule: Do not give SUPERUSER to application users. Use limited roles with only the privileges they truly need.

Use SUPERUSER only for initial setup and exceptional maintenance, and usually through a dedicated admin connection.

INHERIT vs NOINHERIT

By default, roles have INHERIT. This means if a role is a member of another role, it automatically uses its privileges.

Example:

sql 
CREATE ROLE readonly;
CREATE USER report_user INHERIT PASSWORD 'report_secret';
GRANT readonly TO report_user;

If readonly has SELECT on certain tables, report_user can select from them without extra commands.

With NOINHERIT, the member role does not automatically get the privileges. It must SET ROLE or SET SESSION AUTHORIZATION to use them. This is more advanced and usually not needed for beginners.

You can see attributes with:

sql 
\dRp+     -- in psql, list roles with attributes

Role Membership and Grouping

Granting and revoking role membership

To add a user to a role:

sql 
GRANT editors TO bob;

To remove:

sql 
REVOKE editors FROM bob;

You can also chain group roles, for example:

sql 
CREATE ROLE app_read_access;
CREATE ROLE app_write_access;
GRANT app_read_access TO app_write_access;

Now, any role that is a member of app_write_access will also get app_read_access privileges.

Using roles like groups

A common pattern is to:

  1. Define group roles that represent responsibilities.
  2. Grant object privileges to these group roles.
  3. Add or remove users from these group roles.

Example structure for a blog application:

sql 
CREATE ROLE blog_admin;
CREATE ROLE blog_editor;
CREATE ROLE blog_viewer;

Later, you grant table privileges to these roles. Then you assign users to them:

sql 
CREATE USER alice PASSWORD 'alice_pw';
CREATE USER bob PASSWORD 'bob_pw';
GRANT blog_admin TO alice;
GRANT blog_editor TO bob;

Managing Permissions with GRANT and REVOKE

Roles by themselves do not have access to your data. You must grant privileges on specific objects.

Common privileges

For tables, common privileges include:

PrivilegeMeaning
SELECTRead rows
INSERTAdd new rows
UPDATEModify existing rows
DELETERemove rows
TRUNCATERemove all rows in a table
REFERENCESCreate foreign keys referencing this table
TRIGGERCreate triggers on the table

For databases:

PrivilegeMeaning
CONNECTConnect to the database
CREATECreate new schemas in the database
TEMPCreate temporary tables

Example: giving a user access to a database

Assume you have:

sql 
CREATE DATABASE myapp;
CREATE USER myapp_user PASSWORD 'secret';

Now give myapp_user the right to connect:

sql 
GRANT CONNECT ON DATABASE myapp TO myapp_user;

Then, inside myapp, you can give more specific rights.

Example: read only access to a table

Assume a table:

sql 
CREATE TABLE customers (
  id serial PRIMARY KEY,
  name text,
  email text
);

And a role:

sql 
CREATE ROLE readonly;

Grant select on this table:

sql 
GRANT SELECT ON TABLE customers TO readonly;

Any user that has readonly membership now can read from customers.

Example: read and write application user

sql 
CREATE USER app_user PASSWORD 'app_secret';
GRANT CONNECT ON DATABASE myapp TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

USAGE ON SCHEMA lets the role access objects in that schema.

If you later add new tables and want them to be automatically writable by app_user, you should adjust default privileges, but that is usually covered in more advanced material.

Revoking privileges

Use REVOKE to remove privileges:

sql 
REVOKE SELECT ON TABLE customers FROM readonly;
REVOKE CONNECT ON DATABASE myapp FROM app_user;

Be careful: removing privileges from a group role affects all users that are members of that role.

Practical Role Design Patterns

Separate application and admin users

Never use postgres or another superuser as your application user.

A safer design:

RolePurpose
postgresSuperuser for server maintenance only
myapp_adminCan manage schema and indexes of myapp
myapp_userApplication user, can read and write tables
myapp_readonlyRead only account for reporting or debugging

Example setup:

sql 
-- Admin role
CREATE ROLE myapp_admin
  LOGIN
  CREATEDB
  CREATEROLE
  PASSWORD 'admin_only';
-- App user with limited privileges
CREATE ROLE myapp_user
  LOGIN
  PASSWORD 'app_only';
-- Read only role
CREATE ROLE myapp_readonly
  LOGIN
  PASSWORD 'readonly_only';

Grant access inside your myapp database:

sql 
-- In database myapp:
-- Admin can do everything in this db
GRANT ALL PRIVILEGES ON DATABASE myapp TO myapp_admin;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myapp_admin;
-- App user can read and write data, but not change schema
GRANT CONNECT ON DATABASE myapp TO myapp_user;
GRANT USAGE ON SCHEMA public TO myapp_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myapp_user;
-- Read only user
GRANT CONNECT ON DATABASE myapp TO myapp_readonly;
GRANT USAGE ON SCHEMA public TO myapp_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO myapp_readonly;

Now:

Using group roles for permissions

Instead of directly granting many privileges to users, create group roles for each category of access.

Example for a shop database:

sql 
CREATE ROLE orders_read;
CREATE ROLE orders_write;
CREATE ROLE products_admin;

Grant privileges to those roles:

sql 
GRANT SELECT ON TABLE orders TO orders_read;
GRANT INSERT, UPDATE ON TABLE orders TO orders_write;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE products TO products_admin;

Then assign users:

sql 
CREATE USER support_agent PASSWORD 'support_pw';
CREATE USER warehouse_worker PASSWORD 'warehouse_pw';
CREATE USER product_manager PASSWORD 'product_pw';
GRANT orders_read TO support_agent;
GRANT orders_read, orders_write TO warehouse_worker;
GRANT products_admin TO product_manager;

This makes it easier to change permissions later. You only adjust the group role grants, not each user.

Viewing and Modifying Existing Roles

Listing roles

In psql, you can list roles with:

sql 
\du

or with more detail:

sql 
\du+

You will see something similar to:

Role nameAttributesMember of
postgresSuperuser, Create role, Create DB{}
myapp_adminCreate role, Create DB{}
myapp_user{myapp_readonly}
myapp_readonly{}

You can also query the system catalog:

sql 
SELECT rolname, rolsuper, rolcreatedb, rolcanlogin
FROM pg_roles;

Changing role attributes

Use ALTER ROLE to modify roles:

sql 
ALTER ROLE myapp_user WITH PASSWORD 'new_secret';
ALTER ROLE myapp_user WITH NOINHERIT;
ALTER ROLE myapp_admin WITH NOCREATEDB;

You can also disable login:

sql 
ALTER ROLE myapp_user NOLOGIN;

This is useful if you want to temporarily block an account without deleting it.

Dropping roles

To remove a role:

sql 
DROP ROLE myapp_readonly;

This will fail if:

You must first transfer ownership or drop the objects, and remove memberships.

Example, reassign all objects owned by a role to another role:

sql 
REASSIGN OWNED BY old_role TO new_role;
DROP OWNED BY old_role;
DROP ROLE old_role;

Secure Passwords and Login Control

Password considerations

PostgreSQL can use different authentication methods. Passwords are common when applications connect.

When you create roles with passwords:

sql 
CREATE USER app_user WITH PASSWORD 'short';

you should:

You can specify password encryption algorithm with ENCRYPTED PASSWORD in some versions, but exact details depend on server configuration and version.

Controlling where users can log in

Authentication methods and allowed sources are configured in the pg_hba.conf file, not directly on roles. But roles interact with this configuration.

For example, you might:

The details of pg_hba.conf and host based authentication are usually covered in more advanced PostgreSQL administration topics, but you should know that role names are involved in that configuration.

Summary

You have seen how PostgreSQL uses roles as the core building block for user and permission management:

With these basics, you can design a safe and flexible permission structure for your backend applications using PostgreSQL.

Views: 18

Comments

Please login to add a comment.

Don't have an account? Register now!

© 2025 Kahibaro
Visitors: 3514 / 24h
Impressum / Imprint
Datenschutz / Data Policy