search

dolthub / doltgresql

DoltgreSQL - Version Controlled PostgreSQL
Apache License 2.0
1.12k stars 25 forks source link

Added GRANT, REVOKE, Privilege Checking, Ownership, and persisting changes #863

Closed Hydrocharged closed 2 weeks ago

Hydrocharged commented 1 month ago

This adds the majority of the core functionality that was missing for users, authorization, and privilege checking. This is missing rigorous testing, but that will be added in a separate PR (both engine and bats). All changes that will accompany those tests (such as missing statement support) will also be added in separate PRs.

github-actions[bot] commented 2 weeks ago
Main PR
Total 42090 42090
Successful 14026 14213
Failures 28064 27877
Partial Successes[^1] 4720 4717
Main PR
Successful 33.3238% 33.7681%
Failures 66.6762% 66.2319%

${\color{lightgreen}Progressions}$

cluster

QUERY: GRANT SELECT ON clstr_2 TO regress_clstr_user;

copy2

QUERY: GRANT SELECT ON TABLE rls_t1 TO regress_rls_copy_user;
QUERY: GRANT SELECT (a, b) ON TABLE rls_t1 TO regress_rls_copy_user_colperms;

create_procedure

QUERY: GRANT INSERT ON cp_test TO regress_cp_user1;

create_role

QUERY: REVOKE ALL PRIVILEGES ON tenant_table FROM PUBLIC;

create_table_like

QUERY: INSERT INTO test_like_gen_1 (a) VALUES (1);
QUERY: SELECT * FROM test_like_gen_1;
QUERY: CREATE TABLE test_like_gen_2 (LIKE test_like_gen_1);
QUERY: INSERT INTO test_like_gen_2 (a) VALUES (1);
QUERY: CREATE TABLE test_like_4a (LIKE test_like_4);
QUERY: INSERT INTO test_like_4a (a) VALUES(11);

dependency

QUERY: GRANT ALL ON TABLE deptest TO regress_dep_user, regress_dep_user2;
QUERY: REVOKE ALL ON deptest FROM regress_dep_user2;
QUERY: GRANT ALL ON deptest1 TO regress_dep_user1 WITH GRANT OPTION;
QUERY: GRANT ALL ON deptest1 TO regress_dep_user2;
QUERY: GRANT ALL ON deptest1 TO regress_dep_user1;

equivclass

QUERY: grant select on ec0 to regress_user_ectest;
QUERY: grant select on ec1 to regress_user_ectest;
QUERY: revoke select on ec0 from regress_user_ectest;
QUERY: revoke select on ec1 from regress_user_ectest;

event_trigger

QUERY: grant all on table event_trigger_fire1 to public;
QUERY: revoke all on table event_trigger_fire1 from public;

foreign_key

QUERY: grant references on fk_notpartitioned_pk to regress_other_partitioned_fk_owner;
QUERY: revoke all on fk_notpartitioned_pk from regress_other_partitioned_fk_owner;

generated

QUERY: UPDATE gtest1 SET a = 3 WHERE b = 4;
QUERY: INSERT INTO gtest_normal_child (a) VALUES (2);
QUERY: INSERT INTO gtest_normal_child2 (a) VALUES (3);
QUERY: INSERT INTO gtest3 (a) VALUES (1), (2), (3), (NULL);
QUERY: UPDATE gtest3 SET a = 22 WHERE a = 2;
QUERY: INSERT INTO gtest3a (a) VALUES ('a'), ('b'), ('c'), (NULL);
QUERY: UPDATE gtest3a SET a = 'bb' WHERE a = 'b';
QUERY: INSERT INTO gtest1 (a) VALUES (1), (2);
QUERY: INSERT INTO gtest3 (a) VALUES (1), (2);
QUERY: INSERT INTO gtest_varlena (a) VALUES('01234567890123456789');
QUERY: INSERT INTO gtest_varlena (a) VALUES(NULL);
QUERY: ALTER TABLE gtest10a DROP COLUMN b;
QUERY: INSERT INTO gtest10a (a) VALUES (1);
QUERY: GRANT SELECT (a, c) ON gtest11s TO regress_user11;
QUERY: GRANT SELECT (a, c) ON gtest12s TO regress_user11;
QUERY: INSERT INTO gtest20 (a) VALUES (10);
QUERY: INSERT INTO gtest20a (a) VALUES (10);
QUERY: INSERT INTO gtest20a (a) VALUES (30);
QUERY: ALTER TABLE gtest20a ADD CHECK (b < 50);
QUERY: INSERT INTO gtest20b (a) VALUES (10);
QUERY: INSERT INTO gtest20b (a) VALUES (30);
QUERY: CREATE INDEX gtest22c_b_idx ON gtest22c (b);
QUERY: INSERT INTO gtest_parent (f1, f2) VALUES ('2016-07-15', 1);
QUERY: INSERT INTO gtest27 (a, b) VALUES (3, 7), (4, 11);
QUERY: INSERT INTO gtest29 (a) VALUES (3), (4);
QUERY: INSERT INTO gtest29 (a) VALUES (5);
QUERY: ALTER TABLE gtest29 DROP COLUMN a;
QUERY: INSERT INTO gtest26 (a) VALUES (-2), (0), (3);
QUERY: SELECT * FROM gtest26 ORDER BY a;
QUERY: UPDATE gtest26 SET a = a * -2;
QUERY: SELECT * FROM gtest26 ORDER BY a;
QUERY: SELECT * FROM gtest26 ORDER BY a;
QUERY: UPDATE gtest26 SET a = 1 WHERE a = 0;
QUERY: INSERT INTO gtest26 (a) VALUES (1);
QUERY: UPDATE gtest26 SET a = 11 WHERE a = 1;
QUERY: ALTER TABLE gtest28a DROP COLUMN a;

identity

QUERY: GRANT SELECT, INSERT ON itest8 TO regress_identity_user1;

inherit

QUERY: revoke all on permtest_grandchild from regress_no_child_access;
QUERY: grant select on permtest_parent to regress_no_child_access;
QUERY: revoke all on permtest_parent from regress_no_child_access;
QUERY: grant select(a,c) on permtest_parent to regress_no_child_access;
QUERY: revoke all on permtest_parent from regress_no_child_access;

insert

QUERY: grant select (a) on key_desc_1 to regress_insert_other_user;
QUERY: grant insert on key_desc to regress_insert_other_user;
QUERY: grant select (b) on key_desc_1 to regress_insert_other_user;
QUERY: revoke all on key_desc from regress_insert_other_user;
QUERY: revoke all on key_desc_1 from regress_insert_other_user;
QUERY: grant insert on inserttest3 to regress_coldesc_role;
QUERY: grant insert on brtrigpartcon to regress_coldesc_role;
QUERY: revoke select on brtrigpartcon from regress_coldesc_role;
QUERY: revoke all on inserttest3 from regress_coldesc_role;
QUERY: revoke all on brtrigpartcon from regress_coldesc_role;

lock

QUERY: GRANT UPDATE ON TABLE lock_tbl1 TO regress_rol_lock1;
QUERY: REVOKE UPDATE ON TABLE lock_tbl1 FROM regress_rol_lock1;
QUERY: GRANT UPDATE ON TABLE lock_view1 TO regress_rol_lock1;
QUERY: REVOKE UPDATE ON TABLE lock_view1 FROM regress_rol_lock1;
QUERY: GRANT UPDATE ON TABLE lock_view8 TO regress_rol_lock1;
QUERY: GRANT UPDATE ON TABLE lock_tbl1 TO regress_rol_lock1;
QUERY: REVOKE UPDATE ON TABLE lock_view8 FROM regress_rol_lock1;

merge

QUERY: GRANT INSERT ON target TO regress_merge_no_privs;
QUERY: GRANT INSERT ON target TO regress_merge_no_privs;
QUERY: GRANT UPDATE ON target2 TO regress_merge_privs;

rowsecurity

QUERY: GRANT SELECT ON uaccount TO public;
QUERY: GRANT ALL ON document TO public;
QUERY: GRANT ALL ON t1 TO public;
QUERY: GRANT ALL ON t2 TO public;
QUERY: GRANT ALL ON t3 TO public;
QUERY: GRANT ALL ON part_document TO public;
QUERY: GRANT ALL ON part_document_fiction TO public;
QUERY: GRANT ALL ON part_document_satire TO public;
QUERY: GRANT ALL ON part_document_nonfiction TO public;
QUERY: GRANT SELECT ON s1, s2 TO regress_rls_bob;
QUERY: GRANT ALL ON b1 TO regress_rls_bob;
QUERY: GRANT ALL ON bv1 TO regress_rls_carol;
QUERY: GRANT SELECT ON z1,z2 TO regress_rls_group1, regress_rls_group2,
    regress_rls_bob, regress_rls_carol;
QUERY: GRANT SELECT ON rls_view TO regress_rls_bob;
QUERY: GRANT SELECT ON rls_view TO regress_rls_alice;
QUERY: GRANT SELECT ON rls_view TO regress_rls_carol;
QUERY: GRANT SELECT ON z1_blacklist TO regress_rls_bob;
QUERY: REVOKE SELECT ON z1_blacklist FROM regress_rls_bob;
QUERY: GRANT SELECT ON rls_view TO regress_rls_bob;
QUERY: GRANT SELECT ON rls_view TO regress_rls_carol;
QUERY: GRANT SELECT ON rls_view TO regress_rls_alice;
QUERY: GRANT SELECT ON rls_view TO regress_rls_carol;
QUERY: GRANT SELECT ON z1_blacklist TO regress_rls_bob;
QUERY: GRANT SELECT ON z1_blacklist TO regress_rls_carol;
QUERY: GRANT ALL ON x1 TO PUBLIC;
QUERY: GRANT ALL ON y1, y2 TO regress_rls_bob;
QUERY: GRANT SELECT ON t1 TO regress_rls_bob, regress_rls_carol;
QUERY: GRANT ALL ON t1 TO regress_rls_bob;
QUERY: GRANT ALL ON blog, comment TO regress_rls_bob;
QUERY: GRANT ALL ON copy_t TO regress_rls_bob, regress_rls_exempt_user;
QUERY: GRANT ALL ON copy_rel_to TO regress_rls_bob, regress_rls_exempt_user;
QUERY: GRANT ALL ON current_check TO PUBLIC;
QUERY: GRANT SELECT ON coll_t TO regress_rls_alice;
QUERY: GRANT SELECT ON TABLE tbl1 TO regress_rls_eve;
QUERY: GRANT ALL ON r1, r2 TO regress_rls_bob;
QUERY: GRANT SELECT ON ref_tbl TO regress_rls_bob;
QUERY: GRANT SELECT ON rls_tbl TO regress_rls_bob;
QUERY: GRANT SELECT ON rls_view TO regress_rls_alice;
QUERY: GRANT SELECT ON rls_tbl TO regress_rls_alice;
QUERY: grant select on rls_t to regress_rls_alice, regress_rls_bob;

rules

QUERY: GRANT INSERT ON ruletest_v1 TO regress_rule_user1;

select_views

QUERY: GRANT SELECT ON my_property_normal TO public;
QUERY: GRANT SELECT ON my_property_secure TO public;
QUERY: GRANT SELECT ON my_credit_card_normal TO public;
QUERY: GRANT SELECT ON my_credit_card_secure TO public;
QUERY: GRANT SELECT ON my_credit_card_usage_normal TO public;
QUERY: GRANT SELECT ON my_credit_card_usage_secure TO public;

sequence

QUERY: REVOKE ALL ON seq3 FROM regress_seq_user;
QUERY: GRANT SELECT ON seq3 TO regress_seq_user;
QUERY: REVOKE ALL ON seq3 FROM regress_seq_user;
QUERY: GRANT UPDATE ON seq3 TO regress_seq_user;
QUERY: REVOKE ALL ON seq3 FROM regress_seq_user;
QUERY: REVOKE ALL ON seq3 FROM regress_seq_user;
QUERY: GRANT SELECT ON seq3 TO regress_seq_user;
QUERY: REVOKE ALL ON seq3 FROM regress_seq_user;
QUERY: GRANT UPDATE ON seq3 TO regress_seq_user;
QUERY: REVOKE ALL ON seq3 FROM regress_seq_user;
QUERY: REVOKE ALL ON seq3 FROM regress_seq_user;
QUERY: GRANT SELECT ON seq3 TO regress_seq_user;
QUERY: REVOKE ALL ON seq3 FROM regress_seq_user;
QUERY: GRANT UPDATE ON seq3 TO regress_seq_user;
QUERY: REVOKE ALL ON seq3 FROM regress_seq_user;
QUERY: REVOKE ALL ON seq3 FROM regress_seq_user;
QUERY: GRANT UPDATE ON seq3 TO regress_seq_user;

stats_ext

QUERY: GRANT SELECT, DELETE ON tststats.priv_test_view TO regress_stats_user1;
QUERY: GRANT SELECT, DELETE ON tststats.priv_test_tbl TO regress_stats_user1;

updatable_views

QUERY: GRANT SELECT ON base_tbl TO regress_view_user2;
QUERY: GRANT SELECT ON rw_view1 TO regress_view_user2;
QUERY: GRANT UPDATE (a,c) ON base_tbl TO regress_view_user2;
QUERY: GRANT UPDATE (bb,cc) ON rw_view1 TO regress_view_user2;
QUERY: GRANT INSERT, DELETE ON base_tbl TO regress_view_user2;
QUERY: REVOKE INSERT, DELETE ON base_tbl FROM regress_view_user2;
QUERY: GRANT INSERT, DELETE ON rw_view1 TO regress_view_user2;
QUERY: GRANT SELECT ON base_tbl TO regress_view_user1;
QUERY: GRANT SELECT ON rw_view1 TO regress_view_user2;
QUERY: GRANT UPDATE ON base_tbl TO regress_view_user1;
QUERY: GRANT UPDATE ON rw_view1 TO regress_view_user2;
QUERY: REVOKE UPDATE ON base_tbl FROM regress_view_user1;
QUERY: GRANT SELECT ON rw_view1 TO regress_view_user2;
QUERY: GRANT UPDATE (bb,cc) ON rw_view1 TO regress_view_user2;
QUERY: GRANT SELECT ON base_tbl TO regress_view_user2;
QUERY: GRANT UPDATE (a,c) ON base_tbl TO regress_view_user2;
QUERY: GRANT INSERT, DELETE ON base_tbl TO regress_view_user2;
QUERY: REVOKE INSERT, DELETE ON base_tbl FROM regress_view_user2;
QUERY: GRANT INSERT, DELETE ON rw_view1 TO regress_view_user2;
QUERY: GRANT INSERT, DELETE ON base_tbl TO regress_view_user2;
QUERY: GRANT SELECT, UPDATE ON rw_view2 TO regress_view_user3;
QUERY: GRANT SELECT ON base_tbl TO regress_view_user1;
QUERY: GRANT UPDATE (a, b) ON base_tbl TO regress_view_user1;
QUERY: GRANT SELECT ON rw_view1 TO regress_view_user2;
QUERY: GRANT UPDATE (bb, cc) ON rw_view1 TO regress_view_user2;
QUERY: GRANT SELECT ON base_tbl TO regress_view_user2;
QUERY: GRANT UPDATE (a, c) ON base_tbl TO regress_view_user2;
QUERY: GRANT SELECT ON base_tbl TO regress_view_user3;
QUERY: GRANT UPDATE (a, c) ON base_tbl TO regress_view_user3;
QUERY: REVOKE SELECT, UPDATE ON base_tbl FROM regress_view_user1;
QUERY: REVOKE SELECT, UPDATE ON base_tbl FROM regress_view_user2;
QUERY: grant select (aa,bb) on rw_view1 to regress_view_user2;
QUERY: grant insert on rw_view1 to regress_view_user2;
QUERY: grant update (bb) on rw_view1 to regress_view_user2;
QUERY: grant select (a,b) on base_tbl to regress_view_user2;
QUERY: grant insert (a,b) on base_tbl to regress_view_user2;
QUERY: grant update (a,b) on base_tbl to regress_view_user2;

update

QUERY: GRANT ALL ON range_parted, mintab TO regress_range_parted_user;
QUERY: REVOKE ALL ON range_parted, mintab FROM regress_range_parted_user;

[^1]: These are tests that we're marking as Successful, however they do not match the expected output in some way. This is due to small differences, such as different wording on the error messages, or the column names being incorrect while the data itself is correct.