Skip to main content
Version: 3.19
Features and pricing

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.

Authentication methods​

ScalarDB Cluster supports the following authentication methods:

  • Username and password (USERPASS): Users authenticate with a username and password. This is the default method described in this guide.
  • OIDC (OIDC): Client applications pass JWT access tokens from an OIDC provider (for example, Keycloak) instead of passwords. For details, see Control User Access via OIDC-Based JWT Access Tokens.

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​

CommandSuperuser requiredRequired privileges
CREATE NAMESPACEtrue
DROP NAMESPACEtrue
CREATE TABLECREATE
DROP TABLEDROP
CREATE INDEXCREATE
DROP INDEXDROP
TRUNCATE TABLETRUNCATE
ALTER TABLEALTER
CREATE COORDINATOR TABLEStrue
DROP COORDINATOR TABLEStrue
TRUNCATE COORDINATOR TABLEStrue

DML​

CommandSuperuser requiredRequired privileges
SELECTSELECT
INSERTSELECT, INSERT, and UPDATE
UPSERTSELECT, INSERT, and UPDATE
UPDATESELECT, INSERT, and UPDATE
DELETESELECT and DELETE
note

ScalarDB initially offered only the Put operation, which corresponds to UPSERT, for writing data. As a result, there is only one internal write permission. Consequently, the permissions needed for INSERT, UPDATE, and UPSERT are the same; all of them require the SELECT privilege. In the future, ScalarDB plans to provide finer-grained write permissions.

DCL​

CommandSuperuser requiredRequired privileges
CREATE USERtrue
ALTER USERtrue (Users can change their own password.)
DROP USERtrue
GRANTGRANT (Users can grant only the privileges that they have.)
REVOKEGRANT (Users can revoke only the privileges that they have.)
CREATE ROLEtrue
DROP ROLEtrue
GRANT ... TO ROLEGRANT (Users can grant only the privileges that they have.)
REVOKE ... FROM ROLEGRANT (Users can revoke only the privileges that they have.)
GRANT ROLEADMIN OPTION on the role (Users can grant only those roles.)
REVOKE ROLEADMIN OPTION on the role (Users can revoke only those roles.)
REVOKE ADMIN OPTIONADMIN 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.

NameDescriptionDefault
scalar.db.cluster.auth.enabledWhether authentication and authorization are enabled.false

You can also set the following configurations:

NameDescriptionDefault
scalar.db.cluster.auth.cache_expiration_time_millisCache expiration time for authentication and authorization information in milliseconds.60000 (1 minute)
scalar.db.cluster.auth.auth_token_expiration_time_minutesAuthentication and authorization token expiration time in minutes.1440 (1 day)
scalar.db.cluster.auth.auth_token_gc_thread_interval_minutesAuthentication and authorization token garbage collection (GC) thread interval in minutes.360 (6 hours)
scalar.db.cluster.auth.pepperA secret value added to a password before hashing. If not specified, the password is hashed without pepper.

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.

NameDescriptionDefault
scalar.db.cluster.auth.enabledWhether 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.

NameDescriptionDefault
scalar.db.sql.cluster_mode.usernameThe username of the client.
scalar.db.sql.cluster_mode.passwordThe password of the client.

Passing credentials programmatically​

The simplest way to provide credentials is to set them in the configuration properties: a username and password for the userpass authentication type, or a JWT access token for the oidc_jwt authentication type. However, this binds the client to a single ScalarDB user, and a token set in the properties cannot be refreshed when it expires. If your application needs to authenticate as different ScalarDB users at runtime (for example, when one application instance serves many end users), you can instead pass credentials programmatically on a per-thread, per-transaction, or per-operation basis, as described in the following sections. Each mechanism works with both authentication types.

Passing credentials per thread​

A credential holder sets credentials for the current thread for the duration of a block of code. All ScalarDB Cluster operations within the block are executed as the user identified by the held credentials.

Use UserpassHolder when the authentication type of the client is userpass, which is the default:

import com.scalar.db.cluster.client.UserpassHolder;

UserpassHolder.executeWithUserpass(username, password, () -> {
    // All ScalarDB Cluster operations in this block are executed as the user who is authenticated with the specified username and password.
});

Use OidcJwtAccessTokenHolder when the authentication type of the client is oidc_jwt:

import com.scalar.db.cluster.client.OidcJwtAccessTokenHolder;

OidcJwtAccessTokenHolder.executeWithToken(jwtAccessToken, () -> {
    // All ScalarDB Cluster operations in this block are executed as the user who is mapped from the specified token.
});

When using the holders, keep the following in mind:

  • The credentials are set for the current thread only. Other threads are not affected, so you can safely use the holders concurrently from multiple threads.
  • The credentials are resolved for each request. Therefore, when you begin a transaction within the block, also commit or roll it back within the block.
  • The credentials are cleared automatically when the block completes, even if an exception is thrown.
  • Nested calls of the same holder are not supported and throw an IllegalStateException.
  • Credentials set in the holder for the other authentication type are ignored.
  • The credentials set in a holder take precedence over the credentials set in the configuration properties.
  • For UserpassHolder, the password will be null for users that don't have a password.

Passing credentials as operation attributes​

You can pass credentials for a specific transaction, operation, or SQL statement by using operation attributes. For details on operation attributes, see Operation attributes. The following attribute keys are reserved for authentication credentials:

KeyConstant in AuthOperationAttributesDescription
auth-typeTYPEThe authentication method for the call. The value must be userpass or oidc_jwt (case-insensitive).
auth-userpass-usernameUSERPASS_USERNAMEThe username when auth-type is userpass. Required.
auth-userpass-passwordUSERPASS_PASSWORDThe password when auth-type is userpass. Optional for users who don't have a password.
auth-oidc-jwt-access-tokenOIDC_JWT_ACCESS_TOKENThe JWT access token when auth-type is oidc_jwt. Required.

The com.scalar.db.cluster.client.AuthOperationAttributes class provides these keys as constants, but you can also specify them as literal strings.

You can pass the auth attributes in the places described in the following sections. The examples use the userpass authentication type. For oidc_jwt, set auth-type to oidc_jwt and specify the JWT access token by using the auth-oidc-jwt-access-token key instead of the username and password keys.

For a transaction by using the primitive interface​

Pass the attributes to the begin() or beginReadOnly() method on the transaction manager. All operations in the transaction authenticate as the specified user.

import com.scalar.db.cluster.client.AuthOperationAttributes;

Map<String, String> attributes = new HashMap<>();
attributes.put(AuthOperationAttributes.TYPE, "userpass");
attributes.put(AuthOperationAttributes.USERPASS_USERNAME, "user1");
attributes.put(AuthOperationAttributes.USERPASS_PASSWORD, "password1");

DistributedTransaction transaction = manager.begin(attributes);

For a transaction by using the SQL interface​

Pass the attributes to the begin() or beginReadOnly() method on SqlSession, or specify the keys in the WITH clause of the BEGIN or START TRANSACTION statement. All statements in the transaction authenticate as the specified user.

BEGIN WITH 'auth-type' = 'userpass'
  AND 'auth-userpass-username' = 'user1'
  AND 'auth-userpass-password' = 'password1';

For an operation executed directly on the transaction manager​

For one-shot operations that you execute directly on the transaction manager without beginning a transaction, set the attributes on the operation itself by using the attribute() method in the operation builder. This works for any operation builder that supports the attribute() method, such as Get, Scan, Insert, Upsert, Update, and Delete.

import com.scalar.db.cluster.client.AuthOperationAttributes;

Insert insert = Insert.newBuilder()
    .namespace("ns1")
    .table("tbl")
    .partitionKey(Key.ofInt("id", 1))
    .textValue("col1", "a")
    .attribute(AuthOperationAttributes.TYPE, "userpass")
    .attribute(AuthOperationAttributes.USERPASS_USERNAME, "user1")
    .attribute(AuthOperationAttributes.USERPASS_PASSWORD, "password1")
    .build();

manager.insert(insert);

For a SQL statement executed outside of a transaction​

For SQL statements that you execute without beginning a transaction, specify the keys in the WITH clause of the statement. This works for any statement that supports the WITH clause, including prepared statements.

INSERT INTO ns1.tbl (id, col1) VALUES (1, 'a')
  WITH 'auth-type' = 'userpass'
  AND 'auth-userpass-username' = 'user1'
  AND 'auth-userpass-password' = 'password1';

How the auth attributes are handled​

When passing credentials as operation attributes, keep the following in mind:

  • If the auth-type attribute is present with a non-empty value, the specified credentials are used for that call, taking precedence over the credentials set for the current thread by a credential holder and the credentials set in the client configuration properties.
  • When you pass the auth attributes in one of the places described above, the client removes them from the attributes before forwarding the request to the cluster, so the credentials don't appear in server-side logs or error messages. Other attributes in the same attribute map or WITH clause are preserved.
  • The auth attributes are recognized only in the places described above. If you specify them anywhere else, such as on an operation or SQL statement that you execute within a transaction, they are not interpreted as credentials and are forwarded to the cluster as regular operation attributes.
  • Invalid values, such as an unknown auth-type value or a missing required attribute, cause an IllegalArgumentException on the client side before any request is sent.
  • When you execute a batch of SQL statements by using the executeBatch() method, only the auth attributes in the WITH clause of the first statement are used, and the selected credentials apply to the whole batch.
  • Similarly, when you execute multiple operations in a single call on the transaction manager, for example, by using the mutate() method, only the auth attributes on the first operation are used, and the selected credentials apply to all operations in that call.
  • If authentication and authorization are disabled (scalar.db.cluster.auth.enabled is false), the auth attributes are ignored and the operation is executed without authentication. Therefore, you can keep the attributes in your application code even when deploying to an environment where authentication and authorization are disabled.

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​

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.sql.enabled=true

# Enable cross-partition scan to perform a full scan by using the SELECT statements in this tutorial.
# This is not required for authentication and authorization itself.
scalar.db.cross_partition_scan.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.19.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.19.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 | authenticationMethods |
+----------+-------------+-----------------------+
| user1    | false       | USERPASS              |
| admin    | true        | USERPASS              |
+----------+-------------+-----------------------+

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.19.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.19.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.19.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.

Contact technical support
Available only to customers with a commercial license.
Report doc issue
If you have any feedback about this page, please submit an issue.