Create a PostgreSQL Login Role Safely with createuser

Get one flag wrong with createuser and you have handed out a superuser account instead of a plain login. This walks through creating an ordinary, non-admin role with every privilege flag chosen on purpose, then checking the result from SQL. The examples run against PostgreSQL 16.15 on Ubuntu and take about fifteen minutes.

What you need before starting:

• A running PostgreSQL server and the createuser client installed locally.
• A connection account that is a superuser or already has CREATEROLE.
• No Linux root or sudo is normally required: PostgreSQL's own authorisation and connection authentication are what matter here.

The command creates persistent database state, so read the removal step (step 6) before you run anything.

1. Check the installed command

Confirm the client version and option names before copying any example below. These are read-only checks and need no elevated privileges:

$ createuser --version
createuser (PostgreSQL) 16.15 (Ubuntu 16.15-0ubuntu0.24.04.1)
$ command -v createuser
/usr/bin/createuser

• The positional name at the end is the role you are creating.
• -U or --username means the account used to connect to PostgreSQL, not the new role. Mix these up and you either create the role under the wrong connection account or spend ten minutes debugging the wrong permission error.

2. Choose a non-administrator role

Pick a unique name that describes the purpose. This guide uses report_reader_2026. Spelling out every attribute makes a later review much easier, even though most of these are already the PostgreSQL 16.15 defaults:

$ createuser \
    --login \
    --no-superuser \
    --no-createdb \
    --no-createrole \
    --inherit \
    --no-replication \
    --no-bypassrls \
    report_reader_2026

A successful run prints nothing and exits with status 0. Check that explicitly if this is part of a script:

$ printf 'createuser exit status: %s\n' "$?"
createuser exit status: 0

3. Set a password only when password login is intended

The command above creates a login role without asking for a password, which suits authentication handled by a local operating-system mapping, certificates or another method entirely. If this role will actually use password authentication, add --pwprompt instead and type the password at the hidden prompt:

$ createuser --login --no-superuser --no-createdb --no-createrole \
    --inherit --no-replication --no-bypassrls --pwprompt \
    report_reader_2026

4. Verify the role attributes

Run this against a database you can inspect with psql. It reads PostgreSQL's role catalogue and changes nothing. Swap postgres for another accessible database if that one is not present:

$ psql -X -d postgres -Atc \
    "SELECT rolname, rolcanlogin, rolsuper, rolcreatedb, rolcreaterole, rolreplication, rolbypassrls, rolconnlimit FROM pg_roles WHERE rolname = 'report_reader_2026';"
report_reader_2026|t|f|f|f|f|f|-1

• The first seven boolean fields should match the choices you made in step 2.
• A connection limit of -1 means no limit, which is the createuser default.
• No row returned means check your connection options and the role's spelling before creating anything again.
• A duplicate-name error means the role already exists. Inspect it rather than guessing whether an earlier attempt actually succeeded.

To test authentication, connect separately with the role's real host, port and authentication method. A role with rolcanlogin set to true is not automatically a role that can connect: pg_hba.conf, the database name, privileges and the server's listening address still apply.

5. Add membership only for a defined need

Membership grants the privileges of another role under PostgreSQL's inheritance rules, so add it only after identifying that role and reviewing what it can do. Repeat --member-of for each existing role you want to grant:

$ createuser --login --no-superuser --no-createdb --no-createrole \
    --member-of=report_reader report_reader_2026

The existing report_reader role must already exist. Two similarly-named options are not the same thing: --with-member and --with-admin add another existing role as a member of the new role, with the latter also granting membership administration. Treat both as access changes, never as harmless setup flags. The deprecated --role spelling still works as an alias for --member-of, but use the current name in new scripts.

6. Remove a temporary role safely

For the disposable example role used above, remove it only after checking its ownership and dependencies through your normal database change process:

$ dropuser --interactive report_reader_2026
Role "report_reader_2026" will be dropped.
Proceed? (y/n) y