2. Role Management

2.1. CREATE ROLE

Creates a new database role without login capability (a group role).

Syntax:

CREATE ROLE role_name
  [ [ WITH ] option [ ... ] ]

where option can be:

    SUPERUSER | NOSUPERUSER
  | INHERIT | NOINHERIT
  | CREATEROLE | NOCREATEROLE
  | CREATEDB | NOCREATEDB
  | LOGIN | NOLOGIN
  | REPLICATION | NOREPLICATION
  | BYPASSRLS | NOBYPASSRLS
  | CONNECTION LIMIT connlimit
  | PASSWORD 'password' | PASSWORD NULL
  | VALID UNTIL 'timestamp'
  | IN ROLE role_name [, ...]
  | ROLE role_name [, ...]
  | ADMIN role_name [, ...]

Examples:

-- Create a basic group role
CREATE ROLE analysts;

-- Create a role with specific attributes
CREATE ROLE senior_developer
  INHERIT
  CREATEROLE
  CREATEDB;

-- Create a role that is member of other roles
CREATE ROLE junior_developer
  IN ROLE developers, readers;

-- Create a role and immediately add members
CREATE ROLE team_leads
  ROLE alice, bob, charlie;

-- Create a role with admin members (they can grant this role to others)
CREATE ROLE dba_team
  ADMIN alice, bob;

2.2. CREATE USER

Creates a new database role with login capability.

Syntax:

CREATE USER user_name
  [ [ WITH ] option [ ... ] ]

CREATE USER is equivalent to CREATE ROLE ... LOGIN.

Examples:

-- Create a basic user with password
CREATE USER alice WITH PASSWORD 'secure_password_123';

-- Create a user with password expiration
CREATE USER contractor
  PASSWORD 'temp_pass'
  VALID UNTIL '2025-12-31 23:59:59';

-- Create a user with connection limit
CREATE USER api_service
  PASSWORD 'service_password'
  CONNECTION LIMIT 10;

-- Create a superuser (use with caution)
CREATE USER admin_user
  SUPERUSER
  PASSWORD 'admin_password';

-- Create a user that bypasses row-level security
CREATE USER etl_user
  BYPASSRLS
  PASSWORD 'etl_password';

-- Create a user that can create databases
CREATE USER developer
  CREATEDB
  PASSWORD 'dev_password';

2.3. ALTER ROLE

Modifies the attributes of an existing role.

Syntax:

-- Modify role attributes
ALTER ROLE role_name
  [ [ WITH ] option [ ... ] ]

-- Rename a role
ALTER ROLE role_name RENAME TO new_role_name

-- Set configuration parameters for a role
ALTER ROLE role_name SET parameter TO value
ALTER ROLE role_name SET parameter = value
ALTER ROLE role_name RESET parameter
ALTER ROLE role_name RESET ALL

Examples:

-- Change password
ALTER ROLE alice PASSWORD 'new_secure_password';

-- Remove password (allow trust authentication only)
ALTER ROLE alice PASSWORD NULL;

-- Set password expiration
ALTER ROLE alice VALID UNTIL '2025-06-30';

-- Remove password expiration
ALTER ROLE alice VALID UNTIL 'infinity';

-- Grant superuser privilege
ALTER ROLE alice SUPERUSER;

-- Revoke superuser privilege
ALTER ROLE alice NOSUPERUSER;

-- Allow role to create databases
ALTER ROLE developer CREATEDB;

-- Prevent role from creating databases
ALTER ROLE developer NOCREATEDB;

-- Enable RLS bypass
ALTER ROLE etl_user BYPASSRLS;

-- Change connection limit
ALTER ROLE api_service CONNECTION LIMIT 50;

-- Remove connection limit
ALTER ROLE api_service CONNECTION LIMIT -1;

-- Rename a role
ALTER ROLE old_name RENAME TO new_name;

-- Set default search_path for a role
ALTER ROLE developer SET search_path TO myschema, public;

-- Reset a configuration parameter
ALTER ROLE developer RESET search_path;

2.4. DROP ROLE

Removes a database role.

Syntax:

DROP ROLE [ IF EXISTS ] role_name [, ...]
DROP USER [ IF EXISTS ] user_name [, ...]

Important: Before dropping a role, you must:

1. Remove the role from all role memberships
2. Reassign or drop all objects owned by the role
3. Revoke all privileges granted to the role

Examples:

-- Drop a single role
DROP ROLE analysts;

-- Drop multiple roles
DROP ROLE junior_developer, intern;

-- Drop role only if it exists (no error if not found)
DROP ROLE IF EXISTS temporary_role;

-- Drop user
DROP USER alice;
DROP USER IF EXISTS bob, charlie;

2.5. Role Attributes Reference

---