Skip to content
DBAExperts
PostgreSQL Users & permissions

How to create a user and grant permissions in PostgreSQL with GRANT

In PostgreSQL, users are roles with login. Granting access to a table is not enough: you have to grant permission on the schema and account for objects that will be created in the future.

  1. 1

    Create the role with a password

    A role with LOGIN is what we call a user. Set a strong password and, if applicable, a connection limit.

    CREATE ROLE app_lectura WITH LOGIN PASSWORD 'cambia_esto' CONNECTION LIMIT 20;
  2. 2

    Grant access to the database and the schema

    Without USAGE on the schema, the role cannot see the tables even if it has permissions on them. This is the step people forget most.

    GRANT CONNECT ON DATABASE ventas TO app_lectura;
GRANT USAGE ON SCHEMA public TO app_lectura;
  3. 3

    Grant permissions on the current tables

    GRANT on ALL TABLES applies only to the tables that already exist in the schema at that moment.

    GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_lectura;
  4. 4

    Cover future tables with default privileges

    ALTER DEFAULT PRIVILEGES ensures that tables created later also inherit the permission, without having to repeat the GRANT.

    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_lectura;

// common mistake

Granting on the existing tables and forgetting ALTER DEFAULT PRIVILEGES: the moment someone creates a new table, the user can no longer read it and an intermittent, hard-to-trace error appears.

// when it's worth an expert

If you manage several teams, schemas, and nested roles, the permission model becomes easy to break and hard to audit. At dba.mx we design least-privilege role schemes and leave them documented so access is clear and secure, at a fixed price.

Book an assessment — from USD $550

This guide is for reference and uses example commands. In production, adapt to your version and test in a safe environment first.