Star9
61
代码
代码
Issues137
Pull Requests38
流水线
讨论
Wiki
分析
Star9
61
aaronmeiaaronmei修复alter default privileges grant index
19f1466b创建于 2025年4月1日历史提交
create role lowuser password 'lowuser@123';

create schema test_privileges;
create table test_privileges.t1 (c1 int);

SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'SELECT');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'INSERT');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'UPDATE');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'DELETE');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'TRUNCATE');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'REFERENCES');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'ALTER');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'DROP');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'COMMENT');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'INDEX');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'VACUUM');

alter default privileges in schema test_privileges grant SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, ALTER, DROP, COMMENT, INDEX, VACUUM on tables to public;
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'SELECT');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'INSERT');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'UPDATE');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'DELETE');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'TRUNCATE');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'REFERENCES');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'ALTER');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'DROP');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'COMMENT');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'INDEX');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'VACUUM');
create table test_privileges.t2 (c1 int);
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'SELECT');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'INSERT');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'UPDATE');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'DELETE');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'TRUNCATE');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'REFERENCES');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'ALTER');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'DROP');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'COMMENT');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'INDEX');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'VACUUM');

alter default privileges in schema test_privileges revoke SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, ALTER, DROP, COMMENT, INDEX, VACUUM on tables from public;
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'SELECT');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'INSERT');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'UPDATE');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'DELETE');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'TRUNCATE');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'REFERENCES');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'ALTER');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'DROP');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'COMMENT');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'INDEX');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'VACUUM');
create table test_privileges.t3 (c1 int);
SELECT has_table_privilege('lowuser', 'test_privileges.t3', 'SELECT');
SELECT has_table_privilege('lowuser', 'test_privileges.t3', 'INSERT');
SELECT has_table_privilege('lowuser', 'test_privileges.t3', 'UPDATE');
SELECT has_table_privilege('lowuser', 'test_privileges.t3', 'DELETE');
SELECT has_table_privilege('lowuser', 'test_privileges.t3', 'TRUNCATE');
SELECT has_table_privilege('lowuser', 'test_privileges.t3', 'REFERENCES');
SELECT has_table_privilege('lowuser', 'test_privileges.t3', 'ALTER');
SELECT has_table_privilege('lowuser', 'test_privileges.t3', 'DROP');
SELECT has_table_privilege('lowuser', 'test_privileges.t3', 'COMMENT');
SELECT has_table_privilege('lowuser', 'test_privileges.t3', 'INDEX');
SELECT has_table_privilege('lowuser', 'test_privileges.t3', 'VACUUM');

alter default privileges in schema test_privileges revoke grant option for SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, ALTER, DROP, COMMENT, INDEX, VACUUM on tables from public;
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'SELECT');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'INSERT');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'UPDATE');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'DELETE');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'TRUNCATE');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'REFERENCES');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'ALTER');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'DROP');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'COMMENT');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'INDEX');
SELECT has_table_privilege('lowuser', 'test_privileges.t2', 'VACUUM');
create table test_privileges.t4 (c1 int);
SELECT has_table_privilege('lowuser', 'test_privileges.t4', 'SELECT');
SELECT has_table_privilege('lowuser', 'test_privileges.t4', 'INSERT');
SELECT has_table_privilege('lowuser', 'test_privileges.t4', 'UPDATE');
SELECT has_table_privilege('lowuser', 'test_privileges.t4', 'DELETE');
SELECT has_table_privilege('lowuser', 'test_privileges.t4', 'TRUNCATE');
SELECT has_table_privilege('lowuser', 'test_privileges.t4', 'REFERENCES');
SELECT has_table_privilege('lowuser', 'test_privileges.t4', 'ALTER');
SELECT has_table_privilege('lowuser', 'test_privileges.t4', 'DROP');
SELECT has_table_privilege('lowuser', 'test_privileges.t4', 'COMMENT');
SELECT has_table_privilege('lowuser', 'test_privileges.t4', 'INDEX');
SELECT has_table_privilege('lowuser', 'test_privileges.t4', 'VACUUM');

GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, ALTER, DROP, COMMENT, INDEX, VACUUM on test_privileges.t1 to lowuser;
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'SELECT');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'INSERT');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'UPDATE');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'DELETE');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'TRUNCATE');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'REFERENCES');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'ALTER');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'DROP');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'COMMENT');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'INDEX');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'VACUUM');
REVOKE GRANT OPTION FOR SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, ALTER, DROP, COMMENT, INDEX, VACUUM on test_privileges.t1 from lowuser;
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'SELECT');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'INSERT');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'UPDATE');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'DELETE');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'TRUNCATE');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'REFERENCES');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'ALTER');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'DROP');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'COMMENT');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'INDEX');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'VACUUM');
REVOKE SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, ALTER, DROP, COMMENT, INDEX, VACUUM on test_privileges.t1 from lowuser;
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'SELECT');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'INSERT');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'UPDATE');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'DELETE');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'TRUNCATE');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'REFERENCES');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'ALTER');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'DROP');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'COMMENT');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'INDEX');
SELECT has_table_privilege('lowuser', 'test_privileges.t1', 'VACUUM');

drop user lowuser;
drop schema test_privileges cascade;