> ## Documentation Index
> Fetch the complete documentation index at: https://docs.oxla.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Roles

Roles in Oxla can be used to manage access to the following objects:

* Database
* Schema
* Table

<Note>In Oxla, the terms `role` and `user` are interchangeable and refer to the same concept.
In SQL queries, the keywords `ROLE` and `USER` have identical semantics.</Note>

## Superusers

Oxla includes a default superuser named `oxla`, which always exists and retains superuser rights permanently.
Other users may be created as either superusers or non-superusers.
This status is set during user creation but can be changed later if needed.

Both the default superuser and other superusers have the same permissions.
The key difference is that the default superuser cannot be deleted or downgraded to a non-superuser,
while other superusers do not have this protection.

## Privileges Required for Role Management

All role management operations, such as creating, deleting, listing roles, changing passwords, promoting to superuser
or degrading to non-superuser; require **superuser** privileges.

The only exception is password changes: every role can change its own password.

## Creating Roles

The syntax for role creation is as follows:

```sql theme={null}
CREATE ROLE username [ WITH ] PASSWORD 'your_secure_password' [ SUPERUSER | NOSUPERUSER] [ LOGIN ];
```

Here's the breakdown of the above statement:

* `username`: name of the role you want to create
* `your_secure_password`: the role’s password (required and cannot be empty)
* `SUPERUSER` / `NOSUPERUSER`: sets superuser status; defaults to `NOSUPERUSER`
* `WITH`: optional clause, no effect on behavior
* `LOGIN`: optional clause, included for compatibility; all roles can log in by default

The order of `PASSWORD`, `SUPERUSER` / `NOSUPERUSER` and `LOGIN` is flexible. For example:

```sql theme={null}
CREATE ROLE username WITH LOGIN SUPERUSER PASSWORD 'your_secure_password';
```

## Deleting Roles

Roles can own objects and hold privileges. To delete a role, you must first delete or reassign all owned objects and
revoke any granted privileges (default privileges do not require revocation).

Role can be deleted (dropped) using the following syntax:

```sql theme={null}
DROP ROLE username;
```

<Note>You cannot delete the user you are currently logged in as. However, you can delete other users even if they are currently connected to the database.</Note>

Alternatively, one can also execute the following command (PostgreSQL compatibility):

```sql theme={null}
DROP USER username;
```

* `username`: name of the role to drop

## Listing Roles

To list roles in Oxla, execute the following query:

```sql theme={null}
SELECT id, name, user_kind FROM oxla_internal.oxla_role;
```

Example output showing role IDs, names and user kinds:

```text theme={null}
 id |  name  | user_kind 
----+--------+-----------
  1 | oxla   |         1
  2 | alice  |         2
  3 | bob    |         2
  4 | charlie|         3
```

Column `user_kind` holds one of three possible values:

* `1`: default superuser
* `2`: regular superuser
* `3`: non-superuser

## Modifying Roles

Modify roles using `ALTER ROLE` to change passwords or superuser status.

<Note>Modifying a role does not affect privileges or ownership.</Note>

### Changing password

Role's password can be changed either by a superuser or a role itself by executing the code below:

```sql theme={null}
ALTER ROLE username [ WITH ] PASSWORD 'new_password';
```

Here's the breakdown of the above syntax:

* `username`: name of the role for which you want to change the password, or keyword CURRENT\_ROLE/CURRENT\_USER
* `new_password`: new password for that role
* `WITH`: optional clause, no effect on behavior

### Promoting to superuser / Demoting to non-superuser

Promote a non-superuser to superuser by executing this query:

```sql theme={null}
ALTER ROLE username [ WITH ] SUPERUSER;
```

Demote a superuser to non-superuser by executing this query:

```sql theme={null}
ALTER ROLE username [ WITH ] NOSUPERUSER;
```

* `username`: role name

### Changing Password and Superuser Status in One Query

Change password and superuser status simultaneously by executing this query:

```sql theme={null}
ALTER ROLE username [ WITH ] SUPERUSER PASSWORD 'new_password';
```