Create a PostgreSQL Login Role Safely with createuser

You will finish with a PostgreSQL role created by the installed createuser command, with its login and privilege settings chosen explicitly, then verify those settings from SQL. The examples use PostgreSQL 16.15 on Ubuntu and create a deliberately ordinary login role, not an administrator.

Allow about fifteen minutes. You need a running PostgreSQL server, the createuser client, and a connection account that is a superuser or has CREATEROLE. You do not normally need Linux root or sudo: PostgreSQL authorisation and connection authentication are the relevant permissions. The command creates persistent database state, so read the removal step before running it.

1. Check the installed command

Confirm the client version and the option names before copying an example. These are read-only checks and do not need 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 to create. -U or --username means the account used to connect to PostgreSQL, not the new role. That distinction is an easy way to create a role under the wrong connection account or to diagnose the wrong permission failure.

2. Choose a non-administrator role

Use a unique name that describes the purpose. This guide uses report_reader_2026. The role will be allowed to log in, but will not be a superuser, database creator, role creator, replication role or row-level-security bypass role. Those are the defaults in PostgreSQL 16.15, but spelling them out makes a review safer:

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

Do not add --superuser, --createrole, --replication or --bypassrls just to make a connection work. Superuser access bypasses database permissions, and the other elevated attributes have broad consequences. PostgreSQL requires a superuser, rather than merely CREATEROLE, when the requested role would have superuser, replication or bypass-RLS privilege.

A successful run normally prints nothing and exits with status 0. Check the status 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 previous command creates a login role without asking for a password. That is useful when authentication is handled by a local operating-system mapping, certificates or another method. If the role will use password authentication, run the creation command with --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

This is security-sensitive input. Do not put the new password in the command line, shell history, a pasted transcript or an article. --pwprompt prompts for the new role's password. The separate --password or -W option prompts for the password of the account connecting to the server, not for the new role.

Do not use --echo while setting a password in a shared terminal or captured log. It shows the generated command sent to the server and can expose details you did not intend to record.

4. Verify the role attributes

Use psql against a database you can inspect. The query reads PostgreSQL's role catalogue and does not change it. Replace postgres with an accessible database if that database 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 above. A connection limit of -1 means no limit, which is the createuser default. If the query returns no row, check the connection options and the spelling of the role before creating anything again. A duplicate-name error means the role already exists; inspect it rather than guessing whether the earlier command succeeded.

To test authentication, use a separate connection with the role's real host, port and authentication method. Do not assume that a role with rolcanlogin set to true 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 according to PostgreSQL's role and inheritance rules. Add it only after identifying the existing role and reviewing what it can do. For ordinary membership, repeat --member-of for each existing role:

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

The existing report_reader role must already exist. The command's --with-member and --with-admin options are different: they add another existing role as a member of the new role, with the latter also granting membership administration. Treat both as access changes, not harmless setup options. The deprecated --role spelling is the same as --member-of; use the current name in new scripts.

6. Remove a temporary role safely

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

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

If the role owns objects, transfer or remove those objects deliberately before retrying. The interactive prompt is a useful guard against deleting the wrong role. There is no general undo for a dropped role, so keep a tested backup and the intended grants before making that change.