How to Create a Postgres User

Introduction

PostgreSQL handles access control through privileged access management, an effective way to secure any database type. Users create and manage PostgreSQL user accounts with the createuser client utility and the native psql interface.

This tutorial shows how to create and set up different user types for a PostgreSQL database.

Prerequisites

• PostgreSQL installed (follow our guide for Ubuntu or Windows).
• Command-line tool access with sudo privileges.
• Access to the postgres superuser.

Create a New User in PostgreSQL

Creating a new user in PostgreSQL requires access to the postgres user. The postgres user is a superadmin created automatically during a PostgreSQL installation.

The sections below provide steps for creating users in PostgreSQL via the createuser command and the psql command-line interface.

Method 1: Using createuser Client Utility

The client utility method enables user management without the need to connect to psql. To create a user, run the following command in the terminal:

sudo -u postgres createuser [name]

If the operation is successful, the terminal does not print an output. To show the server message, add the -e tag:

sudo -u postgres createuser -e [name]

For example:

sudo -u postgres createuser -e john

Alternatively, split the command into two parts:

1. Switch to the postgres user:

sudo su - postgres

2. Run the createuser command:

createuser [name]

In both cases, PostgreSQL automatically creates the user (i.e., the role with login permissions).

Method 2: Using psql

Another way to create a new user in PostgreSQL is through the interactive psql shell.

1. Switch to the postgres user and start the interactive terminal with:

sudo -u postgres psql

The prompt changes to postgres=# to indicate a successful connection to the Postgres shell.

2. Use the following statement to create a user:

CREATE USER [name];

For example:

CREATE USER mary;

Running the command prints CREATE ROLE to the console. The reason is that the CREATE USER query is an alias for the following command:

CREATE ROLE [name] WITH LOGIN;

Both queries yield the same result.

Create Superuser in PostgreSQL

To create a superuser in PostgreSQL, you must have the superuser role.

The following are the two methods to make a superuser in PostgreSQL:

Method 1: The createuser Command

Create a superuser role through the client utility by adding the --superuser tag:

sudo -u postgres createuser --superuser [name]

Or use the shorthand tag -s instead of --superuser:

sudo -u postgres createuser -s [name]

The terminal outputs a message if an error occurs or the user already exists. If the operation is successful, no message appears.

Method 2: The psql Interactive Shell

Alternatively, create a superuser with the following CREATE USER statement in psql:

CREATE USER [name] SUPERUSER;

CREATE USER is an alias for the following statement:

CREATE ROLE [name] LOGIN SUPERUSER;

The CREATE ROLE statement requires adding the LOGIN permission to emulate a user.

Create a Password for a User

Every database user must have a strong password to prevent brute-force attacks. PostgreSQL offers two methods to create a user with a password.

Method 1: The createuser Command

Use the createuser command and add the --pwprompt option to invoke a password creation prompt automatically:

sudo -u postgres createuser [name] --pwprompt

The shorthand version is the -P tag:

sudo -u postgres createuser [name] -P

The terminal prompts the user to enter a password twice. The password is encrypted and hidden when communicating with the server.

Method 2: The psql Interactive Shell

Use psql to create a user with a password:

CREATE USER [name] WITH PASSWORD '[password]';

If the user already exists, add the password using the ALTER USER statement:

ALTER USER [name] WITH PASSWORD '[password]';

Password management via psql comes with three security vulnerabilities:

• The password is visible on the screen.
• Viewing the command history exposes the password.
• The information is transmitted as clear text without any encryption.

Use this method with caution.

Grant Privileges to a User

By default, new users do not have any privileges except for the ability to log in. To add privileges when creating a user, run the createuser client utility in the following format:

createuser [option] [name]

To do the same in psql, run:

CREATE USER [name] WITH [option];

Below is a table with commonly used options for both methods.

Option Syntax	psql	Explanation
-s --superuser	SUPERUSER	Add the superuser privilege.
-S --no-superuser	NOSUPERUSER	No superuser privilege (default).
-d --createdb	CREATEDB	Allows the user to create databases.
-D --no-createdb	NOCREATEDB	Not allowed to create databases (default).
-r --createrole	CREATEROLE	Allows the user to make new roles.
-R --no-createrole	NOCREATEROLE	Not allowed to create roles (default).
-i --inherit	INHERIT	Automatically inherit the privileges of roles (default).
-I --no-inherit	NOINHERIT	Do not inherit privileges of roles.
-l --login	LOGIN	Allows the user to log into a session with the role name (default).
-L --no-login	NOLOGIN	Not allowed to log into a session with the role name.
--replication	REPLICATION	Allows initiating streaming replication and activating/deactivating backup mode.
--no-replication	NOREPLICATION	Not allowed to initiate streaming replication or backup mode (default).
-P --pwprompt	PASSWORD '[password]'	Initiates password creation prompt or adds provided password to the user. Avoid using this option to create a passwordless user.
/	PASSWORD NULL	Specifically sets the password to null. Every password authentication fails for this user.
-c [number] --connection-limit=[number]	CONNECTION LIMIT [number]	Sets the maximum number of connections for a user. Default is without limit.

The following example creates a user that will be able to create roles and databases:

sudo -u postgres createuser -d -r -e [name]

Below is the psql equivalent of the command above:

CREATE USER [name] WITH CREATEROLE CREATEDB;

In both cases, the specified privileges are granted automatically to the new user.

Create a PostgreSQL User Interactively

The interactive user creation is a practical option available only for the client utility. To create a user interactively, run the following command:

sudo -u postgres createuser --interactive

The command automatically prompts a question series, asking the following:

• The name of the role.
• Whether the role is a superuser.
• If the user is allowed to create databases.
• If the user is allowed to create other roles.

Answer yes (y) to the superuser question to add the "create database" and "create role" privileges and end the prompt.

List All Users in PostgreSQL

Listing all the users with their respective roles and privileges is essential for database user management.

To list all users present in a PostgreSQL deployment, do the following:

1. Connect to the psql prompt as the postgres user:

sudo -u postgres psql

2. List all the users with the following command:

\du

The output shows a table with the role names, attributes (privileges), and user groups. To display the description for each role, add the plus (+) sign:

\du+

The output shows an additional column with the role description where applicable.

Conclusion

After reviewing the examples in this guide, you should know how user and role management works in PostgreSQL.

Next, learn about how Database-as-a-Service can help improve database management productivity.