Materialize/test/sqllogictest/singlereplica_audit_log.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.

# Test expected population of mz_audit_events after some DDL statements.

mode cockroach

# Start from a pristine server
reset-server

statement ok
CREATE DATABASE test

statement ok
CREATE SCHEMA test.sc1

statement ok
CREATE SCHEMA test.sc2

statement ok
DROP SCHEMA test.sc1

statement ok
DROP DATABASE test

statement ok
CREATE ROLE foo

statement ok
DROP ROLE foo

statement ok
CREATE CLUSTER foo REPLICAS (r (SIZE '1'));

statement ok
CREATE MATERIALIZED VIEW v2 AS SELECT 1

statement ok
CREATE VIEW unmat AS SELECT 1

statement ok
CREATE TABLE t ()

statement ok
CREATE DEFAULT INDEX ON t

statement ok
ALTER VIEW unmat RENAME TO renamed

statement ok
CREATE OR REPLACE MATERIALIZED VIEW v2 AS SELECT 2

statement ok
CREATE DEFAULT INDEX ON renamed

statement ok
DROP VIEW renamed

statement ok
CREATE SOURCE s FROM LOAD GENERATOR COUNTER;

statement ok
DROP SOURCE s;

statement ok
CREATE SOURCE multiplex FROM LOAD GENERATOR AUCTION;

statement ok
CREATE TABLE accounts FROM SOURCE multiplex (REFERENCE accounts);

statement ok
CREATE TABLE auctions FROM SOURCE multiplex (REFERENCE auctions);

statement ok
CREATE TABLE bids FROM SOURCE multiplex (REFERENCE bids);

statement ok
CREATE TABLE organizations FROM SOURCE multiplex (REFERENCE organizations);

statement ok
CREATE TABLE users FROM SOURCE multiplex (REFERENCE users);

statement ok
ALTER CLUSTER REPLICA foo.r RENAME TO s;

statement ok
COMMENT ON CLUSTER foo IS 'test comment'

statement ok
COMMENT ON CLUSTER foo IS NULL

statement ok
ALTER CLUSTER foo RENAME TO bar;

statement ok
DROP CLUSTER REPLICA bar.s;

statement ok
DROP CLUSTER bar;

statement ok
ALTER MATERIALIZED VIEW v2 SET (RETAIN HISTORY FOR '5m');

statement ok
ALTER MATERIALIZED VIEW v2 RESET (RETAIN HISTORY);

statement ok
COMMENT ON MATERIALIZED VIEW v2 IS 'test comment'

statement ok
CREATE CONNECTION conn TO SSH TUNNEL (HOST 'host', PORT 22, USER 'materialize')

statement ok
ALTER CONNECTION conn ROTATE KEYS

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET max_aws_privatelink_connections = '10'
----
COMPLETE 0

simple conn=mz_system,user=mz_system
ALTER SYSTEM RESET max_aws_privatelink_connections
----
COMPLETE 0

simple conn=mz_system,user=mz_system
ALTER SYSTEM RESET ALL
----
COMPLETE 0

query ITTTT
SELECT id, event_type, object_type, details, user FROM mz_audit_events ORDER BY id
----
1  create  role  {"id":"u1","name":"materialize"}  NULL
2  grant  cluster  {"database_id":null,"grantee_id":"s2","privileges":"U","role_id":"p","schema_id":null}  NULL
3  grant  database  {"database_id":null,"grantee_id":"s2","privileges":"U","role_id":"p","schema_id":null}  NULL
4  grant  schema  {"database_id":null,"grantee_id":"s2","privileges":"U","role_id":"p","schema_id":null}  NULL
5  grant  type  {"database_id":null,"grantee_id":"p","privileges":"U","role_id":"p","schema_id":null}  NULL
6  create  database  {"id":"u1","name":"materialize"}  NULL
7  grant  database  {"grantee_id":"p","grantor_id":"s1","object_id":"Du1","privileges":"U"}  NULL
8  grant  database  {"grantee_id":"u1","grantor_id":"s1","object_id":"Du1","privileges":"UC"}  NULL
9  create  schema  {"database_name":"materialize","id":"3","name":"public"}  NULL
10  grant  schema  {"grantee_id":"u1","grantor_id":"s1","object_id":"Su1.u3","privileges":"UC"}  NULL
11  create  network-policy  {"id":"u1","name":"default"}  NULL
12  create  cluster  {"id":"u1","name":"quickstart"}  NULL
13  grant  cluster  {"grantee_id":"p","grantor_id":"s1","object_id":"Cu1","privileges":"U"}  NULL
14  grant  cluster  {"grantee_id":"u1","grantor_id":"s1","object_id":"Cu1","privileges":"UC"}  NULL
15  create  cluster-replica  {"billed_as":null,"cluster_id":"u1","cluster_name":"quickstart","disk":false,"internal":false,"logical_size":"2","reason":"system","replica_id":"u1","replica_name":"r1"}  NULL
16  grant  system  {"grantee_id":"s1","grantor_id":"s1","object_id":"SYSTEM","privileges":"RBNP"}  NULL
17  grant  system  {"grantee_id":"u1","grantor_id":"s1","object_id":"SYSTEM","privileges":"RBNP"}  NULL
18  alter  system  {"name":"enable_reduce_mfp_fusion","value":"on"}  mz_system
19  alter  system  {"name":"unsafe_enable_unsafe_functions","value":"on"}  mz_system
20  create  database  {"id":"u2","name":"test"}  materialize
21  create  schema  {"database_name":"test","id":"u9","name":"public"}  materialize
22  create  schema  {"database_name":"test","id":"u10","name":"sc1"}  materialize
23  create  schema  {"database_name":"test","id":"u11","name":"sc2"}  materialize
24  drop  schema  {"database_name":"test","id":"u10","name":"sc1"}  materialize
25  drop  schema  {"database_name":"test","id":"u9","name":"public"}  materialize
26  drop  schema  {"database_name":"test","id":"u11","name":"sc2"}  materialize
27  drop  database  {"id":"u2","name":"test"}  materialize
28  create  role  {"id":"u2","name":"foo"}  materialize
29  drop  role  {"id":"u2","name":"foo"}  materialize
30  create  cluster  {"id":"u2","name":"foo"}  materialize
31  create  cluster-replica  {"billed_as":null,"cluster_id":"u2","cluster_name":"foo","disk":true,"internal":false,"logical_size":"1","reason":"manual","replica_id":"u2","replica_name":"r"}  materialize
32  create  materialized-view  {"cluster_id":"u1","database":"materialize","id":"u1","item":"v2","schema":"public"}  materialize
33  create  view  {"database":"materialize","id":"u2","item":"unmat","schema":"public"}  materialize
34  create  table  {"database":"materialize","id":"u3","item":"t","schema":"public"}  materialize
35  create  index  {"cluster_id":"u1","database":"materialize","id":"u4","item":"t_primary_idx","schema":"public"}  materialize
36  alter  view  {"id":"u2","new_name":{"database":"materialize","item":"renamed","schema":"public"},"old_name":{"database":"materialize","item":"unmat","schema":"public"}}  materialize
37  drop  materialized-view  {"database":"materialize","id":"u1","item":"v2","schema":"public"}  materialize
38  create  materialized-view  {"cluster_id":"u1","database":"materialize","id":"u5","item":"v2","schema":"public"}  materialize
39  create  index  {"cluster_id":"u1","database":"materialize","id":"u6","item":"renamed_primary_idx","schema":"public"}  materialize
40  drop  index  {"database":"materialize","id":"u6","item":"renamed_primary_idx","schema":"public"}  materialize
41  drop  view  {"database":"materialize","id":"u2","item":"renamed","schema":"public"}  materialize
42  create  source  {"cluster_id":null,"database":"materialize","id":"u7","item":"s_progress","schema":"public","type":"progress"}  materialize
43  create  source  {"cluster_id":"u1","database":"materialize","id":"u8","item":"s","schema":"public","type":"load-generator"}  materialize
44  drop  source  {"database":"materialize","id":"u8","item":"s","schema":"public"}  materialize
45  drop  source  {"database":"materialize","id":"u7","item":"s_progress","schema":"public"}  materialize
46  create  source  {"cluster_id":null,"database":"materialize","id":"u9","item":"multiplex_progress","schema":"public","type":"progress"}  materialize
47  create  source  {"cluster_id":"u1","database":"materialize","id":"u10","item":"multiplex","schema":"public","type":"load-generator"}  materialize
48  create  table  {"database":"materialize","id":"u11","item":"accounts","schema":"public"}  materialize
49  create  table  {"database":"materialize","id":"u12","item":"auctions","schema":"public"}  materialize
50  create  table  {"database":"materialize","id":"u13","item":"bids","schema":"public"}  materialize
51  create  table  {"database":"materialize","id":"u14","item":"organizations","schema":"public"}  materialize
52  create  table  {"database":"materialize","id":"u15","item":"users","schema":"public"}  materialize
53  alter  cluster-replica  {"cluster_id":"u2","new_name":"s","old_name":"r","replica_id":"u2"}  materialize
54  comment  cluster  {"id":"Cluster(User(2))","name":"foo"}  materialize
55  comment  cluster  {"id":"Cluster(User(2))","name":"foo"}  materialize
56  alter  cluster  {"id":"u2","new_name":"bar","old_name":"foo"}  materialize
57  drop  cluster-replica  {"cluster_id":"u2","cluster_name":"bar","reason":"manual","replica_id":"u2","replica_name":"s"}  materialize
58  drop  cluster  {"id":"u2","name":"bar"}  materialize
59  alter  materialized-view  {"id":"u5","new_history":"'5m'","old_history":null}  materialize
60  alter  materialized-view  {"id":"u5","new_history":null,"old_history":"FOR␠'5m'"}  materialize
61  comment  materialized-view  {"id":"MaterializedView(User(5))","name":"materialize.public.v2"}  materialize
62  create  connection  {"database":"materialize","id":"u16","item":"conn","schema":"public"}  materialize
63  alter  connection  {"database":"materialize","id":"u16","item":"conn","schema":"public"}  materialize
64  alter  system  {"name":"max_aws_privatelink_connections","value":"10"}  mz_system
65  alter  system  {"name":"max_aws_privatelink_connections","value":null}  mz_system
66  alter  system  null  mz_system

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET unsafe_mock_audit_event_timestamp = 666
----
COMPLETE 0

statement ok
CREATE TABLE tt ()

query ITTTTT
SELECT id, event_type, object_type, details, user, occurred_at FROM mz_audit_events ORDER BY id DESC LIMIT 1
----
68  create  table  {"database":"materialize","id":"u17","item":"tt","schema":"public"}  materialize  1970-01-01␠00:00:00.666+00

simple conn=mz_system,user=mz_system
ALTER SYSTEM RESET unsafe_mock_audit_event_timestamp
----
COMPLETE 0

statement ok
DROP TABLE tt

query B
SELECT occurred_at::text = '1970-01-01 00:00:00.666+00' FROM mz_audit_events ORDER BY id DESC LIMIT 1
----
false

query TTTTBBBT
SELECT replica_id, cluster_name, replica_name, size, created_at IS NOT NULL, dropped_at IS NOT NULL, created_at < dropped_at, credits_per_hour FROM mz_internal.mz_cluster_replica_history ORDER BY created_at
----
u1  quickstart  r1  2  true  false  NULL  1
u2  foo  r  1  true  true  true  1

simple conn=mz_system,user=mz_system
CREATE ROLE r1;
----
COMPLETE 0

simple conn=mz_system,user=mz_system
GRANT SELECT ON t TO r1;
----
COMPLETE 0

query ITTTT
SELECT id, event_type, object_type, details, user FROM mz_audit_events ORDER BY id DESC LIMIT 1
----
72  grant  table  {"grantee_id":"u3","grantor_id":"u1","object_id":"Iu3","privileges":"r"}  mz_system

simple conn=mz_system,user=mz_system
REVOKE SELECT ON t FROM r1;
----
COMPLETE 0

query ITTTT
SELECT id, event_type, object_type, details, user FROM mz_audit_events ORDER BY id DESC LIMIT 1
----
73  revoke  table  {"grantee_id":"u3","grantor_id":"u1","object_id":"Iu3","privileges":"r"}  mz_system

simple conn=mz_system,user=mz_system
ALTER DEFAULT PRIVILEGES FOR ROLE r1 IN SCHEMA public GRANT SELECT ON TABLES to PUBLIC;
----
COMPLETE 0

query ITTTT
SELECT id, event_type, object_type, details, user FROM mz_audit_events ORDER BY id DESC LIMIT 1
----
74  grant  table  {"database_id":"u1","grantee_id":"p","privileges":"r","role_id":"u3","schema_id":"u3"}  mz_system

simple conn=mz_system,user=mz_system
ALTER DEFAULT PRIVILEGES FOR ROLE r1 IN SCHEMA public REVOKE SELECT ON TABLES FROM PUBLIC;
----
COMPLETE 0

query ITTTT
SELECT id, event_type, object_type, details, user FROM mz_audit_events ORDER BY id DESC LIMIT 1
----
75  revoke  table  {"database_id":"u1","grantee_id":"p","privileges":"r","role_id":"u3","schema_id":"u3"}  mz_system

statement ok
CREATE TABLE t1 (a INT);

simple conn=mz_system,user=mz_system
ALTER TABLE t1 OWNER to r1;
----
COMPLETE 0

query ITTTT
SELECT id, event_type, object_type, details, user FROM mz_audit_events ORDER BY id DESC LIMIT 1
----
77  alter  table  {"new_owner_id":"u3","object_id":"Iu18","old_owner_id":"u1"}  mz_system

# Test events for auto-created users, which have the username only in the event details, but not the user column.
simple conn=c,user=new_user
SELECT 1
----
1
COMPLETE 1

query ITTTT
SELECT id, event_type, object_type, details, user FROM mz_audit_events ORDER BY id DESC LIMIT 1
----
78  create  role  {"id":"u4","name":"new_user"}  NULL