Login vs group roles in PostgreSQL

Introduction

Login roles let users access the database. Group roles help organize users and share permissions easily.

When you want to give a person access to the database with a username and password.

When you want to give the same permissions to many users at once.

When you want to manage user permissions in a simple way by grouping them.

When you want to add or remove users from a group to change their permissions quickly.

When you want to separate who can log in from what permissions they have.

Syntax

PostgreSQL

CREATE ROLE role_name [WITH LOGIN];
CREATE ROLE group_name;
GRANT group_name TO role_name;

Roles with LOGIN can connect to the database.

Group roles cannot log in but can hold permissions for many users.

Examples

This creates a login role named 'alice' who can connect to the database.

PostgreSQL

CREATE ROLE alice WITH LOGIN PASSWORD 'secret';

This creates a group role named 'developers' that cannot log in.

PostgreSQL

CREATE ROLE developers;

This adds 'alice' to the 'developers' group, giving her the group's permissions.

PostgreSQL

GRANT developers TO alice;

Sample Program

This example creates a login role 'bob' and a group role 'admins'. Then it adds 'bob' to 'admins'. Finally, it shows the roles and membership info.

PostgreSQL

CREATE ROLE bob WITH LOGIN PASSWORD 'mypassword';
CREATE ROLE admins;
GRANT admins TO bob;
-- Check roles and memberships
SELECT rolname, rolcanlogin FROM pg_roles WHERE rolname IN ('bob', 'admins');
SELECT roleid, member FROM pg_auth_members WHERE member = (SELECT oid FROM pg_roles WHERE rolname = 'bob');

Important Notes

Login roles are like user accounts.

Group roles are like teams or departments.

Users can belong to many groups to get combined permissions.

Summary

Login roles allow users to connect to the database.

Group roles organize users and share permissions easily.

Grant group roles to login roles to manage permissions efficiently.