How to Create a Postgres User

December 27, 2023

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.

How to Create a Postgres User

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
postgres create user echo query

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
sudo -u postgres psql terminal output

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;
create user create role output

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.

Warning: A database superuser bypasses all checks, which is dangerous from a security aspect. Use this action carefully and avoid working with a superuser account unless necessary.

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 superuser psql

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.

Note: The createuser utility is preferred for password creation because it is more secure.

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
postgres createuser password prompt

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

Note: Try our free password generator!

Method 2: The psql Interactive Shell

Use psql to create a user with a password:

CREATE USER [name] WITH PASSWORD '[password]';
create user with password psql

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

ALTER USER [name] WITH PASSWORD '[password]';
alter user with password psql

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.

Note: Check out our complete guide to enterprise password management, or learn more about brute force attacks and how they work.

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 SyntaxpsqlExplanation
-s
--superuser
SUPERUSERAdd the superuser privilege.
-S
--no-superuser
NOSUPERUSERNo superuser privilege (default).
-d
--createdb
CREATEDBAllows the user to create databases.
-D
--no-createdb
NOCREATEDBNot allowed to create databases (default).
-r
--createrole
CREATEROLEAllows the user to make new roles.
-R
--no-createrole
NOCREATEROLENot allowed to create roles (default).
-i
--inherit
INHERITAutomatically inherit the privileges of roles (default).
-I
--no-inherit
NOINHERITDo not inherit privileges of roles.
-l
--login
LOGINAllows the user to log into a session with the role name (default).
-L
--no-login
NOLOGINNot allowed to log into a session with the role name.
--replicationREPLICATIONAllows initiating streaming replication and activating/deactivating backup mode.
--no-replicationNOREPLICATIONNot 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 NULLSpecifically 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]
create postgresql user and grant permissions

Below is the psql equivalent of the command above:

CREATE USER [name] WITH CREATEROLE CREATEDB;
create user grant permissions psql

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
create user 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
du command postgres

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+
du command extended postgres

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.

Was this article helpful?
YesNo
Marko Aleksic
Marko Aleksić is a Technical Writer at phoenixNAP. His innate curiosity regarding all things IT, combined with over a decade long background in writing, teaching and working in IT-related fields, led him to technical writing, where he has an opportunity to employ his skills and make technology less daunting to everyone.
Next you should read
How to Create a Database in PostgreSQL
May 26, 2021

This tutorial shows three easy ways to create a database in PostgreSQL. Whether you prefer using...
Read more
How to List All Databases in PostgreSQL
November 23, 2023

Follow this simple tutorial to learn three different ways to list all databases in PostgreSQL.
Read more
PostgreSQL Drop Database with Examples
January 18, 2022

This tutorial teaches you how to remove (drop) a database in PostgreSQL using two different...
Read more
PostgreSQL Data Types
July 29, 2021

As a relational database management system, PostgreSQL offers a variety of data types for users to work...
Read more