diff options
Diffstat (limited to '')
| -rw-r--r-- | contrib/sepgsql/sql/alter.sql | 197 | ||||
| -rw-r--r-- | contrib/sepgsql/sql/ddl.sql | 125 | ||||
| -rw-r--r-- | contrib/sepgsql/sql/dml.sql | 257 | ||||
| -rw-r--r-- | contrib/sepgsql/sql/label.sql | 294 | ||||
| -rw-r--r-- | contrib/sepgsql/sql/misc.sql | 45 | ||||
| -rw-r--r-- | contrib/sepgsql/sql/truncate.sql | 24 |
6 files changed, 942 insertions, 0 deletions
diff --git a/contrib/sepgsql/sql/alter.sql b/contrib/sepgsql/sql/alter.sql new file mode 100644 index 0000000..f114449 --- /dev/null +++ b/contrib/sepgsql/sql/alter.sql @@ -0,0 +1,197 @@ +-- +-- Test for various ALTER statements +-- + +-- clean-up in case a prior regression run failed +SET client_min_messages TO 'warning'; +DROP DATABASE IF EXISTS sepgsql_test_regression_1; +DROP DATABASE IF EXISTS sepgsql_test_regression; +DROP USER IF EXISTS regress_sepgsql_test_user; +RESET client_min_messages; + +-- @SECURITY-CONTEXT=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 + +-- +-- CREATE Objects to be altered (with debug_audit being silent) +-- +CREATE DATABASE sepgsql_test_regression_1; + +CREATE USER regress_sepgsql_test_user; + +CREATE SCHEMA regtest_schema_1; +CREATE SCHEMA regtest_schema_2; + +GRANT ALL ON SCHEMA regtest_schema_1 TO public; +GRANT ALL ON SCHEMA regtest_schema_2 TO public; + +SET search_path = regtest_schema_1, regtest_schema_2, public; + +CREATE TABLE regtest_table_1 (a int, b text); + +CREATE TABLE regtest_table_2 (c text) inherits (regtest_table_1); + +CREATE TABLE regtest_table_3 (x int primary key, y text); + +--- +-- partitioned table parent +CREATE TABLE regtest_ptable_1 (o int, p text) PARTITION BY RANGE (o); + +-- partitioned table children +CREATE TABLE regtest_ptable_1_ones PARTITION OF regtest_ptable_1 FOR VALUES FROM ('0') TO ('10'); +CREATE TABLE regtest_ptable_1_tens PARTITION OF regtest_ptable_1 FOR VALUES FROM ('10') TO ('100'); +--- + +CREATE SEQUENCE regtest_seq_1; + +CREATE VIEW regtest_view_1 AS SELECT * FROM regtest_table_1 WHERE a > 0; + +CREATE FUNCTION regtest_func_1 (text) RETURNS bool + AS 'BEGIN RETURN true; END' LANGUAGE 'plpgsql'; + +-- switch on debug_audit +SET sepgsql.debug_audit = true; +SET client_min_messages = LOG; + +-- +-- ALTER xxx OWNER TO +-- +-- XXX: It should take db_xxx:{setattr} permission checks even if +-- owner is not actually changed. +-- +ALTER DATABASE sepgsql_test_regression_1 OWNER TO regress_sepgsql_test_user; +ALTER DATABASE sepgsql_test_regression_1 OWNER TO regress_sepgsql_test_user; +ALTER SCHEMA regtest_schema_1 OWNER TO regress_sepgsql_test_user; +ALTER SCHEMA regtest_schema_1 OWNER TO regress_sepgsql_test_user; +ALTER TABLE regtest_table_1 OWNER TO regress_sepgsql_test_user; +ALTER TABLE regtest_table_1 OWNER TO regress_sepgsql_test_user; +ALTER TABLE regtest_ptable_1 OWNER TO regress_sepgsql_test_user; +ALTER TABLE regtest_ptable_1_ones OWNER TO regress_sepgsql_test_user; +ALTER SEQUENCE regtest_seq_1 OWNER TO regress_sepgsql_test_user; +ALTER SEQUENCE regtest_seq_1 OWNER TO regress_sepgsql_test_user; +ALTER VIEW regtest_view_1 OWNER TO regress_sepgsql_test_user; +ALTER VIEW regtest_view_1 OWNER TO regress_sepgsql_test_user; +ALTER FUNCTION regtest_func_1(text) OWNER TO regress_sepgsql_test_user; +ALTER FUNCTION regtest_func_1(text) OWNER TO regress_sepgsql_test_user; + +-- +-- ALTER xxx SET SCHEMA +-- +ALTER TABLE regtest_table_1 SET SCHEMA regtest_schema_2; +ALTER TABLE regtest_ptable_1 SET SCHEMA regtest_schema_2; +ALTER TABLE regtest_ptable_1_ones SET SCHEMA regtest_schema_2; +ALTER SEQUENCE regtest_seq_1 SET SCHEMA regtest_schema_2; +ALTER VIEW regtest_view_1 SET SCHEMA regtest_schema_2; +ALTER FUNCTION regtest_func_1(text) SET SCHEMA regtest_schema_2; + +-- +-- ALTER xxx RENAME TO +-- +ALTER DATABASE sepgsql_test_regression_1 RENAME TO sepgsql_test_regression; +ALTER SCHEMA regtest_schema_1 RENAME TO regtest_schema; +ALTER TABLE regtest_table_1 RENAME TO regtest_table; + +--- +-- partitioned table parent +ALTER TABLE regtest_ptable_1 RENAME TO regtest_ptable; +-- partitioned table child +ALTER TABLE regtest_ptable_1_ones RENAME TO regtest_table_part; +--- + +ALTER SEQUENCE regtest_seq_1 RENAME TO regtest_seq; +ALTER VIEW regtest_view_1 RENAME TO regtest_view; +ALTER FUNCTION regtest_func_1(text) RENAME TO regtest_func; + +SET search_path = regtest_schema, regtest_schema_2, public; + +-- +-- misc ALTER commands +-- +ALTER DATABASE sepgsql_test_regression CONNECTION LIMIT 999; +ALTER DATABASE sepgsql_test_regression SET search_path TO regtest_schema, public; -- not supported yet + +ALTER TABLE regtest_table ADD COLUMN d float; +ALTER TABLE regtest_table DROP COLUMN d; +ALTER TABLE regtest_table ALTER b SET DEFAULT 'abcd'; -- not supported yet +ALTER TABLE regtest_table ALTER b SET DEFAULT 'XYZ'; -- not supported yet +ALTER TABLE regtest_table ALTER b DROP DEFAULT; -- not supported yet +ALTER TABLE regtest_table ALTER b SET NOT NULL; +ALTER TABLE regtest_table ALTER b DROP NOT NULL; +ALTER TABLE regtest_table ALTER b SET STATISTICS -1; +ALTER TABLE regtest_table ALTER b SET (n_distinct = 999); +ALTER TABLE regtest_table ALTER b SET STORAGE PLAIN; +ALTER TABLE regtest_table ADD CONSTRAINT test_fk FOREIGN KEY (a) REFERENCES regtest_table_3(x); -- not supported +ALTER TABLE regtest_table ADD CONSTRAINT test_ck CHECK (b like '%abc%') NOT VALID; -- not supported +ALTER TABLE regtest_table VALIDATE CONSTRAINT test_ck; -- not supported +ALTER TABLE regtest_table DROP CONSTRAINT test_ck; -- not supported + +CREATE TRIGGER regtest_test_trig BEFORE UPDATE ON regtest_table + FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger(); + +ALTER TABLE regtest_table DISABLE TRIGGER regtest_test_trig; -- not supported +ALTER TABLE regtest_table ENABLE TRIGGER regtest_test_trig; -- not supported + +CREATE RULE regtest_test_rule AS ON INSERT TO regtest_table_3 DO ALSO NOTHING; +ALTER TABLE regtest_table_3 DISABLE RULE regtest_test_rule; -- not supported +ALTER TABLE regtest_table_3 ENABLE RULE regtest_test_rule; -- not supported + +ALTER TABLE regtest_table SET (fillfactor = 75); +ALTER TABLE regtest_table RESET (fillfactor); +ALTER TABLE regtest_table_2 NO INHERIT regtest_table; -- not supported +ALTER TABLE regtest_table_2 INHERIT regtest_table; -- not supported +ALTER TABLE regtest_table SET TABLESPACE pg_default; + +--- +-- partitioned table parent +ALTER TABLE regtest_ptable ADD COLUMN d float; +ALTER TABLE regtest_ptable DROP COLUMN d; +ALTER TABLE regtest_ptable ALTER p SET DEFAULT 'abcd'; -- not supported by sepgsql +ALTER TABLE regtest_ptable ALTER p SET DEFAULT 'XYZ'; -- not supported by sepgsql +ALTER TABLE regtest_ptable ALTER p DROP DEFAULT; -- not supported by sepgsql +ALTER TABLE regtest_ptable ALTER p SET NOT NULL; +ALTER TABLE regtest_ptable ALTER p DROP NOT NULL; +ALTER TABLE regtest_ptable ALTER p SET STATISTICS -1; +ALTER TABLE regtest_ptable ALTER p SET (n_distinct = 999); +ALTER TABLE regtest_ptable ALTER p SET STORAGE PLAIN; +ALTER TABLE regtest_ptable ADD CONSTRAINT test_ck CHECK (p like '%abc%') NOT VALID; -- not supported by sepgsql +ALTER TABLE regtest_ptable DROP CONSTRAINT test_ck; -- not supported by sepgsql + +ALTER TABLE regtest_ptable SET TABLESPACE pg_default; + +-- partitioned table child +ALTER TABLE regtest_table_part ALTER p SET DEFAULT 'abcd'; -- not supported by sepgsql +ALTER TABLE regtest_table_part ALTER p SET DEFAULT 'XYZ'; -- not supported by sepgsql +ALTER TABLE regtest_table_part ALTER p DROP DEFAULT; -- not supported by sepgsql +ALTER TABLE regtest_table_part ALTER p SET NOT NULL; +ALTER TABLE regtest_table_part ALTER p DROP NOT NULL; +ALTER TABLE regtest_table_part ALTER p SET STATISTICS -1; +ALTER TABLE regtest_table_part ALTER p SET (n_distinct = 999); +ALTER TABLE regtest_table_part ALTER p SET STORAGE PLAIN; +ALTER TABLE regtest_table_part ADD CONSTRAINT test_ck CHECK (p like '%abc%') NOT VALID; -- not supported by sepgsql +ALTER TABLE regtest_table_part VALIDATE CONSTRAINT test_ck; -- not supported by sepgsql +ALTER TABLE regtest_table_part DROP CONSTRAINT test_ck; -- not supported by sepgsql + +CREATE TRIGGER regtest_part_test_trig BEFORE UPDATE ON regtest_table_part + FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger(); + +ALTER TABLE regtest_table_part DISABLE TRIGGER regtest_part_test_trig; -- not supported by sepgsql +ALTER TABLE regtest_table_part ENABLE TRIGGER regtest_part_test_trig; -- not supported by sepgsql + +ALTER TABLE regtest_table_part SET (fillfactor = 75); +ALTER TABLE regtest_table_part RESET (fillfactor); + +ALTER TABLE regtest_table_part SET TABLESPACE pg_default; +--- + +ALTER VIEW regtest_view SET (security_barrier); + +ALTER SEQUENCE regtest_seq INCREMENT BY 10 START WITH 1000; + +-- +-- clean-up objects +-- +RESET sepgsql.debug_audit; +RESET client_min_messages; +DROP DATABASE sepgsql_test_regression; +DROP SCHEMA regtest_schema CASCADE; +DROP SCHEMA regtest_schema_2 CASCADE; +DROP USER regress_sepgsql_test_user; diff --git a/contrib/sepgsql/sql/ddl.sql b/contrib/sepgsql/sql/ddl.sql new file mode 100644 index 0000000..3deadb6 --- /dev/null +++ b/contrib/sepgsql/sql/ddl.sql @@ -0,0 +1,125 @@ +-- +-- Regression Test for DDL of Object Permission Checks +-- + +-- clean-up in case a prior regression run failed +SET client_min_messages TO 'warning'; +DROP DATABASE IF EXISTS sepgsql_test_regression; +DROP USER IF EXISTS regress_sepgsql_test_user; +RESET client_min_messages; + +-- confirm required permissions using audit messages +-- @SECURITY-CONTEXT=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0 +SET sepgsql.debug_audit = true; +SET client_min_messages = LOG; + +-- +-- CREATE Permission checks +-- +CREATE DATABASE sepgsql_test_regression; + +CREATE USER regress_sepgsql_test_user; + +CREATE SCHEMA regtest_schema; + +GRANT ALL ON SCHEMA regtest_schema TO regress_sepgsql_test_user; + +SET search_path = regtest_schema, public; + +CREATE TABLE regtest_table (x serial primary key, y text); + +ALTER TABLE regtest_table ADD COLUMN z int; + +CREATE TABLE regtest_table_2 (a int); + +CREATE TABLE regtest_ptable (a int) PARTITION BY RANGE (a); +CREATE TABLE regtest_ptable_ones PARTITION OF regtest_ptable FOR VALUES FROM ('0') TO ('10'); +CREATE TABLE regtest_ptable_tens PARTITION OF regtest_ptable FOR VALUES FROM ('10') TO ('100'); + +ALTER TABLE regtest_ptable ADD COLUMN q int; + +-- corresponding toast table should not have label and permission checks +ALTER TABLE regtest_table_2 ADD COLUMN b text; + +-- VACUUM FULL internally create a new table and swap them later. +VACUUM FULL regtest_table; +VACUUM FULL regtest_ptable; + +CREATE VIEW regtest_view AS SELECT * FROM regtest_table WHERE x < 100; +CREATE VIEW regtest_pview AS SELECT * FROM regtest_ptable WHERE a < 99; + +CREATE SEQUENCE regtest_seq; + +CREATE TYPE regtest_comptype AS (a int, b text); + +CREATE FUNCTION regtest_func(text,int[]) RETURNS bool LANGUAGE plpgsql + AS 'BEGIN RAISE NOTICE ''regtest_func => %'', $1; RETURN true; END'; + +CREATE AGGREGATE regtest_agg ( + sfunc1 = int4pl, basetype = int4, stype1 = int4, initcond1 = '0' +); + +-- CREATE objects owned by others +SET SESSION AUTHORIZATION regress_sepgsql_test_user; + +SET search_path = regtest_schema, public; + +CREATE TABLE regtest_table_3 (x int, y serial); +CREATE TABLE regtest_ptable_3 (o int, p serial) PARTITION BY RANGE (o); +CREATE TABLE regtest_ptable_3_ones PARTITION OF regtest_ptable_3 FOR VALUES FROM ('0') to ('10'); +CREATE TABLE regtest_ptable_3_tens PARTITION OF regtest_ptable_3 FOR VALUES FROM ('10') to ('100'); + +CREATE VIEW regtest_view_2 AS SELECT * FROM regtest_table_3 WHERE x < y; +CREATE VIEW regtest_pview_2 AS SELECT * FROM regtest_ptable_3 WHERE o < p; + +CREATE FUNCTION regtest_func_2(int) RETURNS bool LANGUAGE plpgsql + AS 'BEGIN RETURN $1 * $1 < 100; END'; + +RESET SESSION AUTHORIZATION; + +-- +-- ALTER and CREATE/DROP extra attribute permissions +-- +CREATE TABLE regtest_table_4 (x int primary key, y int, z int); +CREATE INDEX regtest_index_tbl4_y ON regtest_table_4(y); +CREATE INDEX regtest_index_tbl4_z ON regtest_table_4(z); +ALTER TABLE regtest_table_4 ALTER COLUMN y TYPE float; +DROP INDEX regtest_index_tbl4_y; +ALTER TABLE regtest_table_4 + ADD CONSTRAINT regtest_tbl4_con EXCLUDE USING btree (z WITH =); +DROP TABLE regtest_table_4 CASCADE; + +-- For partitioned tables +CREATE TABLE regtest_ptable_4 (x int, y int, z int) PARTITION BY RANGE (x); +CREATE TABLE regtest_ptable_4_ones PARTITION OF regtest_ptable_4 FOR VALUES FROM ('0') TO ('10'); + +CREATE INDEX regtest_pindex_tbl4_y ON regtest_ptable_4_ones(y); +CREATE INDEX regtest_pindex_tbl4_z ON regtest_ptable_4_ones(z); +ALTER TABLE regtest_ptable_4 ALTER COLUMN y TYPE float; +DROP INDEX regtest_pindex_tbl4_y; +ALTER TABLE regtest_ptable_4_ones + ADD CONSTRAINT regtest_ptbl4_con EXCLUDE USING btree (z WITH =); +DROP TABLE regtest_ptable_4 CASCADE; + +-- +-- DROP Permission checks (with clean-up) +-- + +DROP FUNCTION regtest_func(text,int[]); +DROP AGGREGATE regtest_agg(int); + +DROP SEQUENCE regtest_seq; +DROP VIEW regtest_view; + +ALTER TABLE regtest_table DROP COLUMN y; + +ALTER TABLE regtest_ptable DROP COLUMN q CASCADE; + +DROP TABLE regtest_table; +DROP TABLE regtest_ptable CASCADE; + +DROP OWNED BY regress_sepgsql_test_user; + +DROP DATABASE sepgsql_test_regression; +DROP USER regress_sepgsql_test_user; +DROP SCHEMA IF EXISTS regtest_schema CASCADE; diff --git a/contrib/sepgsql/sql/dml.sql b/contrib/sepgsql/sql/dml.sql new file mode 100644 index 0000000..4a47b4a --- /dev/null +++ b/contrib/sepgsql/sql/dml.sql @@ -0,0 +1,257 @@ +-- +-- Regression Test for DML Permissions +-- + +-- +-- Setup +-- +CREATE TABLE t1 (a int, junk int, b text); +SECURITY LABEL ON TABLE t1 IS 'system_u:object_r:sepgsql_table_t:s0'; +ALTER TABLE t1 DROP COLUMN junk; +INSERT INTO t1 VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc'); + +CREATE TABLE t2 (x int, y text); +SECURITY LABEL ON TABLE t2 IS 'system_u:object_r:sepgsql_ro_table_t:s0'; +INSERT INTO t2 VALUES (1, 'xxx'), (2, 'yyy'), (3, 'zzz'); + +CREATE TABLE t3 (s int, t text); +SECURITY LABEL ON TABLE t3 IS 'system_u:object_r:sepgsql_fixed_table_t:s0'; +INSERT INTO t3 VALUES (1, 'sss'), (2, 'ttt'), (3, 'uuu'); + +CREATE TABLE t4 (m int, junk int, n text); +SECURITY LABEL ON TABLE t4 IS 'system_u:object_r:sepgsql_secret_table_t:s0'; +ALTER TABLE t4 DROP COLUMN junk; +INSERT INTO t4 VALUES (1, 'mmm'), (2, 'nnn'), (3, 'ooo'); + +CREATE TABLE t5 (e text, f text, g text); +SECURITY LABEL ON TABLE t5 IS 'system_u:object_r:sepgsql_table_t:s0'; +SECURITY LABEL ON COLUMN t5.e IS 'system_u:object_r:sepgsql_table_t:s0'; +SECURITY LABEL ON COLUMN t5.f IS 'system_u:object_r:sepgsql_ro_table_t:s0'; +SECURITY LABEL ON COLUMN t5.g IS 'system_u:object_r:sepgsql_secret_table_t:s0'; + +--- +-- partitioned table parent +CREATE TABLE t1p (o int, p text, q text) PARTITION BY RANGE (o); +SECURITY LABEL ON TABLE t1p IS 'system_u:object_r:sepgsql_table_t:s0'; +SECURITY LABEL ON COLUMN t1p.o IS 'system_u:object_r:sepgsql_table_t:s0'; +SECURITY LABEL ON COLUMN t1p.p IS 'system_u:object_r:sepgsql_ro_table_t:s0'; +SECURITY LABEL ON COLUMN t1p.q IS 'system_u:object_r:sepgsql_secret_table_t:s0'; + +-- partitioned table children +CREATE TABLE t1p_ones PARTITION OF t1p FOR VALUES FROM ('0') TO ('10'); +SECURITY LABEL ON COLUMN t1p_ones.o IS 'system_u:object_r:sepgsql_table_t:s0'; +SECURITY LABEL ON COLUMN t1p_ones.p IS 'system_u:object_r:sepgsql_ro_table_t:s0'; +SECURITY LABEL ON COLUMN t1p_ones.q IS 'system_u:object_r:sepgsql_secret_table_t:s0'; +CREATE TABLE t1p_tens PARTITION OF t1p FOR VALUES FROM ('10') TO ('100'); +SECURITY LABEL ON COLUMN t1p_tens.o IS 'system_u:object_r:sepgsql_table_t:s0'; +SECURITY LABEL ON COLUMN t1p_tens.p IS 'system_u:object_r:sepgsql_ro_table_t:s0'; +SECURITY LABEL ON COLUMN t1p_tens.q IS 'system_u:object_r:sepgsql_secret_table_t:s0'; + +--- + +CREATE TABLE customer (cid int primary key, cname text, ccredit text); +SECURITY LABEL ON COLUMN customer.ccredit IS 'system_u:object_r:sepgsql_secret_table_t:s0'; +INSERT INTO customer VALUES (1, 'Taro', '1111-2222-3333-4444'), + (2, 'Hanako', '5555-6666-7777-8888'); +CREATE FUNCTION customer_credit(int) RETURNS text + AS 'SELECT regexp_replace(ccredit, ''-[0-9]+$'', ''-????'') FROM customer WHERE cid = $1' + LANGUAGE sql; +SECURITY LABEL ON FUNCTION customer_credit(int) + IS 'system_u:object_r:sepgsql_trusted_proc_exec_t:s0'; + +SELECT objtype, objname, label FROM pg_seclabels + WHERE provider = 'selinux' + AND objtype in ('table', 'column') + AND objname in ('t1', 't2', 't3', 't4', + 't5', 't5.e', 't5.f', 't5.g', + 't1p', 't1p.o', 't1p.p', 't1p.q', + 't1p_ones', 't1p_ones.o', 't1p_ones.p', 't1p_ones.q', + 't1p_tens', 't1p_tens.o', 't1p_tens.p', 't1p_tens.q') +ORDER BY objname COLLATE "C"; + +CREATE SCHEMA my_schema_1; +CREATE TABLE my_schema_1.ts1 (a int, b text); +CREATE TABLE my_schema_1.pts1 (o int, p text) PARTITION BY RANGE (o); +CREATE TABLE my_schema_1.pts1_ones PARTITION OF my_schema_1.pts1 FOR VALUES FROM ('0') to ('10'); + +CREATE SCHEMA my_schema_2; +CREATE TABLE my_schema_2.ts2 (x int, y text); +CREATE TABLE my_schema_2.pts2 (o int, p text) PARTITION BY RANGE (o); +CREATE TABLE my_schema_2.pts2_tens PARTITION OF my_schema_2.pts2 FOR VALUES FROM ('10') to ('100'); + +SECURITY LABEL ON SCHEMA my_schema_2 + IS 'system_u:object_r:sepgsql_regtest_invisible_schema_t:s0'; + +-- Hardwired Rules +UPDATE pg_attribute SET attisdropped = true + WHERE attrelid = 't5'::regclass AND attname = 'f'; -- failed + +-- +-- Simple DML statements +-- +-- @SECURITY-CONTEXT=unconfined_u:unconfined_r:sepgsql_regtest_user_t:s0 + +SELECT * FROM t1; -- ok +SELECT * FROM t2; -- ok +SELECT * FROM t3; -- ok +SELECT * FROM t4; -- failed +SELECT * FROM t5; -- failed +SELECT e,f FROM t5; -- ok +SELECT (t1.*)::record FROM t1; -- ok +SELECT (t4.*)::record FROM t4; -- failed + +--- +-- partitioned table parent +SELECT * FROM t1p; -- failed +SELECT o,p FROM t1p; -- ok +--partitioned table children +SELECT * FROM t1p_ones; -- failed +SELECT o FROM t1p_ones; -- ok +SELECT o,p FROM t1p_ones; -- ok +SELECT * FROM t1p_tens; -- failed +SELECT o FROM t1p_tens; -- ok +SELECT o,p FROM t1p_tens; -- ok +--- + +SELECT * FROM customer; -- failed +SELECT cid, cname, customer_credit(cid) FROM customer; -- ok + +SELECT count(*) FROM t5; -- ok +SELECT count(*) FROM t5 WHERE g IS NULL; -- failed + +--- +-- partitioned table parent +SELECT count(*) FROM t1p; -- ok +SELECT count(*) FROM t1p WHERE q IS NULL; -- failed +-- partitioned table children +SELECT count(*) FROM t1p_ones; -- ok +SELECT count(*) FROM t1p_ones WHERE q IS NULL; -- failed +SELECT count(*) FROM t1p_tens; -- ok +SELECT count(*) FROM t1p_tens WHERE q IS NULL; -- failed +--- + +INSERT INTO t1 VALUES (4, 'abc'); -- ok +INSERT INTO t2 VALUES (4, 'xyz'); -- failed +INSERT INTO t3 VALUES (4, 'stu'); -- ok +INSERT INTO t4 VALUES (4, 'mno'); -- failed +INSERT INTO t5 VALUES (1,2,3); -- failed +INSERT INTO t5 (e,f) VALUES ('abc', 'def'); -- failed +INSERT INTO t5 (e) VALUES ('abc'); -- ok + +--- +-- partitioned table parent +INSERT INTO t1p (o,p) VALUES (9, 'mno'); -- failed +INSERT INTO t1p (o) VALUES (9); -- ok +INSERT INTO t1p (o,p) VALUES (99, 'pqr'); -- failed +INSERT INTO t1p (o) VALUES (99); -- ok +-- partitioned table children +INSERT INTO t1p_ones (o,p) VALUES (9, 'mno'); -- failed +INSERT INTO t1p_ones (o) VALUES (9); -- ok +INSERT INTO t1p_tens (o,p) VALUES (99, 'pqr'); -- failed +INSERT INTO t1p_tens (o) VALUES (99); -- ok +--- + +UPDATE t1 SET b = b || '_upd'; -- ok +UPDATE t2 SET y = y || '_upd'; -- failed +UPDATE t3 SET t = t || '_upd'; -- failed +UPDATE t4 SET n = n || '_upd'; -- failed +UPDATE t5 SET e = 'xyz'; -- ok +UPDATE t5 SET e = f || '_upd'; -- ok +UPDATE t5 SET e = g || '_upd'; -- failed + +--- +-- partitioned table parent +UPDATE t1p SET o = 9 WHERE o < 10; -- ok +UPDATE t1p SET o = 99 WHERE o >= 10; -- ok +UPDATE t1p SET o = ascii(COALESCE(p,'upd'))%10 WHERE o < 10; -- ok +UPDATE t1p SET o = ascii(COALESCE(q,'upd'))%100 WHERE o >= 10; -- failed +-- partitioned table children +UPDATE t1p_ones SET o = 9; -- ok +UPDATE t1p_ones SET o = ascii(COALESCE(p,'upd'))%10; -- ok +UPDATE t1p_ones SET o = ascii(COALESCE(q,'upd'))%10; -- failed +UPDATE t1p_tens SET o = 99; -- ok +UPDATE t1p_tens SET o = ascii(COALESCE(p,'upd'))%100; -- ok +UPDATE t1p_tens SET o = ascii(COALESCE(q,'upd'))%100; -- failed +--- + +DELETE FROM t1; -- ok +DELETE FROM t2; -- failed +DELETE FROM t3; -- failed +DELETE FROM t4; -- failed +DELETE FROM t5; -- ok +DELETE FROM t5 WHERE f IS NULL; -- ok +DELETE FROM t5 WHERE g IS NULL; -- failed + +--- +-- partitioned table parent +DELETE FROM t1p; -- ok +DELETE FROM t1p WHERE p IS NULL; -- ok +DELETE FROM t1p WHERE q IS NULL; -- failed +-- partitioned table children +DELETE FROM t1p_ones WHERE p IS NULL; -- ok +DELETE FROM t1p_ones WHERE q IS NULL; -- failed; +DELETE FROM t1p_tens WHERE p IS NULL; -- ok +DELETE FROM t1p_tens WHERE q IS NULL; -- failed +--- + +-- +-- COPY TO/FROM statements +-- +COPY t1 TO '/dev/null'; -- ok +COPY t2 TO '/dev/null'; -- ok +COPY t3 TO '/dev/null'; -- ok +COPY t4 TO '/dev/null'; -- failed +COPY t5 TO '/dev/null'; -- failed +COPY t5(e,f) TO '/dev/null'; -- ok + +--- +-- partitioned table parent +COPY (SELECT * FROM t1p) TO '/dev/null'; -- failed +COPY (SELECT (o,p) FROM t1p) TO '/dev/null'; -- ok +-- partitioned table children +COPY t1p_ones TO '/dev/null'; -- failed +COPY t1p_ones(o,p) TO '/dev/null'; -- ok +COPY t1p_tens TO '/dev/null'; -- failed +COPY t1p_tens(o,p) TO '/dev/null'; -- ok +--- + +COPY t1 FROM '/dev/null'; -- ok +COPY t2 FROM '/dev/null'; -- failed +COPY t3 FROM '/dev/null'; -- ok +COPY t4 FROM '/dev/null'; -- failed +COPY t5 FROM '/dev/null'; -- failed +COPY t5 (e,f) FROM '/dev/null'; -- failed +COPY t5 (e) FROM '/dev/null'; -- ok + +--- +-- partitioned table parent +COPY t1p FROM '/dev/null'; -- failed +COPY t1p (o) FROM '/dev/null'; -- ok +-- partitioned table children +COPY t1p_ones FROM '/dev/null'; -- failed +COPY t1p_ones (o) FROM '/dev/null'; -- ok +COPY t1p_tens FROM '/dev/null'; -- failed +COPY t1p_tens (o) FROM '/dev/null'; -- ok +--- + +-- +-- Schema search path +-- +SET search_path = my_schema_1, my_schema_2, public; +SELECT * FROM ts1; -- ok +SELECT * FROM ts2; -- failed (relation not found) +SELECT * FROM my_schema_2.ts2; -- failed (policy violation) + +-- +-- Clean up +-- +-- @SECURITY-CONTEXT=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0-s0:c0.c255 +DROP TABLE IF EXISTS t1 CASCADE; +DROP TABLE IF EXISTS t2 CASCADE; +DROP TABLE IF EXISTS t3 CASCADE; +DROP TABLE IF EXISTS t4 CASCADE; +DROP TABLE IF EXISTS t5 CASCADE; +DROP TABLE IF EXISTS t1p CASCADE; +DROP TABLE IF EXISTS customer CASCADE; +DROP SCHEMA IF EXISTS my_schema_1 CASCADE; +DROP SCHEMA IF EXISTS my_schema_2 CASCADE; diff --git a/contrib/sepgsql/sql/label.sql b/contrib/sepgsql/sql/label.sql new file mode 100644 index 0000000..d19c6ed --- /dev/null +++ b/contrib/sepgsql/sql/label.sql @@ -0,0 +1,294 @@ +-- +-- Regression Tests for Label Management +-- + +-- +-- Setup +-- +CREATE TABLE t1 (a int, b text); +INSERT INTO t1 VALUES (1, 'aaa'), (2, 'bbb'), (3, 'ccc'); +SELECT * INTO t2 FROM t1 WHERE a % 2 = 0; + +CREATE FUNCTION f1 () RETURNS text + AS 'SELECT sepgsql_getcon()' + LANGUAGE sql; + +CREATE FUNCTION f2 () RETURNS text + AS 'SELECT sepgsql_getcon()' + LANGUAGE sql; +SECURITY LABEL ON FUNCTION f2() + IS 'system_u:object_r:sepgsql_trusted_proc_exec_t:s0'; + +CREATE FUNCTION f3 () RETURNS text + AS 'BEGIN + RAISE EXCEPTION ''an exception from f3()''; + RETURN NULL; + END;' LANGUAGE plpgsql; +SECURITY LABEL ON FUNCTION f3() + IS 'system_u:object_r:sepgsql_trusted_proc_exec_t:s0'; + +CREATE FUNCTION f4 () RETURNS text + AS 'SELECT sepgsql_getcon()' + LANGUAGE sql; +SECURITY LABEL ON FUNCTION f4() + IS 'system_u:object_r:sepgsql_nosuch_trusted_proc_exec_t:s0'; + +CREATE FUNCTION f5 (text) RETURNS bool + AS 'SELECT sepgsql_setcon($1)' + LANGUAGE sql; +SECURITY LABEL ON FUNCTION f5(text) + IS 'system_u:object_r:sepgsql_regtest_trusted_proc_exec_t:s0'; + +CREATE TABLE auth_tbl(uname text, credential text, label text); +INSERT INTO auth_tbl + VALUES ('foo', 'acbd18db4cc2f85cedef654fccc4a4d8', 'sepgsql_regtest_foo_t:s0'), + ('var', 'b2145aac704ce76dbe1ac7adac535b23', 'sepgsql_regtest_var_t:s0'), + ('baz', 'b2145aac704ce76dbe1ac7adac535b23', 'sepgsql_regtest_baz_t:s0'); +SECURITY LABEL ON TABLE auth_tbl + IS 'system_u:object_r:sepgsql_secret_table_t:s0'; + +CREATE FUNCTION auth_func(text, text) RETURNS bool + LANGUAGE sql + AS 'SELECT sepgsql_setcon(regexp_replace(sepgsql_getcon(), ''_r:.*$'', ''_r:'' || label)) + FROM auth_tbl WHERE uname = $1 AND credential = $2'; +SECURITY LABEL ON FUNCTION auth_func(text,text) + IS 'system_u:object_r:sepgsql_regtest_trusted_proc_exec_t:s0'; + +CREATE TABLE foo_tbl(a int, b text); +INSERT INTO foo_tbl VALUES (1, 'aaa'), (2,'bbb'), (3,'ccc'), (4,'ddd'); +SECURITY LABEL ON TABLE foo_tbl + IS 'system_u:object_r:sepgsql_regtest_foo_table_t:s0'; + +CREATE TABLE var_tbl(x int, y text); +INSERT INTO var_tbl VALUES (2,'xxx'), (3,'yyy'), (4,'zzz'), (5,'xyz'); +SECURITY LABEL ON TABLE var_tbl + IS 'system_u:object_r:sepgsql_regtest_var_table_t:s0'; + +CREATE TABLE foo_ptbl(o int, p text) PARTITION BY RANGE (o); +CREATE TABLE foo_ptbl_ones PARTITION OF foo_ptbl FOR VALUES FROM ('0') TO ('10'); +CREATE TABLE foo_ptbl_tens PARTITION OF foo_ptbl FOR VALUES FROM ('10') TO ('100'); + +INSERT INTO foo_ptbl VALUES (0, 'aaa'), (9,'bbb'), (10,'ccc'), (99,'ddd'); +SECURITY LABEL ON TABLE foo_ptbl + IS 'system_u:object_r:sepgsql_regtest_foo_table_t:s0'; + +CREATE TABLE var_ptbl(q int, r text) PARTITION BY RANGE (q); +CREATE TABLE var_ptbl_ones PARTITION OF var_ptbl FOR VALUES FROM ('0') TO ('10'); +CREATE TABLE var_ptbl_tens PARTITION OF var_ptbl FOR VALUES FROM ('10') TO ('100'); + +INSERT INTO var_ptbl VALUES (0,'xxx'), (9,'yyy'), (10,'zzz'), (99,'xyz'); +SECURITY LABEL ON TABLE var_ptbl + IS 'system_u:object_r:sepgsql_regtest_var_table_t:s0'; + +-- +-- Tests for default labeling behavior +-- +-- @SECURITY-CONTEXT=unconfined_u:unconfined_r:sepgsql_regtest_user_t:s0 +CREATE TABLE t3 (s int, t text); +INSERT INTO t3 VALUES (1, 'sss'), (2, 'ttt'), (3, 'uuu'); + +-- @SECURITY-CONTEXT=unconfined_u:unconfined_r:sepgsql_regtest_dba_t:s0 +CREATE TABLE t4 (m int, n text); +INSERT INTO t4 VALUES (1,'mmm'), (2,'nnn'), (3,'ooo'); + +-- @SECURITY-CONTEXT=unconfined_u:unconfined_r:sepgsql_regtest_user_t:s0 +CREATE TABLE tpart (o int, p text) PARTITION BY RANGE (o); + +CREATE TABLE tpart_ones PARTITION OF tpart FOR VALUES FROM ('0') TO ('10'); +-- @SECURITY-CONTEXT=unconfined_u:unconfined_r:sepgsql_regtest_dba_t:s0 +CREATE TABLE tpart_tens PARTITION OF tpart FOR VALUES FROM ('10') TO ('100'); + +INSERT INTO tpart VALUES (0, 'aaa'); +INSERT INTO tpart VALUES (9, 'bbb'); +INSERT INTO tpart VALUES (99, 'ccc'); + +SELECT objtype, objname, label FROM pg_seclabels + WHERE provider = 'selinux' AND objtype = 'table' AND objname in ('t1', 't2', 't3', + 'tpart', + 'tpart_ones', + 'tpart_tens') + ORDER BY objname COLLATE "C" ASC; +SELECT objtype, objname, label FROM pg_seclabels + WHERE provider = 'selinux' AND objtype = 'column' AND (objname like 't3.%' + OR objname like 't4.%' + OR objname like 'tpart.%' + OR objname like 'tpart_ones.%' + OR objname like 'tpart_tens.%') + ORDER BY objname COLLATE "C" ASC; + +-- +-- Tests for SECURITY LABEL +-- +-- @SECURITY-CONTEXT=unconfined_u:unconfined_r:sepgsql_regtest_dba_t:s0 +SECURITY LABEL ON TABLE t1 + IS 'system_u:object_r:sepgsql_ro_table_t:s0'; -- ok +SECURITY LABEL ON TABLE t2 + IS 'invalid security context'; -- be failed +SECURITY LABEL ON COLUMN t2 + IS 'system_u:object_r:sepgsql_ro_table_t:s0'; -- be failed +SECURITY LABEL ON COLUMN t2.b + IS 'system_u:object_r:sepgsql_ro_table_t:s0'; -- ok +SECURITY LABEL ON TABLE tpart + IS 'system_u:object_r:sepgsql_ro_table_t:s0'; -- ok +SECURITY LABEL ON TABLE tpart + IS 'invalid security context'; -- failed +SECURITY LABEL ON COLUMN tpart + IS 'system_u:object_r:sepgsql_ro_table_t:s0'; -- failed +SECURITY LABEL ON COLUMN tpart.o + IS 'system_u:object_r:sepgsql_ro_table_t:s0'; -- ok + +-- +-- Tests for Trusted Procedures +-- +-- @SECURITY-CONTEXT=unconfined_u:unconfined_r:sepgsql_regtest_user_t:s0 +SET sepgsql.debug_audit = true; +SET client_min_messages = log; +SELECT f1(); -- normal procedure +SELECT f2(); -- trusted procedure +SELECT f3(); -- trusted procedure that raises an error +SELECT f4(); -- failed on domain transition +SELECT sepgsql_getcon(); -- client's label must be restored + +-- +-- Test for Dynamic Domain Transition +-- + +-- validation of transaction aware dynamic-transition +-- @SECURITY-CONTEXT=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0:c0.c25 +SELECT sepgsql_setcon('unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0:c0.c15'); +SELECT sepgsql_getcon(); + +SELECT sepgsql_setcon(NULL); -- failed to reset +SELECT sepgsql_getcon(); + +BEGIN; +SELECT sepgsql_setcon('unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0:c0.c12'); +SELECT sepgsql_getcon(); + +SAVEPOINT svpt_1; +SELECT sepgsql_setcon('unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0:c0.c9'); +SELECT sepgsql_getcon(); + +SAVEPOINT svpt_2; +SELECT sepgsql_setcon('unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0:c0.c6'); +SELECT sepgsql_getcon(); + +SAVEPOINT svpt_3; +SELECT sepgsql_setcon('unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0:c0.c3'); +SELECT sepgsql_getcon(); + +ROLLBACK TO SAVEPOINT svpt_2; +SELECT sepgsql_getcon(); -- should be 's0:c0.c9' + +ROLLBACK TO SAVEPOINT svpt_1; +SELECT sepgsql_getcon(); -- should be 's0:c0.c12' + +ABORT; +SELECT sepgsql_getcon(); -- should be 's0:c0.c15' + +BEGIN; +SELECT sepgsql_setcon('unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0:c0.c8'); +SELECT sepgsql_getcon(); + +SAVEPOINT svpt_1; +SELECT sepgsql_setcon('unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0:c0.c4'); +SELECT sepgsql_getcon(); + +ROLLBACK TO SAVEPOINT svpt_1; +SELECT sepgsql_getcon(); -- should be 's0:c0.c8' +SELECT sepgsql_setcon('unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0:c0.c6'); + +COMMIT; +SELECT sepgsql_getcon(); -- should be 's0:c0.c6' + +-- sepgsql_regtest_user_t is not available dynamic-transition, +-- unless sepgsql_setcon() is called inside of trusted-procedure +-- @SECURITY-CONTEXT=unconfined_u:unconfined_r:sepgsql_regtest_user_t:s0:c0.c15 + +-- sepgsql_regtest_user_t has no permission to switch current label +SELECT sepgsql_setcon('unconfined_u:unconfined_r:sepgsql_regtest_user_t:s0'); -- failed +SELECT sepgsql_getcon(); + +-- trusted procedure allows to switch, but unavailable to override MCS rules +SELECT f5('unconfined_u:unconfined_r:sepgsql_regtest_user_t:s0:c0.c7'); -- OK +SELECT sepgsql_getcon(); + +SELECT f5('unconfined_u:unconfined_r:sepgsql_regtest_user_t:s0:c0.c31'); -- Failed +SELECT sepgsql_getcon(); + +SELECT f5(NULL); -- Failed +SELECT sepgsql_getcon(); + +BEGIN; +SELECT f5('unconfined_u:unconfined_r:sepgsql_regtest_user_t:s0:c0.c3'); -- OK +SELECT sepgsql_getcon(); + +ABORT; +SELECT sepgsql_getcon(); + +-- +-- Test for simulation of typical connection pooling server +-- +-- @SECURITY-CONTEXT=unconfined_u:unconfined_r:sepgsql_regtest_pool_t:s0 + +-- we shouldn't allow to switch client label without trusted procedure +SELECT sepgsql_setcon('unconfined_u:unconfined_r:sepgsql_regtest_foo_t:s0'); + +SELECT * FROM auth_tbl; -- failed, no permission to reference + +-- switch to "foo" +SELECT auth_func('foo', 'acbd18db4cc2f85cedef654fccc4a4d8'); + +SELECT sepgsql_getcon(); + +SELECT * FROM foo_tbl; -- OK +SELECT * FROM foo_ptbl; -- OK + +SELECT * FROM var_tbl; -- failed +SELECT * FROM var_ptbl; -- failed + +SELECT * FROM auth_tbl; -- failed + +SELECT sepgsql_setcon(NULL); -- end of session +SELECT sepgsql_getcon(); + +-- the pooler cannot touch these tables directly +SELECT * FROM foo_tbl; -- failed +SELECT * FROM foo_ptbl; -- failed + +SELECT * FROM var_tbl; -- failed +SELECT * FROM var_ptbl; -- failed + +-- switch to "var" +SELECT auth_func('var', 'b2145aac704ce76dbe1ac7adac535b23'); + +SELECT sepgsql_getcon(); + +SELECT * FROM foo_tbl; -- failed +SELECT * FROM foo_ptbl; -- failed + +SELECT * FROM var_tbl; -- OK +SELECT * FROM var_ptbl; -- OK + +SELECT * FROM auth_tbl; -- failed + +SELECT sepgsql_setcon(NULL); -- end of session + +-- misc checks +SELECT auth_func('var', 'invalid credential'); -- not works +SELECT sepgsql_getcon(); + +-- +-- Clean up +-- +-- @SECURITY-CONTEXT=unconfined_u:unconfined_r:sepgsql_regtest_superuser_t:s0-s0:c0.c255 +DROP TABLE IF EXISTS t1 CASCADE; +DROP TABLE IF EXISTS t2 CASCADE; +DROP TABLE IF EXISTS t3 CASCADE; +DROP TABLE IF EXISTS t4 CASCADE; +DROP TABLE IF EXISTS tpart CASCADE; +DROP FUNCTION IF EXISTS f1() CASCADE; +DROP FUNCTION IF EXISTS f2() CASCADE; +DROP FUNCTION IF EXISTS f3() CASCADE; +DROP FUNCTION IF EXISTS f4() CASCADE; +DROP FUNCTION IF EXISTS f5(text) CASCADE; diff --git a/contrib/sepgsql/sql/misc.sql b/contrib/sepgsql/sql/misc.sql new file mode 100644 index 0000000..bd5b6e2 --- /dev/null +++ b/contrib/sepgsql/sql/misc.sql @@ -0,0 +1,45 @@ +-- +-- Regression Test for Misc Permission Checks +-- + +LOAD '$libdir/sepgsql'; -- failed + +-- +-- Permissions to execute functions +-- +CREATE TABLE t1 (x int, y text); +INSERT INTO t1 (SELECT x, md5(x::text) FROM generate_series(1,100) x); + +CREATE TABLE t1p (o int, p text) PARTITION BY RANGE (o); +CREATE TABLE t1p_ones PARTITION OF t1p FOR VALUES FROM ('0') TO ('10'); +CREATE TABLE t1p_tens PARTITION OF t1p FOR VALUES FROM ('10') TO ('100'); +INSERT INTO t1p (SELECT x, md5(x::text) FROM generate_series(0,99) x); + +SET sepgsql.debug_audit = on; +SET client_min_messages = log; + +-- regular function and operators +SELECT * FROM t1 WHERE x > 50 AND y like '%64%'; +SELECT * FROM t1p WHERE o > 50 AND p like '%64%'; +SELECT * FROM t1p_ones WHERE o > 50 AND p like '%64%'; +SELECT * FROM t1p_tens WHERE o > 50 AND p like '%64%'; + +-- aggregate function +SELECT MIN(x), AVG(x) FROM t1; +SELECT MIN(o), AVG(o) FROM t1p; +SELECT MIN(o), AVG(o) FROM t1p_ones; +SELECT MIN(o), AVG(o) FROM t1p_tens; + +-- window function +SELECT row_number() OVER (order by x), * FROM t1 WHERE y like '%86%'; +SELECT row_number() OVER (order by o), * FROM t1p WHERE p like '%86%'; +SELECT row_number() OVER (order by o), * FROM t1p_ones WHERE p like '%86%'; +SELECT row_number() OVER (order by o), * FROM t1p_tens WHERE p like '%86%'; + +RESET sepgsql.debug_audit; +RESET client_min_messages; +-- +-- Cleanup +-- +DROP TABLE IF EXISTS t1 CASCADE; +DROP TABLE IF EXISTS t1p CASCADE; diff --git a/contrib/sepgsql/sql/truncate.sql b/contrib/sepgsql/sql/truncate.sql new file mode 100644 index 0000000..3748a1b --- /dev/null +++ b/contrib/sepgsql/sql/truncate.sql @@ -0,0 +1,24 @@ +-- +-- Regression Test for TRUNCATE +-- + +-- +-- Setup +-- +CREATE TABLE julio_claudians (name text, birth_date date); +SECURITY LABEL ON TABLE julio_claudians IS 'system_u:object_r:sepgsql_regtest_foo_table_t:s0'; +INSERT INTO julio_claudians VALUES ('Augustus', 'September 23, 63 BC'), ('Tiberius', 'November 16, 42 BC'), ('Caligula', 'August 31, 0012'), ('Claudius', 'August 1, 0010'), ('Nero', 'December 15, 0037'); + +CREATE TABLE flavians (name text, birth_date date); +SECURITY LABEL ON TABLE flavians IS 'system_u:object_r:sepgsql_table_t:s0'; + +INSERT INTO flavians VALUES ('Vespasian', 'November 17, 0009'), ('Titus', 'December 30, 0039'), ('Domitian', 'October 24, 0051'); + +SELECT * from julio_claudians; +SELECT * from flavians; + +TRUNCATE TABLE julio_claudians; -- ok +TRUNCATE TABLE flavians; -- failed + +SELECT * from julio_claudians; +SELECT * from flavians; |