Authenticate and Authorize Users

ScalarDB Cluster can authenticate and authorize users in a coarse-grained manner. You can create users and grant or revoke their privileges. Roles can also be created to group privileges and can be granted to users or other roles. This guide describes how to use authentication and authorization in ScalarDB SQL. For more details about the grammar, see DCL.

tip

You can also do authentication and authorization by using the primitive interface. For details, see ClusterClientTransactionAdmin, which implements AuthAdmin.

Users

Users can log in to ScalarDB Cluster with a username and a password and execute SQL statements if they have the required privileges.

Authentication and authorization support two types of users:

• Superusers: This type of user has all privileges. Only superusers can create or drop other users and namespaces.
• Normal users: This type of user initially doesn't have any privileges, so they need to be granted privileges by a superuser or another user who has the GRANT privilege.

Initial user

When you enable authentication and authorization, the initial user admin is created and the initial password of that user is admin. This user is a superuser and has all privileges. You can log in with this user and create other users if necessary.

warning

For security purposes, be sure to change the password of the initial user, especially before deploying to a production environment.

Roles

A role is a named collection of privileges that can be granted to users or other roles. Using roles provides a convenient way to manage privileges for multiple users, rather than granting individual privileges to each user.

Only superusers can create or drop roles. Users who have the GRANT privilege can grant their privileges to roles.

When a role is granted to a user, the user can use all privileges granted to that role. If the role has other roles granted to it (role hierarchy), the user can also use the privileges from those roles.

When granting a role, you can optionally specify WITH ADMIN OPTION to allow the grantee to grant the same role to others.

Privileges

The following privileges are available when using authentication and authorization:

• SELECT
• INSERT
• UPDATE
• DELETE
• CREATE
• DROP
• TRUNCATE
• ALTER
• GRANT

Which privileges are required for each type of operation

The following tables show which privileges are required for each type of operation:

DDL

Command	Superuser required	Required privileges
CREATE NAMESPACE	true
DROP NAMESPACE	true
CREATE TABLE		CREATE
DROP TABLE		DROP
CREATE INDEX		CREATE
DROP INDEX		DROP
TRUNCATE TABLE		TRUNCATE
ALTER TABLE		ALTER
CREATE COORDINATOR TABLES	true
DROP COORDINATOR TABLES	true
TRUNCATE COORDINATOR TABLES	true

DML

Command	Superuser required	Required privileges
SELECT		SELECT
INSERT		INSERT
UPSERT		INSERT
UPDATE		SELECT and UPDATE
DELETE		SELECT and DELETE

DCL

Command	Superuser required	Required privileges
CREATE USER	true
ALTER USER	true (Users can change their own password.)
DROP USER	true
GRANT		GRANT (Users can grant only the privileges that they have.)
REVOKE		GRANT (Users can revoke only the privileges that they have.)
CREATE ROLE	true
DROP ROLE	true
GRANT ... TO ROLE		GRANT (Users can grant only the privileges that they have.)
REVOKE ... FROM ROLE		GRANT (Users can revoke only the privileges that they have.)
GRANT ROLE		ADMIN OPTION on the role (Users can grant only those roles.)
REVOKE ROLE		ADMIN OPTION on the role (Users can revoke only those roles.)
REVOKE ADMIN OPTION		ADMIN OPTION on the role (Users can revoke ADMIN OPTION only for those roles.)

Configurations

This section describes the available configurations for authentication and authorization.

ScalarDB Cluster node configurations

To enable authentication and authorization, you need to set scalar.db.cluster.auth.enabled to true.

Name	Description	Default
scalar.db.cluster.auth.enabled	Whether authentication and authorization are enabled.	false

You can also set the following configurations:

Name	Description	Default
scalar.db.cluster.auth.cache_expiration_time_millis	Cache expiration time for authentication and authorization information in milliseconds.	60000 (1 minute)
scalar.db.cluster.auth.auth_token_expiration_time_minutes	Authentication and authorization token expiration time in minutes.	1440 (1 day)
scalar.db.cluster.auth.auth_token_gc_thread_interval_minutes	Authentication and authorization token garbage collection (GC) thread interval in minutes.	360 (6 hours)
scalar.db.cluster.auth.pepper	A secret value added to a password before hashing. If not specified, the password is hashed without pepper.

note

If you enable authentication and authorization, you will also need to set scalar.db.cross_partition_scan.enabled to true for the system namespace (scalardb by default) because authentication and authorization perform cross-partition scans internally.

ScalarDB Cluster Java client SDK configurations

To enable authentication and authorization on the client side, you need to set scalar.db.cluster.auth.enabled to true.

Name	Description	Default
scalar.db.cluster.auth.enabled	Whether authentication and authorization are enabled.	false

In addition to the configuration in the ScalarDB Cluster SQL client configurations section, you also need to set scalar.db.sql.cluster_mode.username and scalar.db.sql.cluster_mode.password to specify the username and password of the client.

Name	Description	Default
scalar.db.sql.cluster_mode.username	The username of the client.
scalar.db.sql.cluster_mode.password	The password of the client.

Limitations

There are some limitations to the privileges granted or revoked in authentication and authorization:

• You must grant or revoke INSERT and UPDATE privileges together.
• To grant a user the UPDATE or DELETE privilege, the target user must have the SELECT privilege.
• If the target user has the INSERT or UPDATE privilege, you cannot revoke the SELECT privilege from them.

Wire encryption

If you enable authentication and authorization, enabling wire encryption to protect the user credentials is strongly recommended, especially in production environments. For details about wire encryption, see Encrypt Wire Communications.

Tutorial - Authenticate and authorize users

This tutorial explains how to use authentication and authorization.

Prerequisites

• OpenJDK LTS version (8, 11, 17, or 21) from Eclipse Temurin
• Docker 20.10 or later with Docker Compose V2 or later

note

This tutorial has been tested with OpenJDK from Eclipse Temurin. ScalarDB itself, however, has been tested with JDK distributions from various vendors. For details about the requirements for ScalarDB, including compatible JDK distributions, please see Requirements.

warning

You need to have a license key (trial license or commercial license) to use ScalarDB Cluster. If you don't have a license key, please contact us.

1. Create the ScalarDB Cluster configuration file

Create the following configuration file as scalardb-cluster-node.properties, replacing <YOUR_LICENSE_KEY> and <LICENSE_CHECK_CERT_PEM> with your ScalarDB license key and license check certificate values. For more information about the license key and certificate, see How to Configure a Product License Key.

scalar.db.storage=jdbc
scalar.db.contact_points=jdbc:postgresql://postgresql:5432/postgres
scalar.db.username=postgres
scalar.db.password=postgres
scalar.db.cluster.node.standalone_mode.enabled=true
scalar.db.cross_partition_scan.enabled=true
scalar.db.sql.enabled=true

# Enable authentication and authorization
scalar.db.cluster.auth.enabled=true

# License key configurations
scalar.db.cluster.node.licensing.license_key=<YOUR_LICENSE_KEY>
scalar.db.cluster.node.licensing.license_check_cert_pem=<LICENSE_CHECK_CERT_PEM>

2. Create the Docker Compose file

Create the following configuration file as docker-compose.yaml.

services:
  postgresql:
    container_name: "postgresql"
    image: "postgres:15"
    ports:
      - 5432:5432
    environment:
      - POSTGRES_PASSWORD=postgres
    healthcheck:
      test: ["CMD-SHELL", "pg_isready || exit 1"]
      interval: 1s
      timeout: 10s
      retries: 60
      start_period: 30s

  scalardb-cluster-standalone:
    container_name: "scalardb-cluster-node"
    image: "ghcr.io/scalar-labs/scalardb-cluster-node-byol-premium:3.17.0"
    ports:
      - 60053:60053
      - 9080:9080
    volumes:
      - ./scalardb-cluster-node.properties:/scalardb-cluster/node/scalardb-cluster-node.properties
    depends_on:
      postgresql:
        condition: service_healthy

3. Start PostgreSQL and ScalarDB Cluster

Run the following command to start PostgreSQL and ScalarDB Cluster in standalone mode.

docker compose up -d

It may take a few minutes for ScalarDB Cluster to fully start.

4. Connect to ScalarDB Cluster

To connect to ScalarDB Cluster, this tutorial uses the SQL CLI, a tool for connecting to ScalarDB Cluster and executing SQL queries. You can download the SQL CLI from the ScalarDB releases page.

Create a configuration file named scalardb-cluster-sql-cli.properties. This file will be used to connect to ScalarDB Cluster by using the SQL CLI.

scalar.db.sql.connection_mode=cluster
scalar.db.sql.cluster_mode.contact_points=indirect:localhost

# Enable authentication and authorization
scalar.db.cluster.auth.enabled=true

Then, start the SQL CLI by running the following command.

java -jar scalardb-cluster-sql-cli-3.17.0-all.jar --config scalardb-cluster-sql-cli.properties

Enter the username and password as admin and admin, respectively.

Now you're ready to use the database with authentication and authorization enabled in ScalarDB Cluster.

5. Create namespaces and a table

Create namespaces.

CREATE NAMESPACE ns1;

CREATE NAMESPACE ns2;

Next, create a table in the ns1 namespaces.

CREATE TABLE ns1.tbl (
  id INT PRIMARY KEY,
  col1 TEXT,
  col2 INT);

6. Create a user

Create a user named user1.

CREATE USER user1 WITH PASSWORD 'user1';

To check the user, run the following command.

SHOW USERS;

+----------+-------------+
| username | isSuperuser |
+----------+-------------+
| user1    | false       |
| admin    | true        |
+----------+-------------+

You can see that the user1 user has been created.

7. Grant privileges

Grant the SELECT, INSERT, and UPDATE privileges to user1 on the ns1.tbl table.

GRANT SELECT, INSERT, UPDATE ON ns1.tbl TO user1;

Then, grant the SELECT privilege to user1 on the ns2 namespace.

GRANT SELECT ON NAMESPACE ns2 TO user1;

To check the privileges, run the following command.

SHOW GRANTS FOR user1;

+---------+-----------+-----------+---------------+-------------------------+
|  name   |   type    | privilege | grantedToUser | rolesProvidingPrivilege |
+---------+-----------+-----------+---------------+-------------------------+
| ns2     | NAMESPACE | SELECT    | true          |                         |
| ns1.tbl | TABLE     | SELECT    | true          |                         |
| ns1.tbl | TABLE     | INSERT    | true          |                         |
| ns1.tbl | TABLE     | UPDATE    | true          |                         |
+---------+-----------+-----------+---------------+-------------------------+

You can see that user1 has been granted the SELECT, INSERT, and UPDATE privileges on the ns1.tbl table, and the SELECT privilege on the ns2 namespace.

8. Log in as user1

Log in as user1 and execute SQL statements.

java -jar scalardb-cluster-sql-cli-3.17.0-all.jar --config scalardb-cluster-sql-cli.properties

Enter the username and password as user1 and user1, respectively.

Now you can execute SQL statements as user1.

9. Execute DML statements

Execute the following INSERT statement as user1.

INSERT INTO ns1.tbl VALUES (1, 'a', 1);

Then, execute the following SELECT statement as user1.

SELECT * FROM ns1.tbl;

+----+------+------+
| id | col1 | col2 |
+----+------+------+
| 1  | a    | 1    |
+----+------+------+

You can see that user1 can execute INSERT and SELECT statements.

Next, try executing the following DELETE statement as user1.

DELETE FROM ns1.tbl WHERE id = 1;

Error: Authorization error (PERMISSION_DENIED: SQL-10021: Access denied: You need the DELETE privilege on the table ns1.tbl to execute this operation) (state=SDB11,code=9911)

You will see the above error message because user1 doesn't have the DELETE privilege on the ns1.tbl table.

10. Use roles to manage privileges

Log in as admin to create and manage roles.

java -jar scalardb-cluster-sql-cli-3.17.0-all.jar --config scalardb-cluster-sql-cli.properties

Enter the username and password as admin and admin, respectively.

Create a role named cleanup_role.

CREATE ROLE cleanup_role;

To verify the role has been created, run the following command.

SHOW ROLES;

+--------------+--------------+
|   roleName   | grantedRoles |
+--------------+--------------+
| cleanup_role |              |
+--------------+--------------+

Grant the SELECT, DELETE, and TRUNCATE privileges on the ns1.tbl table to the role.

GRANT SELECT, DELETE, TRUNCATE ON ns1.tbl TO ROLE cleanup_role;

To verify the privileges granted to the role, run the following command.

SHOW ROLE GRANTS FOR cleanup_role;

+---------+-------+-----------+
|  name   | type  | privilege |
+---------+-------+-----------+
| ns1.tbl | TABLE | SELECT    |
| ns1.tbl | TABLE | DELETE    |
| ns1.tbl | TABLE | TRUNCATE  |
+---------+-------+-----------+

Grant the role to user1.

GRANT ROLE cleanup_role TO user1;

To verify the privileges of user1, run the following command.

SHOW GRANTS FOR user1;

+---------+-----------+-----------+---------------+-------------------------+
|  name   |   type    | privilege | grantedToUser | rolesProvidingPrivilege |
+---------+-----------+-----------+---------------+-------------------------+
| ns2     | NAMESPACE | SELECT    | true          |                         |
| ns1.tbl | TABLE     | SELECT    | true          | cleanup_role            |
| ns1.tbl | TABLE     | INSERT    | true          |                         |
| ns1.tbl | TABLE     | UPDATE    | true          |                         |
| ns1.tbl | TABLE     | DELETE    | false         | cleanup_role            |
| ns1.tbl | TABLE     | TRUNCATE  | false         | cleanup_role            |
+---------+-----------+-----------+---------------+-------------------------+

Now, log in as user1 and try the DELETE statement again.

java -jar scalardb-cluster-sql-cli-3.17.0-all.jar --config scalardb-cluster-sql-cli.properties

Enter the username and password as user1 and user1, respectively.

DELETE FROM ns1.tbl WHERE id = 1;

This time, the statement succeeds because user1 now has the DELETE privilege through the cleanup_role role.

See also

For more information about using RBAC, see the role-related sections in the ScalarDB SQL Grammar reference.