How to create roles in PostgreSQL

A PostgreSQL application should not run as the cluster owner or a shared administrator account. A dedicated role gives the service its own login name, password policy, connection limit, and membership record before any database, schema, or table privileges are granted.

PostgreSQL uses one role system for users and groups. A role with LOGIN can authenticate, while a role without LOGIN is commonly used as a group that receives grants once and then passes those privileges to member roles. CREATE ROLE is the SQL primitive, and createuser is the command-line wrapper that creates the same database roles from a shell.

Role creation requires a superuser or a role with CREATEROLE. Creating the role is only the account step: pg_hba.conf, database CONNECT privileges, schema USAGE, object grants, and default privileges still decide what the role can reach after it logs in. Avoid SUPERUSER, CREATEDB, and CREATEROLE for application roles unless that elevated capability is the explicit job.

1. Confirm the role shape before creating it.

   The examples below create appuser as a login role with a password prompt, a two-session connection limit, and no database-creation, role-creation, or superuser attributes. They also create app_readonly as a no-login group role for shared grants.

2. Create the login role with a password prompt.

   $ sudo -u postgres createuser --login --pwprompt --connection-limit=2 --no-superuser --no-createdb --no-createrole appuser
   Enter password for new role: 
   Enter it again: 

   createuser creates a PostgreSQL role through the same role system as CREATE ROLE. The explicit negative options keep the command readable in reviews even though those attributes are defaults on current PostgreSQL releases.

   Use --pwprompt or psql's \password command instead of putting PASSWORD '...' in a shell command, where it can appear in shell history, process listings, client logs, or provisioning output.

3. Create a no-login group role when several login roles should share the same grants.

   $ sudo -u postgres psql --command="CREATE ROLE app_readonly NOLOGIN;"
   CREATE ROLE

   A no-login role cannot authenticate by itself. Use it as a grant target, then add login roles as members.

4. Add the login role to the group role.

   $ sudo -u postgres psql --command="GRANT app_readonly TO appuser;"
   GRANT ROLE

   Membership alone does not grant database, schema, table, sequence, or routine access. Grant those privileges to app_readonly or appuser after the account structure exists.

   Related: How to grant privileges in PostgreSQL

5. Verify the role attributes in the system catalog.

   $ sudo -u postgres psql --command="SELECT rolname AS role, rolcanlogin AS can_login, rolsuper AS superuser, rolcreatedb AS create_db, rolcreaterole AS create_role, rolconnlimit AS conn_limit FROM pg_roles WHERE rolname IN ('appuser','app_readonly') ORDER BY rolname;"
        role     | can_login | superuser | create_db | create_role | conn_limit 
   --------------+-----------+-----------+-----------+-------------+------------
    app_readonly | f         | f         | f         | f           |         -1
    appuser      | t         | f         | f         | f           |          2
   (2 rows)

6. Verify the login role's group membership.

   $ sudo -u postgres psql --command="SELECT member.rolname AS member, role.rolname AS member_of FROM pg_auth_members m JOIN pg_roles role ON role.oid = m.roleid JOIN pg_roles member ON member.oid = m.member WHERE member.rolname = 'appuser';"
    member  |  member_of   
   ---------+--------------
    appuser | app_readonly
   (1 row)

7. Test login from the client path the application will use when password authentication should work.

   $ psql --host=db.example.net --username=appuser --dbname=postgres --command="SELECT current_user;"
    current_user 
   --------------
    appuser
   (1 row)

   A failed login test can be caused by pg_hba.conf, network access, TLS policy, database CONNECT privileges, or managed-service authentication rules even when the role exists.