PostgreSQL Users & Roles
Managing database access and user accounts
👥 What are Users & Roles?
Users and roles in PostgreSQL control who can access your database and what actions they can perform. Roles can be users, groups, or both, providing flexible access management for your database security.
-- Create a simple user
CREATE USER john_doe WITH PASSWORD 'secure_password';
-- Create a role
CREATE ROLE developers;
Output:
CREATE ROLE
Key Concepts
Users
Individual accounts that can log in
CREATE USER alice WITH LOGIN;Roles
Groups that organize permissions
CREATE ROLE admin_group;Passwords
Secure authentication credentials
ALTER USER alice PASSWORD 'new_pass';Membership
Assign users to role groups
GRANT admin_group TO alice;🔹 Creating Users
Users are database accounts that can connect and perform operations. Creating users is the first step in database security management. Each user should have unique credentials and appropriate access levels for their responsibilities.
-- Create user with login capability
CREATE USER app_user WITH PASSWORD 'mypassword123';
-- Create user with multiple options
CREATE USER admin_user
WITH PASSWORD 'admin_pass'
CREATEDB
CREATEROLE;
-- Create user with connection limit
CREATE USER limited_user
WITH PASSWORD 'pass123'
CONNECTION LIMIT 5;Output:
CREATE ROLE
🔹 Creating Roles
Roles are containers for permissions that can be assigned to multiple users. They simplify permission management by grouping common access patterns. Roles can inherit from other roles, creating hierarchical permission structures for complex organizations.
-- Create a basic role
CREATE ROLE readonly;
-- Create role with specific privileges
CREATE ROLE data_analyst
WITH LOGIN
PASSWORD 'analyst_pass';
-- Create role that can create databases
CREATE ROLE db_creator
WITH CREATEDB;Output:
CREATE ROLE
🔹 Granting Role Membership
Assign users to roles to inherit permissions efficiently. Role membership allows centralized permission management where changes to a role automatically affect all members. This approach reduces administrative overhead and ensures consistent access control.
-- Add user to a role
GRANT readonly TO app_user;
-- Add multiple users to a role
GRANT developers TO alice, bob, charlie;
-- Grant with admin option
GRANT admin_group TO manager_user WITH ADMIN OPTION;Output:
GRANT ROLE
🔹 Modifying Users and Roles
Update user properties and role attributes as needed. Regular maintenance of user accounts includes password updates, privilege adjustments, and connection limit modifications. Keeping user attributes current ensures security and proper access control.
-- Change password
ALTER USER app_user WITH PASSWORD 'new_secure_pass';
-- Add superuser privilege
ALTER USER admin_user WITH SUPERUSER;
-- Remove login capability
ALTER ROLE old_role WITH NOLOGIN;
-- Set connection limit
ALTER USER app_user CONNECTION LIMIT 10;Output:
ALTER ROLE
🔹 Viewing Users and Roles
Check existing users and their properties in your database. Monitoring user accounts helps maintain security and troubleshoot access issues. PostgreSQL provides several system views to inspect user and role configurations.
-- List all users and roles
\du
-- Query user information
SELECT rolname, rolsuper, rolcreatedb
FROM pg_roles;
-- Check role memberships
SELECT r.rolname AS role,
m.rolname AS member
FROM pg_roles r
JOIN pg_auth_members am ON r.oid = am.roleid
JOIN pg_roles m ON am.member = m.oid;Output:
rolname | rolsuper | rolcreatedb ------------+----------+------------- postgres | t | t app_user | f | f admin_user | t | t
🔹 Removing Users and Roles
Delete users and roles when they're no longer needed. Before removing a role, ensure it owns no database objects and has no active connections. Proper cleanup prevents orphaned objects and maintains database organization.
-- Remove a user
DROP USER app_user;
-- Remove a role
DROP ROLE readonly;
-- Revoke role membership first, then drop
REVOKE developers FROM alice;
DROP ROLE developers;Output:
DROP ROLE