Materialize/test/sqllogictest/secret.slt

# Copyright Materialize, Inc. and contributors. All rights reserved.
#
# Use of this software is governed by the Business Source License
# included in the LICENSE file at the root of this repository.
#
# As of the Change Date specified in that file, in accordance with
# the Business Source License, use of this software will be governed
# by the Apache License, Version 2.0.

# Basic tests of the `CREATE SECRET`, `ALTER SECRET` and `DROP SECRET` DDL statements.

mode cockroach

# Start from a pristine server
reset-server

query TT
SHOW SECRETS
----

statement OK
CREATE SECRET secret AS decode('c2VjcmV0Cg==', 'base64');

statement OK
CREATE SECRET IF NOT EXISTS secret AS decode('c2VjcmV0Cg==', 'base64');

statement error catalog item 'secret' already exists
CREATE SECRET secret AS decode('c2VjcmV0Cg==', 'base64');

statement OK
CREATE SECRET key AS decode('c2VjcmV0Cg==', 'base64');

query TTT rowsort
SELECT id, schema_id, name FROM mz_secrets
----
u1 u3 secret
u4 u3 key

query TT rowsort
SHOW SECRETS
----
secret (empty)
key (empty)

query TT
SHOW SECRETS LIKE 'k%'
----
key (empty)

statement OK
CREATE TABLE t1 (f1 INTEGER);

statement OK
insert into t1 values (1);

statement error catalog item 't1' already exists
CREATE SECRET t1 AS decode('c2VjcmV0Cg==', 'base64');

statement error t1 is a table not a secret
ALTER SECRET t1 rename to t2;

statement error t1 is a table not a secret
ALTER SECRET t1 as 'textsecret';

statement OK
DROP SECRET secret

statement error unknown catalog item 'secret'
DROP SECRET secret

statement OK
DROP SECRET IF EXISTS secret

statement OK
CREATE SECRET secret AS decode('c2VjcmV0Cg==', 'base64');

query TT rowsort
SHOW SECRETS
----
key (empty)
secret (empty)

statement OK
ALTER SECRET key RENAME TO certificate

query TT rowsort
SHOW SECRETS
----
certificate (empty)
secret (empty)

# Rename to an existing secret

statement OK
CREATE SECRET existing AS decode('c2VjcmV0Cg==', 'base64');

statement error catalog item 'existing' already exists
ALTER SECRET certificate RENAME TO existing

statement error catalog item 't1' already exists
ALTER SECRET certificate RENAME TO t1

statement OK
DROP SECRET existing

# Rename across schemas
statement error Expected end of statement, found dot
ALTER SECRET public.certificate RENAME TO public2.certificate2;

statement OK
ALTER SECRET certificate as decode('c2VjcmV0Cg==', 'base64');

statement error unknown catalog item 'nonexistant'
ALTER SECRET nonexistant as decode('c2VjcmV0Cg==', 'base64');

statement OK
create schema testschema

statement OK
CREATE SECRET testschema.key AS decode('c2VjcmV0Cg==', 'base64');

query TT rowsort
SHOW SECRETS
----
certificate (empty)
secret (empty)

query TT rowsort
SHOW SECRETS FROM testschema
----
key (empty)

statement error AS does not allow subqueries
create secret a AS (SELECT * from t1);

statement OK
CREATE SECRET roundtrip AS decode(encode('supersecret', 'base64') , 'base64');

statement OK
CREATE SECRET text_secret as 'text'

statement OK
CREATE SECRET byte_secret as 'text'::bytea;

statement error AS must have type bytea, not type integer
CREATE SECRET int_secret as 123456

statement error secret value can not be null
CREATE SECRET null_secret as NULL

statement error AS must have type bytea, not type text
create secret aa_secret as 'a' || 'a'

statement OK
create secret ab_secret as ('a' || 'b')::bytea

statement OK
create secret secret_512 as REPEAT('x', 1024 * 512)::bytea;

statement error secrets can not be bigger than 512KiB
create secret secret_1024 as REPEAT('x', 1024 * 1024)::bytea;

# Test dropping multiple secrets in a single operation
statement OK
create schema to_be_dropped

statement OK
CREATE SECRET to_be_dropped.secret1 as 'text'

statement OK
CREATE SECRET to_be_dropped.secret2 as 'text'

statement OK
CREATE SECRET to_be_dropped.secret3 as 'text'

statement OK
DROP SCHEMA to_be_dropped CASCADE

# Secret validation
statement error secret value must be valid UTF-8
CREATE SECRET invalid_cert AS '\x80';