​
Overview

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

  • Cluster
  • Database
  • Table
  • Schema

In the following sections, you’ll learn how to create, delete (drop), list and change role’s password.

In Oxla, role and user terms are used interchangeably and refer to the same concept. The syntax for both terms is supported throughout the system

​
Creating Roles

The syntax for role creation is as follows:

CREATE ROLE joe [ WITH ] PASSWORD 'joe_password';

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

CREATE ROLE joe [ WITH ] PASSWORD 'joe_password' LOGIN;

Here’s the breakdown of the above code:

  • joe: name of the role you want to create
  • joe_password: password for that role
  • LOGIN: implicit and supported on syntax level for compatibility sake. Every role in Oxla can login.
  • WITH: optional clause

Oxla also supports the CREATE USER syntax for PostgreSQL compatiblity sake:

CREATE USER joe [ WITH ] PASSWORD 'joe_password';
CREATE USER joe [ WITH ] PASSWORD 'joe_password' LOGIN;

​
Deleting Roles

Since roles can own database objects and possess privileges to access other objects, deleting a role requires all objects owned by the role to be either deleted or reassigned to new owners. Additionally, revocation of privileges granted to that role is required (default privileges don’t need to be revoked).

Only the superuser is permitted to delete a role and current user cannot be deleted

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

DROP ROLE joe;

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

DROP USER joe;

In the code above:

  • joe: name of the role you want to drop

​
Listing Roles

To list roles in Oxla, cluster’s superuser shall execute the following code:

SELECT id,name FROM oxla_internal.oxla_role;

Roles’ IDs and their names will be returned in the following format:

 id | name
----+------
  1 | joe
  2 | andrew

​
Password Change

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

ALTER ROLE joe [ WITH ] PASSWORD 'new_password';

Here’s the breakdown of the above syntax:

  • joe: name of the role for which you want to change the password
  • new_password: new password for that role
  • WITH: optional clause
OverviewPrivileges