createuser

Create a user interactively

$ createuser --interactive [username]

$ createuser [username]

$ createuser [[-s|--superuser]] [username]

$ createuser [[-d|--createdb]] [[-r|--createrole]] [[-P|--pwprompt]] [username]

$ createuser [[-D|--no-createdb]] [[-R|--no-createrole]] [username]

createuser [connection-options...] [option...] [rolename]
Example:
createuser --createdb --login new_app_user
createuser -s -P admin_user

-s, --superuser
    The new role will be a database superuser.

-r, --createrole
    The new role will be allowed to create other roles.

-d, --createdb
    The new role will be allowed to create new databases.

-l, --login
    The new role will be allowed to log in (default behavior).

-P, --pwprompt
    Prompts for a password for the new role.

-R, --replication
    The new role will be allowed to initiate replication.

-U username, --username=username
    User name to connect as for the database server.

-h host, --host=host
    Database server host name or socket directory.

-p port, --port=port
    Database server port number.

--if-not-exists
    Do not error if a role with the same name already exists; issue a notice.

The createuser command is a PostgreSQL utility designed to define new database roles, which can represent either database users or groups of users. It is crucial to understand that createuser manages access within a PostgreSQL database cluster and not system-level user accounts on the Linux operating system. This command provides a convenient way to grant various privileges to a new role, such as the ability to log in, create databases, create other roles, or become a superuser, all from the command line. It acts as a wrapper around the SQL CREATE ROLE command. The utility connects to a PostgreSQL database (typically postgres) to execute the role creation. It simplifies the process by abstracting the complex SQL syntax, allowing database administrators to quickly set up new user accounts with specified permissions.

The createuser command is used exclusively for managing roles within a PostgreSQL database and does not create system-level user accounts on the Linux operating system. Attempting to use it for system user management will fail. To use createuser, you must have superuser privileges within the PostgreSQL database or the CREATEROLE privilege. It's generally best practice to assign the minimum necessary privileges to new roles rather than granting superuser status indiscriminately.

A role created with createuser can have various attributes determining its capabilities. These include whether it can log in, create databases, create other roles, or initiate replication. Specific SQL commands like GRANT and REVOKE are used to manage object-level privileges (e.g., SELECT on a table) for these roles after creation.

Always use strong, unique passwords for database roles. Avoid granting superuser privileges unless absolutely necessary. For production environments, consider using client authentication methods like pg_hba.conf to restrict database access based on host, user, and database.

The createuser utility has been a fundamental part of the PostgreSQL toolset since its early versions, providing a command-line interface for database role management. As PostgreSQL evolved, the underlying SQL syntax for role creation became more feature-rich, encompassing various attributes like login status, password settings, and creation privileges. createuser consistently serves as a user-friendly wrapper for these SQL commands, adapting to new role features while maintaining its simple invocation pattern. Its longevity underscores the importance of streamlined database administration within the PostgreSQL ecosystem.

dropuser(1), psql(1), useradd(8) (for creating Linux system users), usermod(8) (for modifying Linux system users)