How to create roles in PostgreSQL

Creating dedicated roles in PostgreSQL supports least-privilege access by separating application credentials from administrative accounts, reducing the blast radius of a leaked password or compromised service account.

PostgreSQL models both users and groups as roles, where attributes such as LOGIN control whether a role can authenticate, and role membership allows permissions to be assigned once and reused across multiple accounts. Roles are created and managed with SQL statements such as CREATE ROLE and ALTER ROLE, and privileges are granted at the database, schema, and object levels.

Role creation typically requires superuser-level access (or a role with sufficient privilege) and is commonly performed locally as the operating-system postgres account. Password handling and authentication are influenced by the server’s pg_hba.conf rules, so role credentials alone do not guarantee password-based logins will work from every host. Avoid granting SUPERUSER (or broad privileges) to application roles, and prefer explicit, minimal permissions that can be audited and rotated safely.

1. Create an application role with login enabled.

   $ sudo -u postgres psql
   DO $$
   BEGIN
     IF NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'appuser') THEN
       CREATE ROLE appuser LOGIN;
     END IF;
   END$$;
   DO

   Use CREATE ROLE appuser LOGIN; when the role does not already exist, or wrap it in a conditional block for repeatable automation.

2. Set the role password using SQL.

   $ sudo -u postgres psql -c "ALTER ROLE appuser PASSWORD 'S3curePass!2025';"
   ALTER ROLE

   Passwords embedded directly in commands (for example PASSWORD '... ') can be exposed via shell history, process listings, or provisioning logs.

3. Create the application database when a dedicated database is required.

   $ sudo -u postgres createdb --owner=appuser appdb

4. Revoke default database access from PUBLIC when access must be restricted.

   $ sudo -u postgres psql -c "REVOKE CONNECT ON DATABASE appdb FROM PUBLIC;"
   REVOKE

   Revoking CONNECT from PUBLIC blocks all roles without an explicit grant, which can break existing users and automation targeting the same database.

5. Grant database connection access to the application role.

   $ sudo -u postgres psql -c "GRANT CONNECT ON DATABASE appdb TO appuser;"
   GRANT

6. Apply an optional connection limit to reduce runaway sessions.

   $ sudo -u postgres psql -c "ALTER ROLE appuser CONNECTION LIMIT 2;"
   ALTER ROLE

   Use CONNECTION LIMIT -1 to remove the limit.

7. Verify the role attributes in the system catalog.

   $ sudo -u postgres psql -c "SELECT rolname AS role, rolcanlogin AS can_login, rolsuper AS superuser, rolconnlimit AS conn_limit FROM pg_roles WHERE rolname='appuser';"
     role   | can_login | superuser | conn_limit
   ---------+-----------+-----------+------------
    appuser | t         | f         |          2
   (1 row)

8. Verify the database owner when a new database was created.

   $ sudo -u postgres psql -c "SELECT datname AS database, pg_catalog.pg_get_userbyid(datdba) AS owner FROM pg_database WHERE datname='appdb';"
    database | owner
   ----------+--------
    appdb    | appuser
   (1 row)