DROP FUNCTION IF EXISTS create_single_range_partition(TEXT,ANYELEMENT,ANYELEMENT,TEXT);

DROP TABLE IF EXISTS test1 CASCADE;

DROP TABLE IF EXISTS test2 CASCADE;

DROP ROLE IF EXISTSpathman_regress_hacker;

DROP ROLE IF EXISTSregress_pathman_hacker;

SET client_min_messages = 'notice';

GRANT CREATE ON SCHEMA public TO PUBLIC;

CREATE EXTENSION pg_pathman;

CREATE ROLEpathman_regress_hacker LOGIN;

CREATE ROLEregress_pathman_hacker LOGIN;

-- Test 1

RESET ROLE;

ALTER ROLEpathman_regress_hacker NOSUPERUSER;

SET ROLEpathman_regress_hacker;

ALTER ROLEregress_pathman_hacker NOSUPERUSER;

SET ROLEregress_pathman_hacker;

SHOW is_superuser;

is_superuser

--------------

RETURNS bigint

AS $$

BEGIN

ALTER ROLEpathman_regress_hacker SUPERUSER;

ALTER ROLEregress_pathman_hacker SUPERUSER;

SELECT _partition_data_concurrent(relation, NULL::text, NULL::text, p_limit) INTO p_total;

END

$$ LANGUAGE plpgsql;

(1 row)

-- Test result (must be 'off')

SET ROLEpathman_regress_hacker;

SET ROLEregress_pathman_hacker;

SHOW is_superuser;

is_superuser

--------------

-- Test 2

RESET ROLE;

ALTER ROLEpathman_regress_hacker NOSUPERUSER;

SET ROLEpathman_regress_hacker;

ALTER ROLEregress_pathman_hacker NOSUPERUSER;

SET ROLEregress_pathman_hacker;

SHOW is_superuser;

is_superuser

--------------

RETURNS REGCLASS

AS $$

BEGIN

ALTER ROLEpathman_regress_hacker SUPERUSER;

ALTER ROLEregress_pathman_hacker SUPERUSER;

RETURN create_single_range_partition(parent_relid, start_value, end_value, partition_name, NULL::text);

END

$$ LANGUAGE plpgsql;

INSERT INTO test2 values(1);

-- Test result (must be 'off')

SET ROLEpathman_regress_hacker;

SET ROLEregress_pathman_hacker;

SHOW is_superuser;

is_superuser

--------------

DETAIL: drop cascades to sequence test2_seq

drop cascades to table test2_1

drop cascades to table test2_2

DROP ROLEpathman_regress_hacker;

DROP ROLEregress_pathman_hacker;

DROP EXTENSION pg_pathman;

SET search_path = 'public';

CREATE EXTENSION pg_pathman;

CREATE SCHEMA permissions;

CREATE ROLEpathman_user1 LOGIN;

CREATE ROLEpathman_user2 LOGIN;

GRANT USAGE, CREATE ON SCHEMA permissions TOpathman_user1;

GRANT USAGE, CREATE ON SCHEMA permissions TOpathman_user2;

CREATE ROLEregress_pathman_user1 LOGIN;

CREATE ROLEregress_pathman_user2 LOGIN;

GRANT USAGE, CREATE ON SCHEMA permissions TOregress_pathman_user1;

GRANT USAGE, CREATE ON SCHEMA permissions TOregress_pathman_user2;

/* Switch to #1 */

SET ROLEpathman_user1;

SET ROLEregress_pathman_user1;

CREATE TABLE permissions.pathman_user1_table(id serial, a int);

INSERT INTO permissions.pathman_user1_table SELECT g, g FROM generate_series(1, 20) as g;

/* Should fail (can't SELECT) */

SET ROLEpathman_user2;

SET ROLEregress_pathman_user2;

DO $$

BEGIN

SELECT create_range_partitions('permissions.pathman_user1_table', 'id', 1, 10, 2);

RAISE NOTICE 'Insufficient priviliges';

END$$;

NOTICE: Insufficient priviliges

/* Grant SELECT topathman_user2 */

SET ROLEpathman_user1;

GRANT SELECT ON permissions.pathman_user1_table TOpathman_user2;

/* Grant SELECT toregress_pathman_user2 */

SET ROLEregress_pathman_user1;

GRANT SELECT ON permissions.pathman_user1_table TOregress_pathman_user2;

/* Should fail (don't own parent) */

SET ROLEpathman_user2;

SET ROLEregress_pathman_user2;

DO $$

BEGIN

SELECT create_range_partitions('permissions.pathman_user1_table', 'id', 1, 10, 2);

END$$;

NOTICE: Insufficient priviliges

/* Should be ok */

SET ROLEpathman_user1;

SET ROLEregress_pathman_user1;

SELECT create_range_partitions('permissions.pathman_user1_table', 'id', 1, 10, 2);

create_range_partitions

-------------------------

2

(1 row)

/* Should be able to see */

SET ROLEpathman_user2;

SET ROLEregress_pathman_user2;

SELECT * FROM pathman_config;

partrel | expr | parttype | range_interval

---------------------------------+------+----------+----------------

(1 row)

/* Should fail */

SET ROLEpathman_user2;

SET ROLEregress_pathman_user2;

SELECT set_enable_parent('permissions.pathman_user1_table', true);

WARNING: only the owner or superuser can change partitioning configuration of table "pathman_user1_table"

ERROR: new row violates row-level security policy for table "pathman_config_params"

SELECT set_auto('permissions.pathman_user1_table', false);

WARNING: only the owner or superuser can change partitioning configuration of table "pathman_user1_table"

ERROR: new row violates row-level security policy for table "pathman_config_params"

/* Should fail */

SET ROLEpathman_user2;

SET ROLEregress_pathman_user2;

DELETE FROM pathman_config

WHERE partrel = 'permissions.pathman_user1_table'::regclass;

WARNING: only the owner or superuser can change partitioning configuration of table "pathman_user1_table"

/* No rights to insert, should fail */

SET ROLEpathman_user2;

SET ROLEregress_pathman_user2;

DO $$

BEGIN

INSERT INTO permissions.pathman_user1_table (id, a) VALUES (35, 0);

END$$;

NOTICE: Insufficient priviliges

/* No rights to create partitions (need INSERT privilege) */

SET ROLEpathman_user2;

SET ROLEregress_pathman_user2;

SELECT prepend_range_partition('permissions.pathman_user1_table');

ERROR: permission denied for parent relation "pathman_user1_table"

/* Allowpathman_user2 to create partitions */

SET ROLEpathman_user1;

GRANT INSERT ON permissions.pathman_user1_table TOpathman_user2;

GRANT UPDATE(a) ON permissions.pathman_user1_table TOpathman_user2; /* per-column ACL */

/* Allowregress_pathman_user2 to create partitions */

SET ROLEregress_pathman_user1;

GRANT INSERT ON permissions.pathman_user1_table TOregress_pathman_user2;

GRANT UPDATE(a) ON permissions.pathman_user1_table TOregress_pathman_user2; /* per-column ACL */

/* Should be able to prepend a partition */

SET ROLEpathman_user2;

SET ROLEregress_pathman_user2;

SELECT prepend_range_partition('permissions.pathman_user1_table');

prepend_range_partition

-----------------------------------

ORDER BY range_min::int ASC /* prepend */

LIMIT 1)

ORDER BY attname; /* check ACL for each column */

attname | attacl

----------+---------------------------------

a | {pathman_user2=w/pathman_user1}

attname |attacl

----------+-------------------------------------------------

a | {regress_pathman_user2=w/regress_pathman_user1}

cmax |

cmin |

ctid |

(8 rows)

/* Have rights, should be ok (parent's ACL is shared by new children) */

SET ROLEpathman_user2;

SET ROLEregress_pathman_user2;

INSERT INTO permissions.pathman_user1_table (id, a) VALUES (35, 0) RETURNING *;

id | a

----+---

ORDER BY range_max::int DESC /* append */

LIMIT 3)

ORDER BY relname; /* we also check ACL for "pathman_user1_table_2" */

relname | relacl

-----------------------+----------------------------------------------------------------------

pathman_user1_table_2 | {pathman_user1=arwdDxt/pathman_user1,pathman_user2=r/pathman_user1}

pathman_user1_table_5 | {pathman_user1=arwdDxt/pathman_user1,pathman_user2=ar/pathman_user1}

pathman_user1_table_6 | {pathman_user1=arwdDxt/pathman_user1,pathman_user2=ar/pathman_user1}

relname |relacl

-----------------------+------------------------------------------------------------------------------------------------------

pathman_user1_table_2 | {regress_pathman_user1=arwdDxt/regress_pathman_user1,regress_pathman_user2=r/regress_pathman_user1}

pathman_user1_table_5 | {regress_pathman_user1=arwdDxt/regress_pathman_user1,regress_pathman_user2=ar/regress_pathman_user1}

pathman_user1_table_6 | {regress_pathman_user1=arwdDxt/regress_pathman_user1,regress_pathman_user2=ar/regress_pathman_user1}

(3 rows)

/* Try to drop partition, should fail */

END$$;

NOTICE: Insufficient priviliges

/* Disable automatic partition creation */

SET ROLEpathman_user1;

SET ROLEregress_pathman_user1;

SELECT set_auto('permissions.pathman_user1_table', false);

set_auto

----------

(1 row)

/* Partition creation, should fail */

SET ROLEpathman_user2;

SET ROLEregress_pathman_user2;

INSERT INTO permissions.pathman_user1_table (id, a) VALUES (55, 0) RETURNING *;

ERROR: no suitable partition for key '55'

/* Finally drop partitions */

SET ROLEpathman_user1;

SET ROLEregress_pathman_user1;

SELECT drop_partitions('permissions.pathman_user1_table');

NOTICE: 10 rows copied from permissions.pathman_user1_table_1

NOTICE: 10 rows copied from permissions.pathman_user1_table_2

(1 row)

/* Switch to #2 */

SET ROLEpathman_user2;

SET ROLEregress_pathman_user2;

/* Test ddl event trigger */

CREATE TABLE permissions.pathman_user2_table(id serial);

SELECT create_hash_partitions('permissions.pathman_user2_table', 'id', 3);

(1 row)

/* Switch to #1 */

SET ROLEpathman_user1;

SET ROLEregress_pathman_user1;

CREATE TABLE permissions.dropped_column(a int, val int not null, b int, c int);

INSERT INTO permissions.dropped_column SELECT i,i,i,i FROM generate_series(1, 30) i;

GRANT SELECT(val), INSERT(val) ON permissions.dropped_column TOpathman_user2;

GRANT SELECT(val), INSERT(val) ON permissions.dropped_column TOregress_pathman_user2;

SELECT create_range_partitions('permissions.dropped_column', 'val', 1, 10);

create_range_partitions

-------------------------

WHERE parent = 'permissions.dropped_column'::REGCLASS)

AND attacl IS NOT NULL

ORDER BY attrelid::regclass::text; /* check ACL for each column */

attrelid | attname | attacl

------------------------------+---------+----------------------------------

permissions.dropped_column_1 | val | {pathman_user2=ar/pathman_user1}

permissions.dropped_column_2 | val | {pathman_user2=ar/pathman_user1}

permissions.dropped_column_3 | val | {pathman_user2=ar/pathman_user1}

attrelid | attname |attacl

------------------------------+---------+--------------------------------------------------

permissions.dropped_column_1 | val | {regress_pathman_user2=ar/regress_pathman_user1}

permissions.dropped_column_2 | val | {regress_pathman_user2=ar/regress_pathman_user1}

permissions.dropped_column_3 | val | {regress_pathman_user2=ar/regress_pathman_user1}

(3 rows)

ALTER TABLE permissions.dropped_column DROP COLUMN a; /* DROP "a" */

WHERE parent = 'permissions.dropped_column'::REGCLASS)

AND attacl IS NOT NULL

ORDER BY attrelid::regclass::text; /* check ACL for each column (+1 partition) */

attrelid | attname | attacl

------------------------------+---------+----------------------------------

permissions.dropped_column_1 | val | {pathman_user2=ar/pathman_user1}

permissions.dropped_column_2 | val | {pathman_user2=ar/pathman_user1}

permissions.dropped_column_3 | val | {pathman_user2=ar/pathman_user1}

permissions.dropped_column_4 | val | {pathman_user2=ar/pathman_user1}

attrelid | attname |attacl

------------------------------+---------+--------------------------------------------------

permissions.dropped_column_1 | val | {regress_pathman_user2=ar/regress_pathman_user1}

permissions.dropped_column_2 | val | {regress_pathman_user2=ar/regress_pathman_user1}

permissions.dropped_column_3 | val | {regress_pathman_user2=ar/regress_pathman_user1}

permissions.dropped_column_4 | val | {regress_pathman_user2=ar/regress_pathman_user1}

(4 rows)

ALTER TABLE permissions.dropped_column DROP COLUMN b; /* DROP "b" */

WHERE parent = 'permissions.dropped_column'::REGCLASS)

AND attacl IS NOT NULL

ORDER BY attrelid::regclass::text; /* check ACL for each column (+1 partition) */

attrelid | attname | attacl

------------------------------+---------+----------------------------------

permissions.dropped_column_1 | val | {pathman_user2=ar/pathman_user1}

permissions.dropped_column_2 | val | {pathman_user2=ar/pathman_user1}

permissions.dropped_column_3 | val | {pathman_user2=ar/pathman_user1}

permissions.dropped_column_4 | val | {pathman_user2=ar/pathman_user1}

permissions.dropped_column_5 | val | {pathman_user2=ar/pathman_user1}

attrelid | attname |attacl

------------------------------+---------+--------------------------------------------------

permissions.dropped_column_1 | val | {regress_pathman_user2=ar/regress_pathman_user1}

permissions.dropped_column_2 | val | {regress_pathman_user2=ar/regress_pathman_user1}

permissions.dropped_column_3 | val | {regress_pathman_user2=ar/regress_pathman_user1}

permissions.dropped_column_4 | val | {regress_pathman_user2=ar/regress_pathman_user1}

permissions.dropped_column_5 | val | {regress_pathman_user2=ar/regress_pathman_user1}

(5 rows)

DROP TABLE permissions.dropped_column CASCADE;

NOTICE: drop cascades to 6 other objects

/* Finally reset user */

RESET ROLE;

DROP OWNED BYpathman_user1;

DROP OWNED BYpathman_user2;

DROP USERpathman_user1;

DROP USERpathman_user2;

DROP OWNED BYregress_pathman_user1;

DROP OWNED BYregress_pathman_user2;

DROP USERregress_pathman_user1;

DROP USERregress_pathman_user2;

DROP SCHEMA permissions;

DROP EXTENSION pg_pathman;