How to

Create a New Role in PostgreSQL

If you want to manage a PostgreSQL database you must have a role for accessing the database. That means the right permissions to manage specific databases. And the permission rights are defined in the Role. In this tutorial we will learn how to create a new role in PostgreSQL.

Prerequisites

  • A working PostgreSQL server running on your machine.
  • The psql command is accessible from the terminal/command line.

Start psql CLI

PostgreSQL creates a default user as Postgres and a database as Postgress. As well as the Postgres user has a superuser role that we will see in a few moments.

Let’s start creating new roles in Postgres

$ sudo su - postgres
$ psql
postgres=# \du
                               List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Notice that, only one role is in the list that is postgres. And has a bunch of access and permissions. By default the postgres role is superuser. Now we will create new roles with different permissions.

Creating Roles

The CREATE ROLE role_name; is the most basic syntax for creating new roles in PostgreSQl. We can also pass additional options for granting specific permission for creating roles. Run the psql CLI and

$ CREATE ROLE user1; #creates without login permission
$ \h CREATE ROLE; #see all the options for creating roles
$ CREATE ROLE user2 WITH LOGIN; #role with login permission
$ CREATE ROLE user3 WITH LOGIN CREATEDB; #createdb and login
$ CREATE USER user4; #same but root login permit by default

Deleting Roles

So you can create roles using those above commands as your need. Now you may want to delete some existing roles. To to that, you can use the below commands:

$ DROP ROLE role_name; # delete a role

Changing Privileges of Roles

You can also alter or change the permission or privileges of an existing role. The most basic syntax for that ALTER ROLE role_name WITH attribute_options;

$ \du
                               List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 user1     | Cannot login                                               | {}
 user2     |                                                            | {}
 user3     | Create DB                                                  | {}
 user4     |                                                            | {}

Now from the existing role we want to add Create DB privileges to user4. So before adding any privileges we want to see all the available privileges by typing

$ \h CREATE ROLE
Command:     CREATE ROLE
Description: define a new database role
Syntax:
CREATE ROLE name [ [ WITH ] option [ … ] ]
where option can be:
  SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password'
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid

From the list, we will add Create DB permission on line 7 to user4. Just run the command;

$ ALTER ROLE user4 WITH CREATEDB;

Now you can see the new privilege is added to user4 by running $ \du again:

                               List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 user1     | Cannot login                                               | {}
 user2     |                                                            | {}
 user3     | Create DB                                                  | {}
 user4     | Create DB                                                           | {}

Similarly you can:

$ ALTER ROLE user3 WITH NOCREATEDB;
$ ALTER ROLE user2 WITH SUPERUSER;

Posted in