PostgreSQL Users & Roles: Creating and Managing Database Access

In PostgreSQL, “users” and “roles” are essentially the same thing. A role is the technical term; a user is a role that can log in. Understanding how to create and manage roles is essential for database administration and security.

Why Manage Roles?

You might think: “I’m just learning, can’t I just use the superuser account?” You could, but here’s why you shouldn’t:

  • Security: The superuser (postgres) is too powerful. A single mistake could destroy your entire database
  • Real-world practice: Every production database uses role-based access control
  • Learning: Managing roles teaches you how permissions actually work
  • Isolation: Different applications can use different roles with limited permissions

Key Concepts

Superuser Role The postgres role is the superuser—it has unrestricted access to everything. In a production environment, you’d almost never use the superuser account for daily work.

Login Role A role with the LOGIN attribute can connect to the database. If you create a role without LOGIN, it can’t connect directly but can be used for permission grouping.

Group Role A role without LOGIN attribute that groups permissions. You assign group roles to login roles, and the login role inherits the group role’s permissions.

Basic Role/User Creation

Create a Simple User

CREATE ROLE john WITH LOGIN PASSWORD 'secure_password_123';

This creates a role named john that:

  • Can login (WITH LOGIN)
  • Has password secure_password_123
  • Has no special permissions beyond what public provides

To verify it was created:

\du

Output:

                    List of roles
 Role name |                         Attributes
-----------+----------------------------------------------------
 john      |
 postgres  | Superuser, Create role, Create DB, Replication

Create a Superuser

CREATE ROLE admin WITH LOGIN SUPERUSER PASSWORD 'admin_password_456';

The SUPERUSER attribute gives this role unrestricted access.

Create a Role Without Login (Group Role)

CREATE ROLE developers;

This creates a role that can’t login itself but can be used to group permissions. Later, you’d assign this role to login users:

GRANT developers TO john;

Now john has all permissions that the developers role has.

Essential Role Options

WITH LOGIN / WITHOUT LOGIN

-- Can login
CREATE ROLE analyst WITH LOGIN PASSWORD 'password';

-- Cannot login (useful for grouping permissions)
CREATE ROLE data_readers;

PASSWORD & Password Management

CREATE ROLE sarah WITH LOGIN PASSWORD 'initial_password';

Change a password later:

ALTER ROLE sarah WITH PASSWORD 'new_password_789';

Important: PostgreSQL doesn’t store passwords in plain text. They’re encrypted using algorithms like SCRAM-SHA-256.

SUPERUSER / NOSUPERUSER

-- Unrestricted access
CREATE ROLE admin WITH SUPERUSER;

-- Normal user (default)
CREATE ROLE analyst WITH NOSUPERUSER;

The NOSUPERUSER is implicit if you don’t specify—it’s the default.

CREATEDB / NOCREATEDB

-- Can create databases
CREATE ROLE dba WITH LOGIN CREATEDB PASSWORD 'password';

-- Cannot create databases (default)
CREATE ROLE analyst WITH LOGIN NOCREATEDB PASSWORD 'password';

CREATEROLE / NOCREATEROLE

-- Can create other roles
CREATE ROLE db_admin WITH LOGIN CREATEROLE PASSWORD 'password';

-- Cannot create roles (default)
CREATE ROLE analyst WITH LOGIN NOCREATEROLE PASSWORD 'password';

A role with CREATEROLE can create other roles but can’t grant superuser status.

CONNECTION LIMIT

-- Maximum 10 concurrent connections
CREATE ROLE limited_user WITH LOGIN CONNECTION LIMIT 10 PASSWORD 'password';

-- Unlimited connections (default, specified as -1)
CREATE ROLE unlimited_user WITH LOGIN CONNECTION LIMIT -1 PASSWORD 'password';

This prevents one user from consuming all available connections.

VALID UNTIL

-- Password valid until December 31, 2024
CREATE ROLE temporary_user WITH LOGIN PASSWORD 'password' VALID UNTIL '2024-12-31';

After this date, the user can’t login. Useful for temporary contractors or interns.

Modifying Existing Roles

Rename a Role

ALTER ROLE john RENAME TO john_dev;

Change Password

ALTER ROLE john WITH PASSWORD 'new_password_2024';

Add Permissions

-- Add superuser privileges to existing role
ALTER ROLE john WITH SUPERUSER;

Remove Permissions

-- Remove superuser privileges
ALTER ROLE john WITH NOSUPERUSER;

Role Membership & Group Roles

Group roles are powerful for managing permissions across many users.

Create a Group Role

CREATE ROLE data_team;

Add Members to a Group

GRANT data_team TO john;
GRANT data_team TO sarah;
GRANT data_team TO mike;

Now john, sarah, and mike have all permissions that data_team has.

View Role Membership

\du

Output shows group memberships:

                       List of roles
   Role name    |                         Attributes
----------------+---------------------------------------------------
 data_team      |
 john           | Member of: data_team
 postgres       | Superuser, Create role, Create DB, Replication
 sarah          | Member of: data_team

Remove a Role from a Group

REVOKE data_team FROM john;

Grant Permissions on Databases

Grant All Database Permissions

GRANT ALL PRIVILEGES ON DATABASE learning_db TO john;

This gives john full permissions on the learning_db database.

Grant Usage Only

GRANT USAGE ON DATABASE learning_db TO analyst;

This allows analyst to connect to the database but doesn’t grant permissions on specific objects within it.

Grant Connect Permission

GRANT CONNECT ON DATABASE learning_db TO contractor;

Allows connecting to the database.

Grant Permissions on Tables

Grant Select (Query)

GRANT SELECT ON employees TO analyst;

The analyst role can now query the employees table.

Grant Insert

GRANT INSERT ON employees TO data_entry_clerk;

Allow inserting new rows.

Grant Update

GRANT UPDATE ON employees TO hr_manager;

Allow modifying existing rows.

Grant Delete

GRANT DELETE ON employees TO dba;

Allow deleting rows.

Grant All Table Permissions

GRANT ALL PRIVILEGES ON employees TO manager;

Allow all operations (SELECT, INSERT, UPDATE, DELETE).

Grant to All Tables in a Schema

-- Current schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;

-- Future tables added to schema
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analyst;

The second command ensures newly created tables also grant SELECT to analyst.

Real-World Example: Complete Permission Setup

Let’s set up roles for a real application:

-- 1. Create role groups
CREATE ROLE app_users;
CREATE ROLE app_admins;

-- 2. Create specific roles
CREATE ROLE web_app WITH LOGIN PASSWORD 'app_secure_pass_123';
CREATE ROLE mobile_app WITH LOGIN PASSWORD 'app_secure_pass_456';
CREATE ROLE app_admin WITH LOGIN SUPERUSER PASSWORD 'admin_pass_789';

-- 3. Add specific users to groups
GRANT app_users TO web_app;
GRANT app_users TO mobile_app;
GRANT app_admins TO app_admin;

-- 4. Grant database permissions
GRANT CONNECT ON DATABASE learning_db TO app_users;
GRANT CONNECT ON DATABASE learning_db TO app_admins;

-- 5. Grant table permissions to the group
GRANT SELECT, INSERT, UPDATE ON employees TO app_users;
GRANT SELECT ON employees TO app_users;
GRANT ALL PRIVILEGES ON employees TO app_admins;

-- 6. Grant all current and future table permissions
ALTER DEFAULT PRIVILEGES FOR ROLE app_admin IN SCHEMA public
    GRANT SELECT ON TABLES TO app_users;

Now both web_app and mobile_app have identical permissions through the app_users group.

Viewing Current Permissions

See What Permissions a User Has

-- What can 'analyst' role do on the 'employees' table?
SELECT grantee, privilege_type
FROM table_privileges
WHERE table_name='employees' AND grantee='analyst';

See All Permissions on a Table

SELECT grantee, privilege_type
FROM table_privileges
WHERE table_name='employees'
ORDER BY grantee;

Output:

 grantee  | privilege_type
----------+----------------
 john     | SELECT
 john     | INSERT
 sarah    | SELECT
 postgres | ALL
(4 rows)

Removing Roles and Permissions

Revoke Permission

-- Remove SELECT on employees from analyst
REVOKE SELECT ON employees FROM analyst;

-- Remove all permissions on table
REVOKE ALL PRIVILEGES ON employees FROM analyst;

Remove User from Group

REVOKE data_team FROM john;

Drop a Role

DROP ROLE john;

If the role owns objects or has permissions, you’ll get an error. You must revoke all permissions first:

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM john;
DROP ROLE john;

If the role can’t be deleted, use CASCADE:

DROP ROLE john CASCADE;

Common Errors & Solutions

Error: “role ‘john’ already exists”

Problem: You’re trying to create a role that already exists.

Solution: Drop the existing role or use a different name:

DROP ROLE john;
CREATE ROLE john WITH LOGIN PASSWORD 'password';

Or check if it exists first:

-- Use ALTER if you want to modify, not CREATE
ALTER ROLE john WITH PASSWORD 'new_password';

Error: “permission denied for database learning_db”

Problem: The role doesn’t have permission to connect to the database.

Solution: Grant CONNECT permission:

GRANT CONNECT ON DATABASE learning_db TO john;

Error: “must be superuser to create superusers”

Problem: Only superusers can create other superusers.

Solution: Connect as the postgres superuser, not a regular user:

psql -U postgres

Then create the superuser.

Error: “role ‘john’ cannot be dropped because some objects depend on it”

Problem: The role owns tables or other database objects.

Solution: Transfer ownership first:

REASSIGN OWNED BY john TO postgres;
DROP OWNED BY john;
DROP ROLE john;

Performance Tips

Use Group Roles for Consistency Instead of granting permissions individually to each user, create a group role and add users to it. This prevents permissions from getting out of sync.

-- Good: Everyone in the group has identical permissions
CREATE ROLE analysts;
GRANT SELECT ON employees TO analysts;
GRANT analysts TO john;
GRANT analysts TO sarah;

Limit Superuser Usage The postgres superuser is powerful but slow (PostgreSQL can’t optimize queries for superusers). Use specific roles with limited permissions for applications.

Use CONNECTION LIMIT Prevent runaway applications from using all available connections:

CREATE ROLE app_user WITH LOGIN CONNECTION LIMIT 5;

Set VALID UNTIL for Temporary Access Temporary contractors or test accounts automatically expire:

CREATE ROLE temp_developer WITH LOGIN PASSWORD 'password' VALID UNTIL '2024-06-30';

FAQ

Q: What’s the difference between a user and a role? A: In PostgreSQL 10+, they’re the same. A “user” is technically a role with LOGIN attribute. The terms are used interchangeably.

Q: Should I use the postgres superuser for everything? A: No. Use it only for administration tasks. Create application-specific roles with limited permissions for security and performance.

Q: How do I prevent someone from changing their password? A: Only superusers can change passwords. A normal user can change their own password with ALTER ROLE name WITH PASSWORD 'new_password' but can’t change others’ passwords without superuser privileges.

Q: Can a role have multiple passwords? A: No. A role has exactly one password. When you set a new password, it replaces the old one.

Q: How do I know what permissions I have? A: Query the information_schema.role_table_grants view:

SELECT * FROM information_schema.role_table_grants WHERE grantee = 'john';

Q: Can I grant permissions across multiple tables at once? A: Yes, using a wildcard approach or ALTER DEFAULT PRIVILEGES:

-- Current tables in schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;

-- Future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analyst;

Next Steps

You now understand how to create roles and manage basic permissions. The next step is understanding authentication mechanisms—how PostgreSQL decides whether to trust a connection.

Check out the PostgreSQL Basic Authentication guide to learn about pg_hba.conf and connection authentication.

Ready to write queries? Head to the PostgreSQL SELECT Statement Complete Guide.