Role creation and management in PostgreSQL

Introduction

Roles help control who can do what in a database. They keep data safe and organized.

You want to give a new user access to the database.

You need to limit what a user can do, like only read data but not change it.

You want to group users with similar permissions together.

You need to remove access for someone who no longer needs it.

Syntax

PostgreSQL

CREATE ROLE role_name [WITH option [option ...]];
ALTER ROLE role_name [WITH option [option ...]];
DROP ROLE role_name;

Use CREATE ROLE to make a new role.

Use ALTER ROLE to change role settings.

Use DROP ROLE to remove a role.

Examples

This creates a simple role without login rights.

PostgreSQL

CREATE ROLE read_only;
-- Creates a role named read_only with no special permissions.

This role can be used as a user to connect to the database.

PostgreSQL

CREATE ROLE admin WITH LOGIN PASSWORD 'secret';
-- Creates a role that can log in with a password.

Useful for roles that only group permissions.

PostgreSQL

ALTER ROLE read_only NOLOGIN;
-- Prevents the role from logging in directly.

Use this when the role is no longer needed.

PostgreSQL

DROP ROLE read_only;
-- Deletes the role from the database.

Sample Program

This creates a role named analyst that can log in and create databases. Then it shows the role details.

PostgreSQL

CREATE ROLE analyst WITH LOGIN PASSWORD 'data123';
ALTER ROLE analyst CREATEDB;
-- Allows analyst to create databases

-- Check roles
SELECT rolname, rolcanlogin, rolcreatedb FROM pg_roles WHERE rolname = 'analyst';

Important Notes

Roles can represent users or groups.

Use strong passwords for roles with login rights.

Always check existing roles before creating new ones to avoid duplicates.

Summary

Roles control access and permissions in PostgreSQL.

Use CREATE ROLE, ALTER ROLE, and DROP ROLE to manage roles.

Roles can have login rights and other options like creating databases.