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

  • Database
  • Schema
  • Table
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.

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

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:

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:

DROP ROLE username;
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.

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

DROP USER username;
  • username: name of the role to drop

​
Listing Roles

To list roles in Oxla, execute the following query:

SELECT id, name, user_kind FROM oxla_internal.oxla_role;

Example output showing role IDs, names and user kinds:

 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.

Modifying a role does not affect privileges or ownership.

​
Changing password

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

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

ALTER ROLE username [ WITH ] SUPERUSER;

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

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:

ALTER ROLE username [ WITH ] SUPERUSER PASSWORD 'new_password';