Roles
Roles in Oxla can be used to manage access to the following objects:
- Database
- Schema
- Table
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:
Here’s the breakdown of the above statement:
username
: name of the role you want to createyour_secure_password
: the role’s password (required and cannot be empty)SUPERUSER
/NOSUPERUSER
: sets superuser status; defaults toNOSUPERUSER
WITH
: optional clause, no effect on behaviorLOGIN
: optional clause, included for compatibility; all roles can log in by default
The order of PASSWORD
, SUPERUSER
/ NOSUPERUSER
and LOGIN
is flexible. For example:
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:
Alternatively, one can also execute the following command (PostgreSQL compatibility):
username
: name of the role to drop
Listing Roles
To list roles in Oxla, execute the following query:
Example output showing role IDs, names and user kinds:
Column user_kind
holds one of three possible values:
1
: default superuser2
: regular superuser3
: non-superuser
Modifying Roles
Modify roles using ALTER ROLE
to change passwords or superuser status.
Changing password
Role’s password can be changed either by a superuser or a role itself by executing the code below:
Here’s the breakdown of the above syntax:
username
: name of the role for which you want to change the passwordnew_password
: new password for that roleWITH
: optional clause, no effect on behavior
Promoting to superuser / Demoting to non-superuser
Promote a non-superuser to superuser by executing this query:
Demote a superuser to non-superuser by executing this query:
username
: role name
Changing Password and Superuser Status in One Query
Change password and superuser status simultaneously by executing this query: