How to Manage Users in PostgreSQL

How do I manage users and privileges in PostgreSQL?

Create, alter, drop, and privilege roles (users) to control database access.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why does PostgreSQL use roles instead of standalone users?

PostgreSQL abstracts authentication and authorization into roles. A role can own objects, receive privileges, and optionally log in. Treating users as login-enabled roles lets you grant the same rights to many users or service accounts with less duplication.

How do I create a login role?

Syntax

CREATE ROLE role_name [LOGIN] [PASSWORD 'secret'] [IN ROLE parent_role] [VALID UNTIL 'YYYY-MM-DD'];

Example

CREATE ROLE analyst LOGIN PASSWORD 'Strong_42' VALID UNTIL '2025-01-01';

How do I grant privileges to a user?

Syntax

GRANT privilege_list ON object_type object_name TO role_name [WITH GRANT OPTION];

Example

GRANT SELECT, INSERT ON TABLE sales.orders TO analyst;

How can I alter or reset user settings?

Use ALTER ROLE to change passwords, connection limits, timeouts, and more. For example: ALTER ROLE analyst CONNECTION LIMIT 10; or ALTER ROLE analyst RESET ALL;

How do I remove a user safely?

First transfer ownership and revoke privileges: REASSIGN OWNED BY analyst TO admin; REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM analyst; Then drop the role: DROP ROLE analyst;

What are best practices for secure user management?

Grant the least privilege needed, avoid creating superusers, rotate passwords periodically, and use role hierarchies (e.g., readonly, writer) instead of ad-hoc grants.

Why How to Manage Users in PostgreSQL is important

How to Manage Users in PostgreSQL Example Usage


-- Full workflow
CREATE ROLE analyst LOGIN PASSWORD 'Strong_42';
GRANT CONNECT ON DATABASE reporting TO analyst;
GRANT USAGE ON SCHEMA public TO analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analyst;

How to Manage Users in PostgreSQL Syntax


-- Create a user (role with LOGIN)
CREATE ROLE role_name LOGIN PASSWORD 'secret' [VALID UNTIL 'YYYY-MM-DD'];

-- Grant object privileges
GRANT { SELECT | INSERT | UPDATE | DELETE | ALL } ON { TABLE | DATABASE | SCHEMA | SEQUENCE } object TO role_name;

-- Alter role attributes
ALTER ROLE role_name [SET param = value | RESET param | PASSWORD 'newpass' | VALID UNTIL 'date'];

-- Drop role
REASSIGN OWNED BY role_name TO new_owner;
DROP ROLE role_name;

Common Mistakes

Creating every user as a superuser. This bypasses privilege checks and exposes the entire cluster. Fix: grant only the permissions required and reserve superuser for administrators.
Dropping a role that still owns objects. This causes errors and orphaned objects. Fix: run REASSIGN OWNED and DROP OWNED before DROP ROLE.