PostgreSQL CREATE ROLE Statement

Summary: In this tutorial, you’ll learn how to create roles with various options using the PostgreSQL CREATE ROLE statement.

Understanding PostgreSQL roles #

In PostgreSQL, a role is an entity that owns database objects such as tables and views. A role has privileges.

A role is like a user in other database systems. Additionally, PostgreSQL uses roles for both users and groups.

Roles act like users if they have the LOGIN privilege. Roles that serve as group (or group role) do not have LOGIN privileges.

Basic PostgreSQL CREATE ROLE Statement #

You can create a new role using the CREATE ROLE statement:

CREATE ROLE role_name [WITH option1 option2 ...];Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, specify the name of the role after the CREATE ROLE keyword.
• Second, list the role’s privileges and attributes in the WITH clause.

Creating a role with LOGIN privilege #

The following example uses the CREATE ROLE statement to create a role with the LOGIN privilege:

CREATE ROLE bob
WITH LOGIN PASSWORD 'securepassword';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

You need to replace the securepassword word with your password.

The statement creates a role with the name bob that can log in and has a password.

You can use the role bob to log in to the PostgreSQL server and connect to the postgres database using the psql tool as follows:

psql -U bob -d postgresCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

It’ll prompt you to enter a password for bob; use the password you specified when you created the role.

Postgres Superuser #

PostgreSQL comes with a default superuser role named postgres.

The postgres superuser role has full administrative privilege over the entire PostgreSQL server. It means that it can do anything in the database.

The postgres superuser bypasses all privilege checks. It can access any database even if it doesn’t own them.

The postgres superuser owns the default database postgres.

Typically, you should use the postgres superuser for initial setup and maintenance:

• Creating other roles.
• Managing privileges.
• Performing backups and restores.
• Installing extensions.

It’s best practice not to use the postgres superuser for daily operations. Instead, you should create separate roles with limited privileges and grant only necessary permissions as needed.

Creating superuser roles #

The following example creates a superuser role named admin:

CREATE ROLE admin
WITH LOGIN PASSWORD 'securepassword'
SUPERUSER;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The admin works like the postgres superuser role, except you can drop it like a regular role, whereas you cannot drop the postgres role.

You create a new superuser role to limit access to the postgres role. However, if you only need a superuser role occasionally, use the postgres user.

Creating roles with a validity period #

When creating a new role, you can use the VALID UNTIL option to set the password expiration date.

For example, the following CREATE ROLE statement creates a new role with the password expired on March 7, 2025:

CREATE ROLE alice
WITH LOGIN
PASSWORD 'securepassword'
SUPERUSER
VALID UNTIL '2025-03-07';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The password will expire on March 7, 2025. It means that authentication will fail after March 7, 2025.

Creating roles with limited concurrent connections #

PostgreSQL allows you to create roles with limited concurrent connections using the CONNECTION LIMIT attribute.

For example, the following statement creates a role named api with 99 concurrent connections:

CREATE ROLE api
LOGIN PASSWORD 'securepassword'
CONNECTION LIMIT 99;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Creating roles with database creation privileges #

When creating a role, you can include privileges such as CREATEDB. For example, the following statement creates a role called dbc:

CREATE ROLE dbc
CREATEDB
LOGIN PASSWORD 'securepassword';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

The role dbc can log in with a password and create new databases.

The dbc becomes the owner of any database it creates. As the owner, the role dbc has complete control over those databases, such as creating tables and modifying data.

Displaying role attributes #

To display attributes of all roles in psql, you use the \du command:

\duCode language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Output:

 Role name |                         Attributes
-----------+------------------------------------------------------------
 alice     | Superuser                                                 +
           | Password valid until 2025-03-07 00:00:00-07
 api       | 99 connections
 bob       |
 dbc       | Create DB
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS

The output shows the role name and attributes of each.

Summary #

• A role serves as a user that has privileges.
• Use the CREATE ROLE statement to create a new role.