How to Create a Postgres User

Introduction

User privilege and privilege access management is a crucial security concept for any database type. PostgreSQL handles access control through roles and privileges. For example, adding the LOGIN permission to a role yields a user. Postgres offers several ways to create and manage user accounts.

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

Prerequisites

• Postgres installed (follow our guide for Ubuntu or Windows).
• Access to the terminal with sudo privileges.
• Access to the postgres user.

Create a New User in PostgreSQL

There are two ways to make a new user in PostgreSQL, and both cases require access to the postgres user.

Method 1: Using The createuser Client Utility

The first way to create a new user is with the createuser client utility. This method avoids connecting to the PSQL command-line interface.

To create the user, run the following command in the terminal:

sudo -u postgres createuser <name>

The terminal does not output a message. To echo 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>

Postgres automatically creates the user (role with login permissions) in both cases.

Method 2: Using PSQL

The second 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 terminal session changes to postgres=#, indicating 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 a Superuser in PostgreSQL

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

There are two ways to make a superuser in PostgreSQL:

1. 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 in case of an error or if the user already exists. If successful, no message appears.

2. Alternatively, use the CREATE USER PSQL statement:

CREATE USER <name> SUPERUSER;

The CREATE USER statement 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 the 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.

1. Use the createuser client utility 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 to enter the password twice. The password itself or the length is encrypted and hidden when communicating with the server.

2. Use PSQL to create a user with a password:

CREATE USER <name> WITH PASSWORD '<password>';

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

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 transmits as clear text without any encryption.

Use this method with caution.

Grant Privileges to the User

By default, new users do not have any privileges except for login. 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 user. Default is without limit.

For example, create a user with create role and database privileges and add the -e tag to echo the results:

sudo -u postgres createuser -d -r -e <name>

Or use the PSQL equivalent:

CREATE USER <name> WITH CREATEROLE CREATEDB;

In both cases, the stated 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:

• 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 automatically to add the "create database" and "create role" privileges and end the prompt.

List All Users in PostgreSQL

An essential tool for user management in databases is listing all the users with their respective roles and privileges.

To list all users in PostgreSQL, 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 the user's 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 going through the examples in this guide, you should know how user and role management works in PostgreSQL.

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