Capabilities Preview

SET PERSIST vsql_allow_preview_extensions = ON;

mysqld --vsql_allow_preview_extensions=ON

SET PERSIST vsql_allow_preview_extensions = OFF;

#include <villagesql/preview/keyring.h>
#include <villagesql/vsql.h>

using KeyringCapability = vsql::preview_keyring::KeyringCapability;

static KeyringCapability g_keyring;

VEF_GENERATE_ENTRY_POINTS(
    make_extension()
        .func(/* ... */)
        .with(g_keyring))

#include <villagesql/preview/keyring.h>
#include <villagesql/vsql.h>

using KeyringCapability = vsql::preview_keyring::KeyringCapability;

static KeyringCapability g_keyring;

VEF_GENERATE_ENTRY_POINTS(
    make_extension()
        .with(g_keyring))

struct KeyringCapability::ReadResult {
  KeyringCapability::Status status;
  std::string value;
};

[[nodiscard]] KeyringCapability::ReadResult
KeyringCapability::read(std::string_view data_id,
                        std::string_view auth_id = {}) const;

[[nodiscard]] KeyringCapability::Status
KeyringCapability::write(std::string_view data_id,
                         std::string_view auth_id,
                         std::string_view data) const;

auto [status, value] = g_keyring.read("my_secret");
if (status == KeyringCapability::Status::OK) {
  // value contains the secret bytes
}

#include <villagesql/preview/keyring.h>
#include <villagesql/vsql.h>

using namespace vsql;
using KeyringCapability = vsql::preview_keyring::KeyringCapability;

static KeyringCapability g_keyring;

void keyring_read(StringArg data_id, StringArg auth_id, StringResult out) {
  if (data_id.is_null()) { out.set_null(); return; }

  const auto [status, value] =
      g_keyring.read(data_id.value(), auth_id.is_null() ? "" : auth_id.value());
  if (status == KeyringCapability::Status::UNAVAILABLE) {
    out.error("No keyring component is installed");
    return;
  }
  if (status != KeyringCapability::Status::OK) { out.set_null(); return; }

  auto buf = out.buffer();
  size_t len = std::min(value.size(), buf.size());
  memcpy(buf.data(), value.data(), len);
  out.set_length(len);
}

void keyring_store(StringArg data_id, StringArg auth_id, StringArg value,
                   IntResult out) {
  if (data_id.is_null() || value.is_null()) { out.set(1); return; }

  KeyringCapability::Status status = g_keyring.write(
      data_id.value(), auth_id.is_null() ? "" : auth_id.value(), value.value());
  if (status == KeyringCapability::Status::UNAVAILABLE) {
    out.error("No keyring component is installed");
    return;
  }
  out.set(status == KeyringCapability::Status::OK ? 0 : 1);
}

VEF_GENERATE_ENTRY_POINTS(
    make_extension()
        .func(make_func<&keyring_read>("keyring_read")
                  .returns(STRING).param(STRING).param(STRING).build())
        .func(make_func<&keyring_store>("keyring_store")
                  .returns(INT).param(STRING).param(STRING).param(STRING).build())
        .with(g_keyring))

#include <cstddef>

#include <mysql/components/services/mysql_current_thread_reader.h>
#include <villagesql/preview/mysql_services.h>
#include <villagesql/vsql.h>

using namespace vsql;

static preview_mysql_services::MysqlServices services;
VSQL_REQUIRE_SERVICE(services, mysql_current_thread_reader, thd_reader);

VEF_GENERATE_ENTRY_POINTS(
    make_extension()
        .func(/* ... */)
        .with(services))

static preview_mysql_services::ServiceRef<SERVICE_TYPE(keyring_reader_with_status)>
    reader;
static const int reader_req =
    (services.require<SERVICE_TYPE(keyring_reader_with_status)>(
         "keyring_reader_with_status.component_keyring_file", reader),
     0);

error: unknown type name 'mysql_service_mysql_current_thread_reader_t'

error: unknown type name 'size_t'

if (!thd_reader.valid()) { out.error("service unavailable"); return; }
MYSQL_THD thd = nullptr;
if (thd_reader->get(&thd) || thd == nullptr) { out.set_null(); return; }

ERROR 3219 (HY000): Failed to load VEF extension 'vsql_mysql_services_missing_test': failed to acquire MySQL service 'vsql_intentionally_missing'

#include <cstddef>

#include <mysql/components/services/defs/mysql_string_defs.h>
#include <mysql/components/services/mysql_current_thread_reader.h>
#include <mysql/components/services/mysql_thd_attributes.h>
#include <villagesql/preview/mysql_services.h>
#include <villagesql/vsql.h>

using namespace vsql;

static preview_mysql_services::MysqlServices services;
VSQL_REQUIRE_SERVICE(services, mysql_current_thread_reader, thd_reader);
VSQL_REQUIRE_SERVICE(services, mysql_thd_attributes, attrs);

// session_sql_command() -> STRING: the name of the SQL command running on the
// calling session, or NULL when the THD or the attribute cannot be read.
void session_sql_command(StringResult out) {
  if (!thd_reader.valid() || !attrs.valid()) {
    out.error("MySQL session services are not available");
    return;
  }

  MYSQL_THD thd = nullptr;
  // MySQL convention: a false return means success.
  if (thd_reader->get(&thd) || thd == nullptr) {
    out.set_null();
    return;
  }

  mysql_cstring_with_length value{nullptr, 0};
  if (attrs->get(thd, "sql_command", &value) || value.str == nullptr) {
    out.set_null();
    return;
  }

  out.set(std::string_view(value.str, value.length));
}

VEF_GENERATE_ENTRY_POINTS(make_extension().with(services).func(
    make_func<&session_sql_command>("session_sql_command")
        .returns(STRING)
        .no_params()
        .build()))

INSTALL EXTENSION vsql_mysql_services_session_test;
SELECT vsql_mysql_services_session_test.session_sql_command() AS sql_command;

+-------------+
| sql_command |
+-------------+
| select      |
+-------------+

#include <villagesql/preview/status_var.h>
#include <villagesql/vsql.h>

namespace sv = vsql::preview_status_var;

static long long g_hits   = 0;
static long long g_misses = 0;

static auto STATUS_VARS = sv::make_capability({
    sv::make_int("ext_hits",   &g_hits),
    sv::make_int("ext_misses", &g_misses)});

VEF_GENERATE_ENTRY_POINTS(
    make_extension()
        .with(STATUS_VARS))

SHOW GLOBAL STATUS LIKE 'my_ext%';

Variable_name       Value
my_ext.ext_hits     0
my_ext.ext_misses   0

#include <villagesql/preview/sys_var.h>
#include <villagesql/vsql.h>

namespace sv = vsql::preview_sys_var;

static bool      g_enabled   = true;
static long long g_threshold = 1000;
static char     *g_log_file  = nullptr;

static void on_threshold_change(sv::SysVarChange c) {
  // c.var_name() identifies the variable; c.as_int() returns the new value
}

static auto SYS_VARS = sv::make_capability({
    sv::make_bool("enabled",      "Enable feature",  &g_enabled,   true),
    sv::make_int ("threshold_ms", "Threshold in ms", &g_threshold, 1000, 0, 3600000)
        .on_change<&on_threshold_change>(),
    sv::make_str ("log_file",     "Log file path",   &g_log_file,  "/tmp/myext.log")});

VEF_GENERATE_ENTRY_POINTS(
    make_extension()
        .with(SYS_VARS))

SELECT @@global.my_ext.threshold_ms;
SET GLOBAL my_ext.threshold_ms = 500;
SET GLOBAL my_ext.log_file = '/var/log/myext.log';

bool err = SYS_VARS.set("my_ext", "threshold_ms", nullptr, value);

#include <villagesql/preview/thread_worker.h>
#include <villagesql/vsql.h>

static vef_next_wakeup_t my_work(vef_wakeup_reason_t reason,
                                 struct vef_thread_handle_t *thread,
                                 void *arg) {
  // ...
  return {};
}

static vsql::preview_thread_worker::ThreadWorkerCapability<&my_work>
    g_worker{"suffix"};

VEF_GENERATE_ENTRY_POINTS(
    make_extension()
        .with(g_worker))

typedef vef_next_wakeup_t (*vef_work_fn_t)(vef_wakeup_reason_t reason,
                                           struct vef_thread_handle_t *thread,
                                           void *arg);

typedef struct {
  unsigned int sleep_ms;
  int poll_fd;
} vef_next_wakeup_t;

#include <villagesql/preview/thread_worker.h>
#include <villagesql/vsql.h>

#include <atomic>

static std::atomic<unsigned long long> g_heartbeat{0};

static vef_next_wakeup_t heartbeat_work(vef_wakeup_reason_t reason,
                                        struct vef_thread_handle_t *thread,
                                        void *arg) {
  switch (reason) {
    case VEF_WAKEUP_ENABLE:
      return {1000, 0};  // tick every 1000 ms, no poll fd
    case VEF_WAKEUP_PERIODIC:
      g_heartbeat.fetch_add(1, std::memory_order_relaxed);
      return {};  // keep current sleep_ms and poll_fd
    case VEF_WAKEUP_POLL_FD:
      return {};  // not used in this example
    case VEF_WAKEUP_DISABLE:
      return {};  // ignored
  }
  return {};
}

static vsql::preview_thread_worker::ThreadWorkerCapability<&heartbeat_work>
    g_worker{"heartbeat"};

VEF_GENERATE_ENTRY_POINTS(
    make_extension()
        .with(g_worker))

SET GLOBAL my_ext.heartbeat_enabled = ON;

#include <villagesql/preview/sql_query.h>
#include <villagesql/preview/thread_worker.h>
#include <villagesql/vsql.h>

static vsql::preview_sql_query::SqlQueryCapability g_sql;

static vef_next_wakeup_t my_work(vef_wakeup_reason_t reason,
                                 struct vef_thread_handle_t *thread,
                                 void *arg) {
  auto session = g_sql.open(thread);
  if (!session) return {};
  // ...
  return {};
}

static vsql::preview_thread_worker::ThreadWorkerCapability<&my_work>
    g_worker{"sql_demo"};

VEF_GENERATE_ENTRY_POINTS(
    make_extension()
        .with(g_worker)
        .with(g_sql))

auto result = session.sql("SELECT id, name FROM t").execute();
if (result.has_error()) {
  // result.error().message holds the server error string.
  return {};
}
while (result.next()) {
  long long id          = result.column_int(0);
  std::string_view name = result.column_str(1);
  // ...
}

auto status = session.sql("SELECT 1").for_each(
    [](const auto &row) {
      // row.column_int(0), row.column_str(1), etc.
    });
if (status.has_error()) {
  // status.error().message
}

struct Diag {
  uint32_t errno_;
  vef_sql_diag_severity_t severity;   // NOTE | WARNING | ERROR
  std::string_view sqlstate;          // 5-char SQLSTATE
  std::string_view message;           // may be empty
};

bool         Result::has_error() const;
Diag         Result::error() const;
unsigned int Result::warning_count() const;
Diag         Result::warning(unsigned int i) const;

#include <villagesql/preview/sql_query.h>
#include <villagesql/preview/thread_worker.h>
#include <villagesql/vsql.h>

static vsql::preview_sql_query::SqlQueryCapability g_sql;

static vef_next_wakeup_t sql_demo_work(vef_wakeup_reason_t reason,
                                       struct vef_thread_handle_t *thread,
                                       void *arg) {
  if (reason == VEF_WAKEUP_ENABLE) return {5000, 0};
  if (reason != VEF_WAKEUP_PERIODIC) return {};

  auto session = g_sql.open(thread);
  if (!session) return {};

  // Buffered: read a small result set.
  auto rs = session.sql("SELECT id, name FROM mydb.t LIMIT 10").execute();
  if (rs.has_error()) {
    auto e = rs.error();
    // Log e.errno_, e.sqlstate, e.message somewhere extension-owned.
  } else {
    while (rs.next()) {
      long long id          = rs.column_int(0);
      std::string_view name = rs.column_str(1);
      (void)id; (void)name;
    }
  }

  // Streaming: process rows without buffering.
  auto status = session.sql("SELECT v FROM mydb.t").for_each(
      [](const auto &row) {
        long long v = row.column_int(0);
        (void)v;
      });
  for (unsigned i = 0; i < status.warning_count(); ++i) {
    auto w = status.warning(i);
    (void)w;
  }
  return {};
}

static vsql::preview_thread_worker::ThreadWorkerCapability<&sql_demo_work>
    g_worker{"sql_demo"};

VEF_GENERATE_ENTRY_POINTS(
    make_extension()
        .with(g_worker)
        .with(g_sql))

#include <villagesql/preview/storage_builder.h>
#include <villagesql/preview/storage_api.h>
#include <villagesql/vsql.h>

namespace storage = vsql::preview_storage;
using vsql::preview_storage_builder::ColumnStoreCapability;
using vsql::preview_storage_builder::make_column_store;
using vsql::preview_storage_builder::StorageCapability;

struct MyCtx {
  storage::Space::Ref space = 0;
  storage::Segment::PageRef root_page = storage::Page::INVALID_REF;
};

static auto STORAGE = StorageCapability{};

static constexpr auto kMyStorage =
    make_column_store<MyCtx>(MY_TYPE)
        .create<&MyStorage::create>()
        .drop<&MyStorage::drop>()
        .load<&MyStorage::load>()
        .insert<&MyStorage::insert>()
        .select<&MyStorage::select>()
        .mark_delete<&MyStorage::mark_delete>()
        .purge<&MyStorage::purge>()
        .build();

static auto COLUMN_STORE = ColumnStoreCapability().column_store(kMyStorage);

using namespace vsql;

VEF_GENERATE_ENTRY_POINTS(
    make_extension()
        .with(STORAGE)
        .with(COLUMN_STORE)
        .type(MY_TYPE))

// CREATE TABLE / ALTER TABLE ADD COLUMN.
// col_len is the type's persisted length. Reserve segments here and store
// space + root_page in ctx->user() so DML functions can reach them.
bool create(storage::Column::StorageCtx<MyCtx>*, storage::Space::Ref,
            storage::Segment::TrxRef, uint32_t col_len,
            char* error_msg, uint32_t error_msg_len);

// DROP TABLE / ALTER TABLE DROP COLUMN.
// Release any segments reserved in create(). Arena memory is freed by the
// server after this call returns.
bool drop(storage::Column::StorageCtx<MyCtx>*, storage::Segment::TrxRef,
          char* error_msg, uint32_t error_msg_len);

// Called when the server reattaches to existing storage (e.g. after restart).
// Recover space and root_page from the StorageRef set in create().
bool load(storage::Column::StorageCtx<MyCtx>*, storage::Column::StorageRef,
          char* error_msg, uint32_t error_msg_len);

// INSERT. col_data is the encoded value; rowid_prefix identifies the owning
// row. Write into your storage layout and return a Column::Ref the server
// stores in the row payload in place of the value bytes.
bool insert(storage::Column::StorageCtx<MyCtx>*, storage::MtrCtx::Ref,
            storage::Segment::TrxRef, storage::Column::Data col_data,
            storage::Column::Data rowid_prefix, storage::Column::Ref* col_ref,
            char* error_msg, uint32_t error_msg_len);

// SELECT. Given the Column::Ref produced by insert, populate col_data and
// rowid_prefix, and report the writing transaction and delete-mark status.
bool select(storage::Column::StorageCtx<MyCtx>*, storage::MtrCtx::Ref,
            storage::Column::Ref, storage::Column::Data* col_data,
            storage::Column::Data* rowid_prefix, storage::Segment::TrxRef*,
            bool* delete_marked, char* error_msg, uint32_t error_msg_len);

// DELETE (in-transaction). Set or clear the delete-mark flag. The actual
// bytes must remain readable until purge() runs.
bool mark_delete(storage::Column::StorageCtx<MyCtx>*, storage::MtrCtx::Ref,
                 storage::Segment::TrxRef, storage::Column::Ref,
                 bool delete_mark, char* error_msg, uint32_t error_msg_len);

// InnoDB purge. Reclaim storage for entries whose deleting transaction is
// no longer visible to any active snapshot.
bool purge(storage::Column::StorageCtx<MyCtx>*, storage::MtrCtx::Ref,
           storage::Segment::TrxRef, storage::Column::Ref,
           char* error_msg, uint32_t error_msg_len);

bool MyStorage::create(storage::Column::StorageCtx<MyCtx>* ctx,
                       storage::Space::Ref space, storage::Segment::TrxRef trx,
                       uint32_t col_len,
                       char* error_msg, uint32_t error_msg_len) {
  storage::Segment::PageRef root;
  if (storage::Segment::create(space, 1, trx, root) != storage::Error::SUCCESS) {
    snprintf(error_msg, error_msg_len, "%s", storage::last_error().data());
    return true;
  }

  ctx->user()->space = space;
  ctx->user()->root_page = root;
  // Encode space and root into StorageRef so load() can recover both.
  ctx->set_ref((static_cast<storage::Column::StorageRef>(space) << 32) |
               static_cast<storage::Column::StorageRef>(root));
  return false;
}

bool MyStorage::load(storage::Column::StorageCtx<MyCtx>* ctx,
                     storage::Column::StorageRef storage_ref,
                     char* error_msg, uint32_t error_msg_len) {
  ctx->user()->space =
      static_cast<storage::Space::Ref>(storage_ref >> 32);
  ctx->user()->root_page =
      static_cast<storage::Segment::PageRef>(storage_ref & 0xFFFFFFFF);
  ctx->set_ref(storage_ref);
  return false;
}

storage::MtrCtx mtr;
storage::MtrCtx::Ref mtr_ref = mtr.start();
if (mtr_ref == nullptr) { /* OOM — handle error */ return true; }
// ... page operations ...
mtr.commit();

storage::Page root;
root.load(ctx->user()->space, ctx->user()->root_page,
          storage::Page::Latch::EXCLUSIVE, mtr_ref);
storage::Segment::Ref seg = storage::Segment::get_header(root, 0);
storage::Page data_page;
data_page.load_new(seg, mtr_ref);  // allocates a fresh page

storage::Page page;

// Read
page.load(ctx->user()->space, page_num, storage::Page::Latch::SHARED, mtr_ref);
uint32_t v = page.read_integer_4(storage::Page::HEADER_SIZE + offset);

// Write
page.load(ctx->user()->space, page_num, storage::Page::Latch::EXCLUSIVE, mtr_ref);
page.write_integer_4(storage::Page::HEADER_SIZE + offset, v, mtr_ref);

#include <villagesql/preview/statement_event.h>
#include <villagesql/vsql.h>

namespace se = vsql::preview_statement_event;

static void on_statement(const se::StatementEventArgs &args,
                         se::StatementEventResult &result) {
  // inspect args; optionally write an advisory message via result
}

static se::StatementEventCapability<VEF_STATEMENT_EVENT_POSTEXECUTE,
                                    &on_statement>
    g_statement_event;

VEF_GENERATE_ENTRY_POINTS(
    make_extension()
        .with(g_statement_event))

#include <cerrno>
#include <cstdio>
#include <cstring>
#include <ctime>
#include <mutex>

#include <villagesql/preview/statement_event.h>
#include <villagesql/preview/sys_var.h>
#include <villagesql/vsql.h>

using namespace vsql;
namespace sv = vsql::preview_sys_var;
namespace se = vsql::preview_statement_event;

static bool g_enabled;
static long long g_threshold_ms;
static char *g_log_filename;
static std::mutex g_log_mutex;

static void slow_query_hook(const se::StatementEventArgs &args,
                            se::StatementEventResult &result) {
  if (!g_enabled) return;
  if (args.query_time_secs() * 1000.0 < static_cast<double>(g_threshold_ms))
    return;

  time_t now = static_cast<time_t>(args.query_start_utime() / 1000000);
  char ts[32];
  struct tm tm_utc;
  gmtime_r(&now, &tm_utc);
  strftime(ts, sizeof(ts), "%Y-%m-%dT%H:%M:%SZ", &tm_utc);

  std::lock_guard<std::mutex> lock(g_log_mutex);
  FILE *f = fopen(g_log_filename, "a");
  if (f == nullptr) {
    result.error_msg("failed to open '%s': %s", g_log_filename,
                     strerror(errno));
    return;
  }

  fprintf(f, "# Time: %s\n", ts);
  fprintf(f, "# User@Host: %s @ %s  Id: %lu\n", args.user() ? args.user() : "",
          args.client_ip() ? args.client_ip() : "", args.connection_id());
  fprintf(f,
          "# Schema: %s  Query_time: %.6f  Lock_time: %.6f"
          "  Rows_sent: %llu  Rows_examined: %llu\n",
          args.schema() ? args.schema() : "", args.query_time_secs(),
          args.lock_time_secs(), (unsigned long long)args.rows_sent(),
          (unsigned long long)args.rows_examined());
  fprintf(f, "SET timestamp=%llu;\n", (unsigned long long)now);
  auto q = args.query();
  fprintf(f, "%.*s;\n", (int)q.size(), q.data());
  fclose(f);
}

static auto SYS_VARS = sv::make_capability({
    sv::make_bool("enabled", "Enable the slow query log", &g_enabled, false),
    sv::make_int("threshold_ms", "Minimum execution time to log, in ms",
                 &g_threshold_ms, 1000, 0, 3600000),
    sv::make_str("log_file", "Path to the slow query log file",
                 &g_log_filename, "/tmp/vsql_slow_query.log")});

static se::StatementEventCapability<VEF_STATEMENT_EVENT_POSTEXECUTE,
                                    &slow_query_hook>
    STATEMENT_EVENT;

VEF_GENERATE_ENTRY_POINTS(
    make_extension().with(SYS_VARS).with(STATEMENT_EVENT))

INSTALL EXTENSION vsql_slow_query_log;
SET GLOBAL vsql_slow_query_log.enabled = ON;
SET GLOBAL vsql_slow_query_log.threshold_ms = 500;

# Time: 2026-06-22T22:53:44Z
# User@Host: root @   Id: 27
# Schema:   Query_time: 0.605084  Lock_time: 0.000000  Rows_sent: 1  Rows_examined: 1
SET timestamp=1782168824;
SELECT SLEEP(0.6);

#include <villagesql/preview/auth.h>
#include <villagesql/vsql.h>

using namespace vsql;
using vsql::preview_auth::AuthContext;
using vsql::preview_auth::AuthResult;

AuthResult authenticate(AuthContext &c) {
  // ... validate the client and set the effective account ...
  return AuthResult::kOk;
}

constexpr auto MY_AUTH =
    vsql::preview_auth::make_auth<&authenticate>("my_auth")
        .client_plugin("mysql_clear_password")
        .build();

static vsql::preview_auth::AuthCapability g_auth{MY_AUTH};

VEF_GENERATE_ENTRY_POINTS(
    make_extension()
        .with(g_auth))

AuthResult authenticate(AuthContext &c);

#include <cstring>

#include <villagesql/preview/auth.h>
#include <villagesql/vsql.h>

using namespace vsql;
using vsql::preview_auth::AuthContext;
using vsql::preview_auth::AuthResult;

namespace {

constexpr char kToken[] = "vsql-auth-test-token";
constexpr char kMappedAccount[] = "vsql_auth_test_user";

AuthResult authenticate(AuthContext &c) {
  auto pkt = c.read_packet();
  if (pkt.empty()) return AuthResult::kError;

  // mysql_clear_password sends a NUL-terminated string; drop the trailing NUL.
  size_t len = pkt.size();
  if (len && pkt[len - 1] == '\0') --len;

  if (len != std::strlen(kToken) ||
      std::memcmp(pkt.data(), kToken, len) != 0) {
    return AuthResult::kReject;
  }

  c.authenticate_as(kMappedAccount);
  // @@external_user records the connecting identity, not the mapped account.
  c.set_external_user(c.user_name());
  return AuthResult::kOk;
}

constexpr auto AUTH_METHOD =
    vsql::preview_auth::make_auth<&authenticate>("vsql_auth_test")
        .client_plugin("mysql_clear_password")
        .build();
vsql::preview_auth::AuthCapability g_auth{AUTH_METHOD};

}  // namespace

VEF_GENERATE_ENTRY_POINTS(make_extension().with(g_auth))

INSTALL EXTENSION vsql_auth_test;
CREATE USER auth_user IDENTIFIED WITH vsql_auth_test;
CREATE USER vsql_auth_test_user;
GRANT SELECT ON *.* TO vsql_auth_test_user;
GRANT PROXY ON vsql_auth_test_user TO auth_user;

CREATE USER auth_user IDENTIFIED WITH vsql_auth_test BY 'secret';

ERROR 1827 (HY000): The password hash doesn't have the expected format.

SELECT plugin FROM mysql.user WHERE user = 'auth_user';

+----------------+
| plugin         |
+----------------+
| vsql_auth_test |
+----------------+

mysql --enable-cleartext-plugin --user=auth_user \
      --password=vsql-auth-test-token \
      -e "SELECT CURRENT_USER(), @@external_user"

CURRENT_USER()         @@external_user
vsql_auth_test_user@%  auth_user

UNINSTALL EXTENSION vsql_auth_test;

bool auto_create_enabled() { return true; }

constexpr auto AUTH_METHOD =
    vsql::preview_auth::make_auth<&authenticate>("vsql_auth_test")
        .client_plugin("mysql_clear_password")
        .auto_create(&auto_create_enabled)
        .build();

if (c.account_unknown()) {
  const char *roles[] = {"vsql_role_granted"};
  c.request_provision(c.user_name(), roles, 1);
  c.authenticate_as(c.user_name());
  c.set_external_user(c.user_name());
  return AuthResult::kOk;
}

INSTALL EXTENSION vsql_auth_test;
SET GLOBAL vsql_auth_test.auto_create = ON;
CREATE ROLE vsql_role_granted;
GRANT SELECT ON *.* TO vsql_role_granted;

mysql --enable-cleartext-plugin --user=auto_created_user \
      --password=vsql-auth-test-token \
      -e "SELECT CURRENT_USER() AS who, @@external_user AS ext"

+---------------------+-------------------+
| who                 | ext               |
+---------------------+-------------------+
| auto_created_user@% | auto_created_user |
+---------------------+-------------------+

SELECT user, host, plugin FROM mysql.user WHERE user = 'auto_created_user';

+-------------------+------+----------------+
| user              | host | plugin         |
+-------------------+------+----------------+
| auto_created_user | %    | vsql_auth_test |
+-------------------+------+----------------+

SHOW GRANTS FOR 'auto_created_user'@'%';

+----------------------------------------------------------+
| Grants for auto_created_user@%                           |
+----------------------------------------------------------+
| GRANT USAGE ON *.* TO `auto_created_user`@`%`            |
| GRANT `vsql_role_granted`@`%` TO `auto_created_user`@`%` |
+----------------------------------------------------------+

mysql --enable-cleartext-plugin --user=never_created --password=wrong-token \
      -e "SELECT 1"

ERROR 1045 (28000): Access denied for user 'never_created'@'localhost' (using password: YES)

bool auto_grant_enabled() { return true; }

constexpr auto AUTH_METHOD =
    vsql::preview_auth::make_auth<&authenticate>("vsql_auth_test")
        .client_plugin("mysql_clear_password")
        .auto_grant(&auto_grant_enabled)
        .build();

CREATE USER auth_user IDENTIFIED WITH vsql_auth_test;
CREATE USER vsql_auth_test_user;
GRANT SELECT ON *.* TO vsql_auth_test_user;
GRANT PROXY ON vsql_auth_test_user TO auth_user;
CREATE ROLE vsql_role_granted, vsql_role_denied;

+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE           |
+----------------+

SET GLOBAL vsql_auth_test.auto_grant = ON;

+------------------------------------------------+
| CURRENT_ROLE()                                 |
+------------------------------------------------+
| `vsql_role_denied`@`%`,`vsql_role_granted`@`%` |
+------------------------------------------------+

SHOW GRANTS FOR vsql_auth_test_user;

+-----------------------------------------------------------------------------------+
| Grants for vsql_auth_test_user@%                                                  |
+-----------------------------------------------------------------------------------+
| GRANT SELECT ON *.* TO `vsql_auth_test_user`@`%`                                  |
| GRANT `vsql_role_denied`@`%`,`vsql_role_granted`@`%` TO `vsql_auth_test_user`@`%` |
+-----------------------------------------------------------------------------------+