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

mode cockroach

reset-server

# Enable rbac checks.
simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_rbac_checks TO true;
----
COMPLETE 0

# Test mz_aclitem type and functions

statement ok
CREATE ROLE test_role

query T
SELECT mz_internal.make_mz_aclitem('u1', 'u2', 'CREATE')
----
u1=C/u2

query T
SELECT mz_internal.make_mz_aclitem('u1', 'u2', 'CREATE, USAGE')
----
u1=UC/u2

query T
SELECT mz_internal.make_mz_aclitem('u1', 'u2', 'CREATE')::text
----
materialize=C/test_role

query T
SELECT mz_internal.make_mz_aclitem('u1', 'u2', 'CREATE, USAGE')::text
----
materialize=UC/test_role

query T
SELECT mz_internal.make_mz_aclitem('p', 'u2', 'CREATE')
----
=C/u2

query T
SELECT mz_internal.make_mz_aclitem('p', 'u2', 'CREATE, USAGE')
----
=UC/u2

query T
SELECT mz_internal.make_mz_aclitem('p', 'u2', 'SELECT')::text
----
=r/test_role

query T
SELECT mz_internal.make_mz_aclitem('p', 'u2', 'INSERT, SELECT')::text
----
=ar/test_role

query error mz_aclitem grantor cannot be PUBLIC role
SELECT mz_internal.make_mz_aclitem('u1', 'p', 'CREATE')

query error mz_aclitem grantor cannot be PUBLIC role
SELECT mz_internal.make_mz_aclitem('u1', 'p', 'CREATE, USAGE')

query T
SELECT mz_internal.mz_aclitem_grantee(mz_internal.make_mz_aclitem('u1', 'u2', 'DELETE'))
----
u1

query T
SELECT mz_internal.mz_aclitem_grantee(mz_internal.make_mz_aclitem('p', 'u2', 'DELETE'))
----
p

query T
SELECT mz_internal.mz_aclitem_grantor(mz_internal.make_mz_aclitem('u1', 'u2', 'DELETE'))
----
u2

query T
SELECT mz_internal.mz_aclitem_privileges(mz_internal.make_mz_aclitem('u1', 'u2', 'DELETE'))
----
d

query B
SELECT mz_internal.make_mz_aclitem('u1', 'u2', 'DELETE') = mz_internal.make_mz_aclitem('u1', 'u2', 'DELETE')
----
true

query B
SELECT mz_internal.make_mz_aclitem('u1', 'u2', 'DELETE') <> mz_internal.make_mz_aclitem('u1', 'u2', 'DELETE')
----
false

query B
SELECT mz_internal.make_mz_aclitem('u3', 'u2', 'DELETE') = mz_internal.make_mz_aclitem('u1', 'u2', 'DELETE')
----
false

query B
SELECT mz_internal.make_mz_aclitem('u1', 'u3', 'DELETE') = mz_internal.make_mz_aclitem('u1', 'u2', 'DELETE')
----
false

query B
SELECT mz_internal.make_mz_aclitem('u1', 'u3', 'DELETE, USAGE') = mz_internal.make_mz_aclitem('u1', 'u2', 'DELETE')
----
false

query B
SELECT mz_internal.make_mz_aclitem('u3', 'u2', 'DELETE') <> mz_internal.make_mz_aclitem('u1', 'u2', 'DELETE')
----
true

query B
SELECT mz_internal.make_mz_aclitem('u1', 'u3', 'DELETE') <> mz_internal.make_mz_aclitem('u1', 'u2', 'DELETE')
----
true

query B
SELECT mz_internal.make_mz_aclitem('u1', 'u3', 'DELETE, USAGE') <> mz_internal.make_mz_aclitem('u1', 'u2', 'DELETE')
----
true

# RoleIds that don't exist are printed as IDs instead of names

query T
SELECT mz_internal.make_mz_aclitem('u99991', 'u2', 'CREATE')::text
----
u99991=C/test_role

query T
SELECT mz_internal.make_mz_aclitem('u1', 'u87398', 'CREATE')::text
----
materialize=C/u87398

query T
SELECT mz_internal.make_mz_aclitem('u3251', 's345', 'CREATE')::text
----
u3251=C/s345

# Test parsing errors

query error couldn't parse role id 'uasdf7890ad'
SELECT mz_internal.make_mz_aclitem('u1', 'uasdf7890ad', 'CREATE')

query error couldn't parse role id 'sd98fas9df8'
SELECT mz_internal.make_mz_aclitem('sd98fas9df8', 's1', 'CREATE')

query error unrecognized privilege type: "asdfa ljefioj"
SELECT mz_internal.make_mz_aclitem('u1', 's1', 'asdfa ljefioj')

# Test mz_acl_item_contains_privilege NULLs

query B
SELECT mz_internal.mz_acl_item_contains_privilege(NULL, 'SELECT')
----
NULL

query B
SELECT mz_internal.mz_acl_item_contains_privilege(mz_internal.make_mz_aclitem('u1', 'u2', 'CREATE'), NULL)
----
NULL

query B
SELECT mz_internal.mz_acl_item_contains_privilege(NULL, NULL)
----
NULL

query B
SELECT mz_internal.mz_acl_item_contains_privilege(mz_internal.make_mz_aclitem('u1', 'u2', 'USAGE'), 'CREATE');
----
false

query B
SELECT mz_internal.mz_acl_item_contains_privilege(mz_internal.make_mz_aclitem('u1', 'u2', 'USAGE'), 'USAGE');
----
true

# Test aclitem type and functions

statement ok
CREATE TABLE t (a aclitem)

query T
SELECT a::text from t
----

statement ok
DROP TABLE t

query error binary encoding of aclitem types does not exist
SELECT makeaclitem(1, 2, 'CREATE', false)

query T
SELECT makeaclitem(1, 2, 'CREATE', false)::text
----
1=C/2

query T
SELECT makeaclitem(1, 2, 'CREATE, USAGE', false)::text
----
1=UC/2

query T
SELECT makeaclitem((SELECT oid FROM mz_roles WHERE name = 'materialize'), (SELECT oid FROM mz_roles WHERE name = 'test_role'), 'CREATE', false)::text
----
materialize=C/test_role

query T
SELECT makeaclitem((SELECT oid FROM mz_roles WHERE name = 'materialize'), (SELECT oid FROM mz_roles WHERE name = 'test_role'), 'CREATE, USAGE', false)::text
----
materialize=UC/test_role

query T
SELECT makeaclitem(0, 2, 'CREATE', false)::text
----
=C/2

query T
SELECT makeaclitem(0, 2, 'CREATE, USAGE', false)::text
----
=UC/2

query T
SELECT makeaclitem(0, (SELECT oid FROM mz_roles WHERE name = 'test_role'), 'SELECT', false)::text
----
=r/test_role

query T
SELECT makeaclitem(0, (SELECT oid FROM mz_roles WHERE name = 'test_role'), 'INSERT, SELECT', false)::text
----
=ar/test_role

query B
SELECT makeaclitem(1, 2, 'DELETE', false) = makeaclitem(1, 2, 'DELETE', false)
----
true

query B
SELECT makeaclitem(3, 2, 'DELETE', false) = makeaclitem(1, 2, 'DELETE', false)
----
false

query B
SELECT makeaclitem(1, 3, 'DELETE', false) = makeaclitem(1, 2, 'DELETE', false)
----
false

query B
SELECT makeaclitem(1, 3, 'DELETE, USAGE', false) = makeaclitem(1, 2, 'DELETE', false)
----
false

# Role OIDs that don't exist are printed as IDs instead of names

query T
SELECT makeaclitem(99991, (SELECT oid FROM mz_roles WHERE name = 'test_role'), 'CREATE', false)::text
----
99991=C/test_role

query T
SELECT makeaclitem((SELECT oid FROM mz_roles WHERE name = 'materialize'), 87398, 'CREATE', false)::text
----
materialize=C/87398

query T
SELECT makeaclitem(3251, 345, 'CREATE', false)::text
----
3251=C/345

# Test parsing errors

query error unrecognized privilege type: "asdfa ljefioj"
SELECT makeaclitem(1, 1, 'asdfa ljefioj', false)::text

# GRANT OPTION isn't implemented.

query error GRANT OPTION not yet supported
SELECT makeaclitem(1, 2, 'CREATE', true)::text

# aclexplode

query T rowsort
SELECT aclexplode(ARRAY[makeaclitem(1, 2, 'SELECT, INSERT, DELETE', false), makeaclitem(3, 4, 'USAGE', false)])
----
(4,3,USAGE,f)
(2,1,DELETE,f)
(2,1,INSERT,f)
(2,1,SELECT,f)

query error ACL arrays must not contain null values
SELECT aclexplode(array[null]::aclitem[]);

# mz_aclexplode

query T
SELECT mz_internal.mz_aclexplode(ARRAY[mz_internal.make_mz_aclitem('u1', 'u2', 'SELECT, INSERT, DELETE'), mz_internal.make_mz_aclitem('u3', 'u4', 'USAGE')])
----
(u4,u3,USAGE,f)
(u2,u1,DELETE,f)
(u2,u1,INSERT,f)
(u2,u1,SELECT,f)

query error MZ_ACL arrays must not contain null values
SELECT mz_internal.mz_aclexplode(array[null]::mz_catalog.mz_aclitem[]);

# Test casting to/from aclitem and mz_aclitem

query B
SELECT mz_internal.make_mz_aclitem('u1', 'u2', 'CREATE, USAGE')::aclitem = makeaclitem((SELECT oid FROM mz_roles WHERE name = 'materialize'), (SELECT oid FROM mz_roles WHERE name = 'test_role'), 'CREATE, USAGE', false)
----
true

query B
SELECT mz_internal.make_mz_aclitem('p', 'u2', 'CREATE, USAGE')::aclitem = makeaclitem(0, (SELECT oid FROM mz_roles WHERE name = 'test_role'), 'CREATE, USAGE', false)
----
true

query B
SELECT makeaclitem((SELECT oid FROM mz_roles WHERE name = 'materialize'), (SELECT oid FROM mz_roles WHERE name = 'test_role'), 'CREATE, USAGE', false)::mz_catalog.mz_aclitem = mz_internal.make_mz_aclitem('u1', 'u2', 'CREATE, USAGE')
----
true

query B
SELECT makeaclitem(0, (SELECT oid FROM mz_roles WHERE name = 'test_role'), 'CREATE, USAGE', false)::mz_catalog.mz_aclitem = mz_internal.make_mz_aclitem('p', 'u2', 'CREATE, USAGE')
----
true

query T
SELECT mz_internal.make_mz_aclitem('u99991', 'u2', 'CREATE')::aclitem::text
----
NULL

query T
SELECT mz_internal.make_mz_aclitem('u1', 'u87398', 'CREATE')::aclitem::text
----
NULL

query T
SELECT mz_internal.make_mz_aclitem('u3251', 's345', 'CREATE')::aclitem::text
----
NULL

query T
SELECT makeaclitem(99991, (SELECT oid FROM mz_roles WHERE name = 'test_role'), 'CREATE', false)::mz_catalog.mz_aclitem
----
NULL

query T
SELECT makeaclitem((SELECT oid FROM mz_roles WHERE name = 'materialize'), 87398, 'CREATE', false)::mz_catalog.mz_aclitem
----
NULL

query T
SELECT makeaclitem(3251, 345, 'CREATE', false)::mz_catalog.mz_aclitem
----
NULL

# Disable rbac checks.

simple conn=mz_system,user=mz_system
ALTER SYSTEM SET enable_rbac_checks TO false;
----
COMPLETE 0