# PostgreSQL User Permissions

> Learn how PostgreSQL manages users and permissions through roles, from role attributes like Superuser and Create DB to creating roles with CREATE ROLE.

Author: [Flavio Copes](https://flaviocopes.com/about/) | Published: 2019-12-30 | Topics: [Database](https://flaviocopes.com/tags/database/) | Canonical: https://flaviocopes.com/postgres-user-permissions/

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:

![PostgreSQL \du command output showing flaviocopes role with Superuser, Create role, Create DB, Replication, Bypass RLS attributes](https://flaviocopes.com/images/postgres-user-permissions/Screen_Shot_2019-12-18_at_22.20.41.png)

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:

```sql
CREATE ROLE <role>;
```

For example:

```sql
CREATE ROLE testing;
```

![Terminal showing CREATE ROLE testing command and \du output displaying testing role with Cannot login attribute](https://flaviocopes.com/images/postgres-user-permissions/Screen_Shot_2019-12-18_at_22.23.53.png)

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:

![Terminal error message: psql error could not connect to server FATAL role testing is not permitted to log in](https://flaviocopes.com/images/postgres-user-permissions/Screen_Shot_2019-12-18_at_22.32.01.png)

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

```sql
CREATE ROLE <role> WITH LOGIN;
```

If we remove that role using:

```sql
DROP ROLE <role>;
```

and add `WITH LOGIN` this time:

```sql
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:

![Terminal showing DROP ROLE and CREATE ROLE testing WITH LOGIN commands, \du output shows testing role without Cannot login](https://flaviocopes.com/images/postgres-user-permissions/Screen_Shot_2019-12-18_at_22.26.42.png)

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

![Terminal showing successful login as testing user with postgres=> prompt indicating non-superuser status](https://flaviocopes.com/images/postgres-user-permissions/Screen_Shot_2019-12-18_at_22.30.08.png)

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:

```sql
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`:

```sql
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:

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

We can add it using:

```sql
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

```sql
CREATE ROLE <groupname>;
```

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`:

```sql
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:

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

You can remove a role from a group role using:

```sql
REVOKE <groupname> FROM <username>
```

Example:

```sql
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:

```sql
CREATE ROLE employee WITH CREATEDB INHERIT;
```

Now create a new role using `INHERIT`:

```sql
CREATE ROLE flavio;
GRANT employee TO flavio;
```

![Terminal showing employee role creation and grant commands, \du output displays flavio role as member of employee group](https://flaviocopes.com/images/postgres-user-permissions/Screen_Shot_2019-12-19_at_09.37.22.png)