How to create a PostgreSQL user and grant permissions

Topic: Databases core

Create a PostgreSQL role (user) with CREATE USER or CREATE ROLE; grant privileges with GRANT on databases, schemas, and tables. Use LOGIN for interactive users and NOLOGIN for app roles. Use this when onboarding a new user or service account or when implementing least privilege.

Intent: How-to

Quick answer

  • CREATE USER appuser WITH PASSWORD 'secret' LOGIN; or CREATE ROLE appuser WITH LOGIN PASSWORD 'secret'. For app-only: NOLOGIN and grant to a LOGIN role that the app uses.
  • Grant access: GRANT CONNECT ON DATABASE mydb TO appuser; GRANT USAGE ON SCHEMA public TO appuser; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO appuser.
  • Restrict to read-only: GRANT SELECT ONLY. Use REVOKE to remove; list roles with \du in psql. Store passwords in a secret manager; avoid granting SUPERUSER or CREATEDB unless required.

Prerequisites

Steps

  1. Create the role

    psql as superuser: CREATE USER myuser WITH PASSWORD 'x' LOGIN; or CREATE ROLE myuser WITH LOGIN PASSWORD 'x'. Use NOLOGIN for roles that are granted to other roles (e.g. group role).

  2. Grant database and schema

    GRANT CONNECT ON DATABASE mydb TO myuser; GRANT USAGE ON SCHEMA public TO myuser; (repeat for other schemas). Without USAGE on schema, the user cannot see or use objects in that schema.

  3. Grant table and sequence privileges

    GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO myuser; GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO myuser. For future tables: ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ... TO myuser.

  4. Verify and secure

    Connect as the user: psql -U myuser -d mydb -h localhost. Run expected queries; confirm denied operations fail. Store password in a vault; do not use SUPERUSER for apps.

Summary

Create a role with CREATE USER or CREATE ROLE; grant CONNECT, USAGE on schema, and table/sequence privileges. Use default privileges for future objects. Use this to add users and enforce least privilege.

Prerequisites

Steps

Step 1: Create the role

Create a LOGIN role with a password; use NOLOGIN for group roles.

Step 2: Grant database and schema

Grant CONNECT on the database and USAGE on each schema the user needs.

Step 3: Grant table and sequence privileges

Grant object-level privileges (SELECT, INSERT, etc.); use ALTER DEFAULT PRIVILEGES for future tables.

Step 4: Verify and secure

Connect as the user and test; store passwords in a vault; avoid SUPERUSER for applications.

Verification

  • User can connect and perform allowed operations; denied operations return permission errors.

Troubleshooting

Permission denied — Grant USAGE on schema and the required privilege on the object. Cannot connect — Check pg_hba.conf and listen_addresses; ensure password is correct.

Next steps

Continue to