How To Create User Role In Postgresql

In PostgreSQL, all is built around the concept of role.

When first installing PostgreSQL on macOS, the script created a role with your macOS username, with a list of permissions granted.

There are no users in PostgreSQL, just roles.

By running psql postgres in your terminal, you'll automatically login with your macOS username to PostgreSQL, therefore accessing the role created.

In my case the flaviocopes role was created, and I can see it by using the \du command:

See? I have the following roles attributes by default:

Superuser
Create role
Create DB
Replication
Bypass RLS

and I'm not a member of any other role (more on this later)

Creating a new role

A new role is created using the CREATE ROLE command:

For example:

We got a new role, with the Cannot login role attribute. Our newly created user will not be able to login.

You can try by typing the \q command, and then psql postgres -U testing, but you'll see this error:

To fix this problem we must add the LOGIN role attribute at creation:

                          CREATE              ROLE              <              role              >              WITH              LOGIN;

If we remove that role using:

and add WITH LOGIN this time:

                          DROP              ROLE              testing;              CREATE              ROLE              testing              WITH              LOGIN;

We can see that the testing role can login, because we don't have the Cannot login role attribute this time:

Try by adding the command \q to quit, and then psql postgres -U testing:

Notice that the prompt changed from =# to => because we don't have the Superuser role attribute now.

Adding a password to a role

In the previous CREATE ROLE command we created a role without password. Of course it's very important to have (secure) passwords. You can add a password by using the PASSWORD keyword:

                          CREATE              ROLE              <              role              >              WITH              LOGIN PASSWORD              '<password>';

CREATE USER

An alternative way to define roles with the LOGIN attribute automatically added (effectively creating users that can login) is to use CREATE USER:

                          CREATE              USER              <              role              >              PASSWORD              '<password>';

Adding a role attribute to a role

A role attribute can be added later on to a role using the ALTER ROLE command.

Let's suppose we created a role without the LOGIN attribute:

                          CREATE              ROLE              <username>              PASSWORD              '<password>';

We can add it using:

                          ALTER              ROLE              <              role              >              WITH              LOGIN;

Built-in role attributes

We saw the LOGIN role attribute already, to allow a role to login.

But what are other built-in role attributes we can use?

LOGIN / NOLOGIN: allow (or not) to login to PostgreSQL
SUPERUSER / NOSUPERUSER: allow (or not) superuser permissions. A database superuser will bypass other permission checks, except for LOGIN (it must be granted separately).
CREATEDB / NOCREATEDB: allow (or not) the ability to create new databases
CREATEROLE / NOCREATEROLE: allow (or not) the ability to create new roles
CREATEUSER / NOCREATEUSER: allow (or not) the ability to create new users
INHERIT / NOINHERIT: allow (or not) the ability to make the privileges inheritable
REPLICATION / NOREPLICATION: grant (or not) replication permissions (an advanced topic we'll not cover)

Group roles

In PostgreSQL, there are no groups of users.

Instead you can create roles with certain permissions, and then grant those roles to other roles.

Roles will inherit the permissions of roles granted to them, if those roles have the INHERIT attribute.

Create a group role

To create a group role, type

The syntax is the same as creating a role.

Once the group role is created, you can add roles to the group role using GRANT:

                          GRANT              <groupname>              TO              <              role              >

For example, we can create a flavio user role, a "employee" group role, and assign the user to the group role:

                          CREATE              USER              flavio PASSWORD              'superSecret123$';              CREATE              ROLE              employee;              GRANT              employee              TO              flavio;

You can remove a role from a group role using:

                          REVOKE              <groupname>              FROM              <username>

Example:

                          REVOKE              employee              FROM              flavio;

Group role attributes

By default, adding a role to a group role will not make the role inherit attributes (permissions) from the group role.

You need to create the group role with the INHERIT attribute.

Suppose you create the employee group role, and assign it the CREATEDB attribute:

                          CREATE              ROLE              employee              WITH              CREATEDB              INHERIT;

Now create a new role using INHERIT:

                          CREATE              ROLE              flavio;              GRANT              employee              TO              flavio;