How to manage users in PostgreSQL

Galaxy Glossary

How do I manage users (roles) in PostgreSQL?

Manage users in PostgreSQL by creating, altering, granting, and revoking roles to control database access and security.

Sign up for the latest in SQL knowledge from the Galaxy Team!

Description

Why manage users in PostgreSQL?

PostgreSQL treats users as roles with optional login. Proper role management secures data and enforces least-privilege access for every engineer and service touching your ecommerce database.

How do I create a new user quickly?

Run CREATE ROLE with LOGIN and a password. The user is created instantly and can connect if the role has CONNECT rights on the target database.

CREATE ROLE storefront_reader LOGIN PASSWORD 'TempP@ss123';

How can I grant read-only access to all product tables?

First, add the role to a read-only group. Then grant USAGE on schema and SELECT on tables.This avoids repeating grants for each new table.

-- group role authreader=# CREATE ROLE readonly; -- membership authreader=# GRANT readonly TO storefront_reader; -- permissions appdb=# GRANT USAGE ON SCHEMA public TO readonly; appdb=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly; appdb=# ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;.

How do I change a user password safely?

Use ALTER ROLE ... PASSWORD. To force reconnection, REVOKE the old connection first or set a short password validity window.

ALTER ROLE storefront_reader PASSWORD 'N3wS3cur3P@ss';

How do I delete a user without breaking dependencies?

Before dropping a role, reassign or drop its owned objects and revoke memberships. This prevents orphaned privileges.

REASSIGN OWNED BY storefront_reader TO admin; DROP OWNED BY storefront_reader; DROP ROLE storefront_reader;

What are best practices for user management?

Prefer group roles, avoid superuser grants, rotate passwords, and audit role memberships quarterly. Automate with migration scripts so changes are version-controlled.

Why How to manage users in PostgreSQL is important

How to manage users in PostgreSQL Example Usage


-- Give the "analytics_app" user read access to all Orders and OrderItems
CREATE ROLE analytics_app LOGIN PASSWORD 'An@lyt1cs';
GRANT USAGE ON SCHEMA public TO analytics_app;
GRANT SELECT ON Orders, OrderItems TO analytics_app;

How to manage users in PostgreSQL Syntax


-- Create user (role) with login
CREATE ROLE role_name LOGIN [ENCRYPTED] PASSWORD 'password'
    [VALID UNTIL 'timestamp']
    [IN ROLE group_role [, ...]]
    [CONNECTION LIMIT n];

-- Change attributes
ALTER ROLE role_name [WITH]
    [LOGIN | NOLOGIN] [SUPERUSER | NOSUPERUSER]
    [PASSWORD 'password'] [VALID UNTIL 'timestamp'];

-- Grant table access (ecommerce example)
GRANT SELECT, INSERT ON TABLE Customers, Orders TO sales_app;

-- Revoke or drop
REVOKE ALL PRIVILEGES ON TABLE Products FROM temp_user;
DROP ROLE temp_user;

Common Mistakes

Granting SUPERUSER to regular accounts. This bypasses row-level security and risks accidental schema changes. Instead, grant only the required privileges or use group roles.
Dropping a role before reassigning owned objects, causing orphaned data or broken foreign keys. Always run REASSIGN OWNED and DROP OWNED first.

Frequently Asked Questions (FAQs)

Can I limit a user's connection count?

Yes. Use CREATE ROLE ... CONNECTION LIMIT n to cap concurrent sessions and protect the server from overload.

How do I list all users and their roles?

Query pg_roles or run \du in psql to view every role, its attributes, and memberships.

Can I enforce password expiry?

Set VALID UNTIL 'YYYY-MM-DD' when creating or altering a role. Login fails after the timestamp until the password is reset.

Want to learn about other SQL terms?

dbt tests

dbt tests are declarative assertions in dbt that automatically validate data quality during model builds.

dbt utils

dbt utils is a community-maintained dbt package that adds time-saving utility macros for analytics engineers, such as generating surrogate keys, safely unioning models, and automating date spines.

dbt Tutorial Glossary: Build, Test & Deploy SQL Models

dbt is an open-source framework that lets data teams transform raw warehouse tables into tested, documented, production-ready datasets using version-controlled SQL.

Trusted by top engineers on high-velocity teams

Assort Health

Curri

Rubie