Secret Management via SQL

[piotrrzysko]

This is a proposal to introduce secret management via SQL in Trino. The goal of this feature is to allow users to easily define and manage secrets, which can be referenced in the CREATE CATALOG statement.

SQL Syntax

We assume that the fully qualified secret name follows the format secret_provider_name.secret_name, where secret_provider_name is the name of a secret provider plugin, and secret_name is the secret identifier.

CREATE SECRET

Proposed syntax for creating secrets:

CREATE SECRET [ IF NOT EXISTS ] secret_name AS secret_value

The type of secret_value is VARCHAR.

ALTER SECRET

Proposed syntax for updating a secret string:

ALTER SECRET [ IF EXISTS ] secret_name SET VALUE secret_value

The type of secret_value is VARCHAR.

DROP SECRET

Proposed syntax for dropping secrets:

DROP SECRET [ IF EXISTS ] secret_name

This will remove the secret regardless of whether it is referenced anywhere.

SHOW SECRETS

Proposed syntax for listing secrets:

SHOW SECRETS [ FROM | IN secret_provider_name ] secret_name
[ LIKE '<pattern>' ESCAPE '<escape>' ]

An example output:

Provider	Secret
vault	password1
vault	password2
aws	secret123

Example usage

-- create:
CREATE SECRET vault.pg_password SECRET_STRING '1234';

-- update:
ALTER SECRET vault.pg_password SET SECRET_STRING '4321';

-- reference:
CREATE CATALOG example USING postgresql
WITH (
    "connection-url" = 'jdbc:pg:localhost:5432',
    "connection-user" = 'user',
    "connection-password" = '$vault:pg_password',
    "case-insensitive-name-matching" = 'true'
);

-- show:
SHOW SECRETS FROM vault;

-- drop:
DROP SECRET vault.pg_password;

cc: @martint