# 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 # Give materialize the CREATEROLE system privilege. simple conn=mz_system,user=mz_system GRANT CREATEROLE ON SYSTEM TO materialize; ---- COMPLETE 0 statement ok CREATE VIEW role_members AS SELECT role.name AS role, member.name AS member, grantor.name AS grantor FROM mz_role_members membership LEFT JOIN mz_roles role ON membership.role_id = role.id LEFT JOIN mz_roles member ON membership.member = member.id LEFT JOIN mz_roles grantor ON membership.grantor = grantor.id statement ok CREATE ROLE joe statement ok CREATE ROLE group1 query TTT rowsort SELECT * FROM role_members ---- query B rowsort SELECT pg_has_role('joe', 'group1', 'USAGE') ---- false query B rowsort SELECT has_role('joe', 'group1', 'USAGE') ---- false query B rowsort SELECT pg_has_role((SELECT oid FROM mz_roles WHERE name = 'joe'), 'group1', 'USAGE') ---- false query B rowsort SELECT has_role((SELECT oid FROM mz_roles WHERE name = 'joe'), 'group1', 'USAGE') ---- false query B rowsort SELECT pg_has_role('joe', (SELECT oid FROM mz_roles WHERE name = 'group1'), 'USAGE') ---- false query B rowsort SELECT has_role('joe', (SELECT oid FROM mz_roles WHERE name = 'group1'), 'USAGE') ---- false query B rowsort SELECT pg_has_role((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_roles WHERE name = 'group1'), 'USAGE') ---- false query B rowsort SELECT has_role((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_roles WHERE name = 'group1'), 'USAGE') ---- false statement ok GRANT group1 TO joe query TTT rowsort SELECT * FROM role_members ---- group1 joe mz_system query B rowsort SELECT pg_has_role('joe', 'group1', 'USAGE') ---- true query B rowsort SELECT has_role('joe', 'group1', 'USAGE') ---- true query B rowsort SELECT pg_has_role((SELECT oid FROM mz_roles WHERE name = 'joe'), 'group1', 'USAGE') ---- true query B rowsort SELECT has_role((SELECT oid FROM mz_roles WHERE name = 'joe'), 'group1', 'USAGE') ---- true query B rowsort SELECT pg_has_role('joe', (SELECT oid FROM mz_roles WHERE name = 'group1'), 'USAGE') ---- true query B rowsort SELECT has_role('joe', (SELECT oid FROM mz_roles WHERE name = 'group1'), 'USAGE') ---- true query B rowsort SELECT pg_has_role((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_roles WHERE name = 'group1'), 'USAGE') ---- true query B rowsort SELECT has_role((SELECT oid FROM mz_roles WHERE name = 'joe'), (SELECT oid FROM mz_roles WHERE name = 'group1'), 'USAGE') ---- true # Dropping a role also removes it from role_members simple conn=mz_system,user=mz_system DROP ROLE group1 ---- COMPLETE 0 query TTT rowsort SELECT * FROM mz_role_members ---- statement ok CREATE ROLE group1 query B rowsort SELECT pg_has_role('joe', 'group1', 'USAGE') ---- false query B rowsort SELECT has_role('joe', 'group1', 'USAGE') ---- false statement ok GRANT group1 TO joe query TTT rowsort SELECT * FROM role_members ---- group1 joe mz_system query B rowsort SELECT pg_has_role('joe', 'group1', 'USAGE') ---- true query B rowsort SELECT has_role('joe', 'group1', 'USAGE') ---- true # Dropped roles have their membership revoked simple conn=mz_system,user=mz_system DROP ROLE joe ---- COMPLETE 0 query TTT rowsort SELECT * FROM mz_role_members ---- statement ok CREATE ROLE joe statement ok GRANT group1 TO joe statement ok CREATE ROLE group2 simple conn=mz_system,user=mz_system GRANT group2 TO joe ---- COMPLETE 0 query TTT rowsort SELECT * FROM role_members ---- group1 joe mz_system group2 joe mz_system query B rowsort SELECT pg_has_role('joe', 'group1', 'USAGE') ---- true query B rowsort SELECT has_role('joe', 'group1', 'USAGE') ---- true query B rowsort SELECT pg_has_role('joe', 'group2', 'USAGE') ---- true query B rowsort SELECT has_role('joe', 'group2', 'USAGE') ---- true query B rowsort SELECT pg_has_role('group1', 'group2', 'USAGE') ---- false query B rowsort SELECT has_role('group1', 'group2', 'USAGE') ---- false statement ok GRANT group2 TO group1 query TTT rowsort SELECT * FROM role_members ---- group1 joe mz_system group2 joe mz_system group2 group1 mz_system query B rowsort SELECT pg_has_role('joe', 'group1', 'USAGE') ---- true query B rowsort SELECT has_role('joe', 'group1', 'USAGE') ---- true query B rowsort SELECT pg_has_role('joe', 'group2', 'USAGE') ---- true query B rowsort SELECT has_role('joe', 'group2', 'USAGE') ---- true query B rowsort SELECT pg_has_role('group1', 'group2', 'USAGE') ---- true query B rowsort SELECT has_role('group1', 'group2', 'USAGE') ---- true # Redundant grants don't error or show up multiple times in mz_role_membership or change the grantor simple conn=mz_system,user=mz_system GRANT group1 TO joe ---- COMPLETE 0 query TTT rowsort SELECT * FROM role_members ---- group1 joe mz_system group2 joe mz_system group2 group1 mz_system # Test circular membership errors statement error role "joe" is a member of role "joe" GRANT joe TO joe statement error role "joe" is a member of role "group1" GRANT joe TO group1 statement ok REVOKE group1 FROM joe query TTT rowsort SELECT * FROM role_members ---- group2 joe mz_system group2 group1 mz_system query B rowsort SELECT pg_has_role('joe', 'group2', 'USAGE') ---- true query B rowsort SELECT has_role('joe', 'group2', 'USAGE') ---- true query B rowsort SELECT pg_has_role('joe', 'group1', 'USAGE') ---- false query B rowsort SELECT has_role('joe', 'group1', 'USAGE') ---- false query B rowsort SELECT pg_has_role('group1', 'joe', 'USAGE') ---- false query B rowsort SELECT has_role('group1', 'joe', 'USAGE') ---- false query B rowsort SELECT pg_has_role('group1', 'group2', 'USAGE') ---- true query B rowsort SELECT has_role('group1', 'group2', 'USAGE') ---- true query B rowsort SELECT pg_has_role('group2', 'joe', 'USAGE') ---- false query B rowsort SELECT has_role('group2', 'joe', 'USAGE') ---- false query B rowsort SELECT pg_has_role('group2', 'group1', 'USAGE') ---- false query B rowsort SELECT has_role('group2', 'group1', 'USAGE') ---- false # Redundant revokes don't error statement ok REVOKE group1 FROM joe query TTT rowsort SELECT * FROM role_members ---- group2 joe mz_system group2 group1 mz_system # Dropped roles are revoked from all members statement ok DROP ROLE group2 query TTT rowsort SELECT * FROM mz_role_members ---- query TTT rowsort SELECT * FROM role_members ---- # Dropped roles have their membership revoked statement ok DROP ROLE joe query TTT rowsort SELECT * FROM role_members ---- statement ok CREATE ROLE joe # Cannot grant or revoke system role statement error db error: ERROR: role name "mz_system" cannot be granted GRANT mz_system TO joe statement error role name "mz_system" is reserved GRANT joe TO mz_system statement error db error: ERROR: role name "mz_system" cannot be granted REVOKE mz_system FROM joe statement error role name "mz_system" is reserved REVOKE joe FROM mz_system # Prevent granting and revoking to/from PUBLIC role statement error role name "public" is reserved GRANT group1 TO public statement error db error: ERROR: role name "public" cannot be granted GRANT public TO group1 statement error role name "public" is reserved REVOKE group1 FROM public statement error db error: ERROR: role name "public" cannot be granted REVOKE public FROM group1 statement ok DROP ROLE group1 statement ok DROP ROLE joe # SHOW ROLES/USERS query TT rowsort show roles ---- materialize (empty) query TT rowsort show users ---- materialize (empty) # Test grant/revoke multiple roles statement ok CREATE ROLE joe statement ok CREATE ROLE group1 statement ok CREATE ROLE group2 statement ok CREATE ROLE group3 statement error unknown role 'bob' GRANT group3 TO joe, group1, bob query TTT rowsort SELECT * FROM role_members ---- statement error role name "mz_system" is reserved GRANT group3 TO joe, group1, mz_system query TTT rowsort SELECT * FROM role_members ---- statement ok GRANT group3 TO joe, group1 query TTT rowsort SELECT * FROM role_members ---- group3 joe mz_system group3 group1 mz_system statement error role "joe" is a member of role "group3" GRANT joe TO group1, group3 query TTT rowsort SELECT * FROM role_members ---- group3 joe mz_system group3 group1 mz_system statement ok GRANT group3 TO group1, group2 query TTT rowsort SELECT * FROM role_members ---- group3 joe mz_system group3 group1 mz_system group3 group2 mz_system statement error unknown role 'bob' REVOKE group3 FROM joe, group1, bob query TTT rowsort SELECT * FROM role_members ---- group3 joe mz_system group3 group1 mz_system group3 group2 mz_system statement error role name "mz_system" is reserved REVOKE group3 FROM joe, group1, mz_system query TTT rowsort SELECT * FROM role_members ---- group3 joe mz_system group3 group1 mz_system group3 group2 mz_system statement ok REVOKE group3 FROM joe, group1 query TTT rowsort SELECT * FROM role_members ---- group3 group2 mz_system statement ok REVOKE group3 FROM joe, group2 query TTT rowsort SELECT * FROM role_members ---- # Test pg_auth_members statement ok GRANT group3 TO joe, group1, group2 statement ok GRANT group1 TO joe query I rowsort SELECT COUNT(*) FROM pg_auth_members ---- 4 query TTTB rowsort SELECT role.name, member.name, grantor.name, members.admin_option FROM pg_auth_members members LEFT JOIN mz_roles role ON members.roleid = role.oid LEFT JOIN mz_roles member ON members.member = member.oid LEFT JOIN mz_roles grantor ON members.grantor = grantor.oid ---- group1 joe mz_system false group3 joe mz_system false group3 group1 mz_system false group3 group2 mz_system false statement ok DROP ROLE group1, group2, group3, joe statement ok CREATE ROLE joe statement ok CREATE ROLE mike statement ok CREATE ROLE group1 statement ok CREATE ROLE group2 statement ok GRANT group1, group2 TO joe, mike query TTTB rowsort SELECT role.name, member.name, grantor.name, members.admin_option FROM pg_auth_members members LEFT JOIN mz_roles role ON members.roleid = role.oid LEFT JOIN mz_roles member ON members.member = member.oid LEFT JOIN mz_roles grantor ON members.grantor = grantor.oid ---- group1 joe mz_system false group2 joe mz_system false group1 mike mz_system false group2 mike mz_system false statement ok REVOKE group1, group2 FROM joe, mike query TTTB rowsort SELECT role.name, member.name, grantor.name, members.admin_option FROM pg_auth_members members LEFT JOIN mz_roles role ON members.roleid = role.oid LEFT JOIN mz_roles member ON members.member = member.oid LEFT JOIN mz_roles grantor ON members.grantor = grantor.oid ---- statement ok DROP ROLE group1, group2, joe, mike # Test recursive check of pg_has_role simple conn=mz_system,user=mz_system CREATE ROLE group1; ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE ROLE group2; ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE ROLE joe; ---- COMPLETE 0 simple conn=mz_system,user=mz_system CREATE ROLE other; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT group1 to joe; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT group2 to group1; ---- COMPLETE 0 simple conn=mz_system,user=mz_system GRANT group1 to other; ---- COMPLETE 0 query B rowsort SELECT pg_has_role('joe', 'group2', 'USAGE') ---- true query B rowsort SELECT has_role('joe', 'group2', 'USAGE') ---- true query B rowsort SELECT pg_has_role('joe', 'group1', 'USAGE') ---- true query B rowsort SELECT has_role('joe', 'group1', 'USAGE') ---- true query B rowsort SELECT pg_has_role('group1', 'joe', 'USAGE') ---- false query B rowsort SELECT has_role('group1', 'joe', 'USAGE') ---- false query B rowsort SELECT pg_has_role('group1', 'group2', 'USAGE') ---- true query B rowsort SELECT has_role('group1', 'group2', 'USAGE') ---- true query B rowsort SELECT pg_has_role('group2', 'joe', 'USAGE') ---- false query B rowsort SELECT has_role('group2', 'joe', 'USAGE') ---- false query B rowsort SELECT pg_has_role('group2', 'group1', 'USAGE') ---- false query B rowsort SELECT has_role('group2', 'group1', 'USAGE') ---- false simple conn=mz_system,user=mz_system DROP ROLE group1, group2, joe, other; ---- COMPLETE 0 # Test two input variant of pg_has_role. simple conn=mz_system,user=mz_system CREATE ROLE group1; ---- COMPLETE 0 query B rowsort SELECT pg_has_role('group1', 'USAGE') ---- false query B rowsort SELECT has_role('group1', 'USAGE') ---- false query B rowsort SELECT pg_has_role('group1', 'MEMBER') ---- false query B rowsort SELECT has_role('group1', 'MEMBER') ---- false simple conn=mz_system,user=mz_system GRANT group1 TO materialize ---- COMPLETE 0 query B rowsort SELECT pg_has_role('group1', 'USAGE') ---- true query B rowsort SELECT has_role('group1', 'USAGE') ---- true query B rowsort SELECT pg_has_role('group1', 'MEMBER') ---- true query B rowsort SELECT has_role('group1', 'MEMBER') ---- true simple conn=mz_system,user=mz_system REVOKE group1 FROM materialize ---- COMPLETE 0 query B rowsort SELECT pg_has_role('group1', 'USAGE') ---- false query B rowsort SELECT has_role('group1', 'USAGE') ---- false query B rowsort SELECT pg_has_role('group1', 'MEMBER') ---- false query B rowsort SELECT has_role('group1', 'MEMBER') ---- false simple conn=mz_system,user=mz_system DROP ROLE group1 ---- COMPLETE 0 # Test pg_has_role error scenarios. ## If any input is NULL then the result is NULL. query B rowsort SELECT pg_has_role(NULL, 'materialize', 'USAGE') ---- NULL query B rowsort SELECT has_role(NULL, 'materialize', 'USAGE') ---- NULL query B rowsort SELECT pg_has_role('materialize', NULL, 'MEMBER') ---- NULL query B rowsort SELECT has_role('materialize', NULL, 'MEMBER') ---- NULL query B rowsort SELECT pg_has_role('materialize', 'materialize', NULL) ---- NULL query B rowsort SELECT has_role('materialize', 'materialize', NULL) ---- NULL ## If any of the text inputs are invalid then the query should error. query error role "fake-role" does not exist SELECT pg_has_role('fake-role', 'materialize', 'USAGE') query error role "fake-role" does not exist SELECT pg_has_role('materialize', 'fake-role', 'USAGE') query error unrecognized privilege type: "fake privilege" SELECT pg_has_role('materialize', 'materialize', 'fake privilege') ## If any of the oid inputs are invalid then the query should be false. query B rowsort SELECT pg_has_role(((SELECT MAX(oid::int8) FROM mz_roles) + 1)::text::oid, 'materialize', 'MEMBER') ---- false query B rowsort SELECT has_role(((SELECT MAX(oid::int8) FROM mz_roles) + 1)::text::oid, 'materialize', 'MEMBER') ---- false query B rowsort SELECT pg_has_role('materialize', ((SELECT MAX(oid::int8) FROM mz_roles) + 1)::text::oid, 'USAGE') ---- false query B rowsort SELECT has_role('materialize', ((SELECT MAX(oid::int8) FROM mz_roles) + 1)::text::oid, 'USAGE') ---- false ## Public role isn't accepted query error role "public" does not exist SELECT pg_has_role('materialize', 'public', 'USAGE') query error role "public" does not exist SELECT pg_has_role('public', 'materialize', 'USAGE') # Test information_schema.applicable_roles statement ok CREATE ROLE r1 statement ok CREATE ROLE r2 statement ok CREATE ROLE r3 statement ok CREATE ROLE r4 statement ok GRANT r2 TO r1 statement ok GRANT r3 TO r2 statement ok GRANT r3 TO r4 simple conn=r1,user=r1 SELECT * FROM information_schema.applicable_roles ---- r1,r2,NO r2,r3,NO COMPLETE 2 simple conn=mz_system,user=mz_system SELECT * FROM information_schema.applicable_roles ---- r1,r2,NO r2,r3,NO r4,r3,NO COMPLETE 3 simple conn=r1,user=r1,rowsort SELECT * FROM information_schema.enabled_roles ---- r1 r2 r3 COMPLETE 3 simple conn=mz_system,user=mz_system,rowsort SELECT * FROM information_schema.enabled_roles ---- r1 r2 r3 r4 mz_system mz_monitor mz_support materialize mz_analytics mz_monitor_redacted COMPLETE 10 statement ok DROP ROLE r1, r2, r3, r4 # Disable RBAC checks simple conn=mz_system,user=mz_system ALTER SYSTEM SET enable_rbac_checks TO false; ---- COMPLETE 0