How to Create a Role in Postgres

Have a Database Problem? Speak with an Expert for Free
Get Started >>

Introduction

If you’re a database administrator working with PostgreSQL, you’ll need to be able to create and manage roles for your cluster. A role can be defined as a database entity that can own objects and have privileges. The CREATE ROLE command can be used to create a new role for a PostgreSQL database. In this article, we’ll take a closer look at the CREATE ROLE command and show you the different ways you can create a role in PostgreSQL.

Prerequisites

In order to get the most out of this tutorial, you’ll need to have a few key prerequisites in place:

  • PostgreSQL must be installed on your device.
  • You’ll need to make sure that the psql command-line interface is configured and working properly.
  • You should have some fundamental knowledge of database management systems, particularly PostgreSQL.

Connect to psql on the command line

Before we create a new role in PostgreSQL, we’ll need to access the command-line interface.

If you don’t have PostgreSQL installed on your UBUNTU system, you can use the following commands to download and install it.

1
2
sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

PostgreSQL will create a default user for you during the installation. We’ll be using this postgres user to log in with the command shown below:

1
sudo su - postgres

To view the current ROLES in your PostgreSQL installation, enter the command-line interface using the following command:

1
psql

Postgres list roles command

To view a list of all the ROLE and USER groups for a Postgres server, you can use the following psql command:

1
\du
1
2
3
4
5
                                   List OF roles
 ROLE name |                         Attributes                         | Member OF
-----------+------------------------------------------------------------+-----------

 postgres  | Superuser, CREATE ROLE, CREATE DB, Replication, Bypass RLS |

The result shown above indicates there is only one default role with many privileges associated with it.

Postgres ‘CREATE ROLE’ command

To create a role in Postgres, use the CREATE ROLE statement followed by the new role name.

The basic syntax for this command can be seen below:

1
CREATE ROLE [ROLE NAME]

Let’s try using this syntax to create a role:

1
CREATE ROLE new_role;

The new_role in the above example is the name of the role created using the CREATE ROLE statement in PostgreSQL.

To verify that our role was created successfully, we’ll use the \du command to view the list of users and roles again:

1
\du

The output should look like this:

1
2
3
4
5
6
                                   List OF roles
 ROLE name |                         Attributes                         | Member OF
-----------+------------------------------------------------------------+-----------

 new_role  | Cannot login                                               | {}
 postgres  | Superuser, CREATE ROLE, CREATE DB, Replication, Bypass RLS | {}

The only information we see about the _newrole is that it has no login privileges.

In our next example, we’ll create a role with an encrypted password:

1
CREATE ROLE record with LOGIN ENCRYPTED PASSWORD '1234';

To verify that this role was created, we’ll use the \du command once again:

1
2
3
4
5
6
7
postgres=# \du
                                   List OF roles
 ROLE name |                         Attributes                         | Member OF
-----------+------------------------------------------------------------+-----------
 new_role  | Cannot login                                               | {}
 postgres  | Superuser, CREATE ROLE, CREATE DB, Replication, Bypass RLS | {}
 record    |                                                            | {}

Create role as a Postgres superuser

If you want to create a role that’s a superuser, you can use the following command:

1
CREATE ROLE superuser WITH SUPERUSER CREATEDB CREATEROLE LOGIN ENCRYPTED PASSWORD '1234';

The superuser immediately after the CREATE ROLE statement is the name of the user we just created.

The example above has a LOGIN attribute and contains a non-empty password. These options are necessary if you plan to use this superuser role for local and remote connections to a database.

Once again, let’s verify that our role was created successfully:

1
2
3
4
5
6
7
8
postgres=# \du

                                   List OF roles
 ROLE name |                         Attributes                         | Member OF
-----------+------------------------------------------------------------+-----------
 new_role  | Cannot login                                               | {}
 postgres  | Superuser, CREATE ROLE, CREATE DB, Replication, Bypass RLS | {}
 superuser | Superuser, CREATE ROLE, CREATE DB                          | {}

We can see that our new superuser is included in the list.

Create role as a Postgres non-superuser

To create a non-superuser role that still has a LOGIN attribute, we can use the following command:

1
createuser -PE user1

You’ll be prompted to enter the password for your new role. Let’s discuss the two flags specified in this command:

-P: This flag prompts the administrator to enter a new password for the user. -E: This flag ensures that the password is encrypted.

Now that we used the createuser command, let’s connect to psql and verify that it worked correctly. We’ll use the \du command:

1
2
3
4
5
                                   List OF roles
 ROLE name |                         Attributes                         | Member OF
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, CREATE ROLE, CREATE DB, Replication, Bypass RLS | {}
 user2     |                                                            | {}

We can see that the createuser command created a new PostgreSQL user or role. To create a new user, you must have the CREATE ROLE privilege and you must be a superuser.

Conclusion

Creating new roles is a key part of PostgreSQL database administration. In this article, we showed you how you can use the CREATE ROLE statement or the createuser command to create a role in Postgres. With our instructions to guide you, you’ll be ready to create and define new roles for your own PostgreSQL database.

Pilot the ObjectRocket Platform Free!

Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.

Get Started

Keep in the know!

Subscribe to our emails and we’ll let you know what’s going on at ObjectRocket. We hate spam and make it easy to unsubscribe.