Skip to content

Commit 87dee41

Browse files
peterepetere
committed
Add COMMENT and SECURITY LABEL support for publications and subscriptions
1 parent 7678fe1 commit 87dee41

File tree

11 files changed

+100
-12
lines changed

11 files changed

+100
-12
lines changed

‎doc/src/sgml/ref/comment.sgml

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -46,12 +46,14 @@ COMMENT ON
4646
OPERATOR FAMILY <replaceable class="PARAMETER">object_name</replaceable> USING <replaceable class="parameter">index_method</replaceable> |
4747
POLICY <replaceable class="PARAMETER">policy_name</replaceable> ON <replaceable class="PARAMETER">table_name</replaceable> |
4848
[ PROCEDURAL ] LANGUAGE <replaceable class="PARAMETER">object_name</replaceable> |
49+
PUBLICATION <replaceable class="PARAMETER">object_name</replaceable> |
4950
ROLE <replaceable class="PARAMETER">object_name</replaceable> |
5051
RULE <replaceable class="PARAMETER">rule_name</replaceable> ON <replaceable class="PARAMETER">table_name</replaceable> |
5152
SCHEMA <replaceable class="PARAMETER">object_name</replaceable> |
5253
SEQUENCE <replaceable class="PARAMETER">object_name</replaceable> |
5354
SERVER <replaceable class="PARAMETER">object_name</replaceable> |
5455
STATISTICS <replaceable class="PARAMETER">object_name</replaceable> |
56+
SUBSCRIPTION <replaceable class="PARAMETER">object_name</replaceable> |
5557
TABLE <replaceable class="PARAMETER">object_name</replaceable> |
5658
TABLESPACE <replaceable class="PARAMETER">object_name</replaceable> |
5759
TEXT SEARCH CONFIGURATION <replaceable class="PARAMETER">object_name</replaceable> |

‎doc/src/sgml/ref/security_label.sgml

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -34,9 +34,11 @@ SECURITY LABEL [ FOR <replaceable class="PARAMETER">provider</replaceable> ] ON
3434
LARGE OBJECT <replaceable class="PARAMETER">large_object_oid</replaceable> |
3535
MATERIALIZED VIEW <replaceable class="PARAMETER">object_name</replaceable> |
3636
[ PROCEDURAL ] LANGUAGE <replaceable class="PARAMETER">object_name</replaceable> |
37+
PUBLICATION <replaceable class="PARAMETER">object_name</replaceable> |
3738
ROLE <replaceable class="PARAMETER">object_name</replaceable> |
3839
SCHEMA <replaceable class="PARAMETER">object_name</replaceable> |
3940
SEQUENCE <replaceable class="PARAMETER">object_name</replaceable> |
41+
SUBSCRIPTION <replaceable class="PARAMETER">object_name</replaceable> |
4042
TABLESPACE <replaceable class="PARAMETER">object_name</replaceable> |
4143
TYPE <replaceable class="PARAMETER">object_name</replaceable> |
4244
VIEW <replaceable class="PARAMETER">object_name</replaceable>

‎src/backend/catalog/system_views.sql

Lines changed: 22 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -424,6 +424,28 @@ FROM
424424
WHERE
425425
l.objsubid = 0
426426
UNION ALL
427+
SELECT
428+
l.objoid, l.classoid, l.objsubid,
429+
'publication'::text AS objtype,
430+
NULL::oid AS objnamespace,
431+
quote_ident(p.pubname) AS objname,
432+
l.provider, l.label
433+
FROM
434+
pg_seclabel l
435+
JOIN pg_publication p ON l.classoid = p.tableoid AND l.objoid = p.oid
436+
WHERE
437+
l.objsubid = 0
438+
UNION ALL
439+
SELECT
440+
l.objoid, l.classoid, 0::int4 AS objsubid,
441+
'subscription'::text AS objtype,
442+
NULL::oid AS objnamespace,
443+
quote_ident(s.subname) AS objname,
444+
l.provider, l.label
445+
FROM
446+
pg_shseclabel l
447+
JOIN pg_subscription s ON l.classoid = s.tableoid AND l.objoid = s.oid
448+
UNION ALL
427449
SELECT
428450
l.objoid, l.classoid, 0::int4 AS objsubid,
429451
'database'::text AS objtype,

‎src/backend/parser/gram.y

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6340,9 +6340,11 @@ comment_type_name:
63406340
| EXTENSION { $$ = OBJECT_EXTENSION; }
63416341
| FOREIGN DATA_P WRAPPER { $$ = OBJECT_FDW; }
63426342
| opt_procedural LANGUAGE { $$ = OBJECT_LANGUAGE; }
6343+
| PUBLICATION { $$ = OBJECT_PUBLICATION; }
63436344
| ROLE { $$ = OBJECT_ROLE; }
63446345
| SCHEMA { $$ = OBJECT_SCHEMA; }
63456346
| SERVER { $$ = OBJECT_FOREIGN_SERVER; }
6347+
| SUBSCRIPTION { $$ = OBJECT_SUBSCRIPTION; }
63466348
| TABLESPACE { $$ = OBJECT_TABLESPACE; }
63476349
;
63486350

@@ -6453,8 +6455,10 @@ security_label_type_name:
64536455
DATABASE { $$ = OBJECT_DATABASE; }
64546456
| EVENT TRIGGER { $$ = OBJECT_EVENT_TRIGGER; }
64556457
| opt_procedural LANGUAGE { $$ = OBJECT_LANGUAGE; }
6458+
| PUBLICATION { $$ = OBJECT_PUBLICATION; }
64566459
| ROLE { $$ = OBJECT_ROLE; }
64576460
| SCHEMA { $$ = OBJECT_SCHEMA; }
6461+
| SUBSCRIPTION { $$ = OBJECT_SUBSCRIPTION; }
64586462
| TABLESPACE { $$ = OBJECT_TABLESPACE; }
64596463
;
64606464

‎src/test/modules/dummy_seclabel/expected/dummy_seclabel.out

Lines changed: 20 additions & 12 deletions
Original file line numberDiff line numberDiff line change
@@ -67,20 +67,28 @@ SECURITY LABEL ON FUNCTION dummy_seclabel_four() IS 'classified'; -- OK
6767
SECURITY LABEL ON DOMAIN dummy_seclabel_domain IS 'classified'; -- OK
6868
CREATE SCHEMA dummy_seclabel_test;
6969
SECURITY LABEL ON SCHEMA dummy_seclabel_test IS 'unclassified'; -- OK
70+
SET client_min_messages = error;
71+
CREATE PUBLICATION dummy_pub;
72+
CREATE SUBSCRIPTION dummy_sub CONNECTION '' PUBLICATION foo WITH (NOCONNECT);
73+
RESET client_min_messages;
74+
SECURITY LABEL ON PUBLICATION dummy_pub IS 'classified';
75+
SECURITY LABEL ON SUBSCRIPTION dummy_sub IS 'classified';
7076
SELECT objtype, objname, provider, label FROM pg_seclabels
7177
ORDER BY objtype, objname;
72-
objtype | objname | provider | label
73-
----------+------------------------------+----------+--------------
74-
column | dummy_seclabel_tbl1.a | dummy | unclassified
75-
domain | dummy_seclabel_domain | dummy | classified
76-
function | dummy_seclabel_four() | dummy | classified
77-
role | regress_dummy_seclabel_user1 | dummy | classified
78-
role | regress_dummy_seclabel_user2 | dummy | unclassified
79-
schema | dummy_seclabel_test | dummy | unclassified
80-
table | dummy_seclabel_tbl1 | dummy | top secret
81-
table | dummy_seclabel_tbl2 | dummy | classified
82-
view | dummy_seclabel_view1 | dummy | classified
83-
(9 rows)
78+
objtype | objname | provider | label
79+
--------------+------------------------------+----------+--------------
80+
column | dummy_seclabel_tbl1.a | dummy | unclassified
81+
domain | dummy_seclabel_domain | dummy | classified
82+
function | dummy_seclabel_four() | dummy | classified
83+
publication | dummy_pub | dummy | classified
84+
role | regress_dummy_seclabel_user1 | dummy | classified
85+
role | regress_dummy_seclabel_user2 | dummy | unclassified
86+
schema | dummy_seclabel_test | dummy | unclassified
87+
subscription | dummy_sub | dummy | classified
88+
table | dummy_seclabel_tbl1 | dummy | top secret
89+
table | dummy_seclabel_tbl2 | dummy | classified
90+
view | dummy_seclabel_view1 | dummy | classified
91+
(11 rows)
8492

8593
-- check for event trigger
8694
CREATE FUNCTION event_trigger_test()

‎src/test/modules/dummy_seclabel/sql/dummy_seclabel.sql

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -71,6 +71,13 @@ SECURITY LABEL ON DOMAIN dummy_seclabel_domain IS 'classified'; -- OK
7171
CREATE SCHEMA dummy_seclabel_test;
7272
SECURITY LABEL ON SCHEMA dummy_seclabel_test IS 'unclassified'; -- OK
7373

74+
SET client_min_messages = error;
75+
CREATE PUBLICATION dummy_pub;
76+
CREATE SUBSCRIPTION dummy_sub CONNECTION '' PUBLICATION foo WITH (NOCONNECT);
77+
RESET client_min_messages;
78+
SECURITY LABEL ON PUBLICATION dummy_pub IS 'classified';
79+
SECURITY LABEL ON SUBSCRIPTION dummy_sub IS 'classified';
80+
7481
SELECT objtype, objname, provider, label FROM pg_seclabels
7582
ORDER BY objtype, objname;
7683

‎src/test/regress/expected/publication.out

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6,6 +6,13 @@ CREATE ROLE regress_publication_user2;
66
CREATE ROLE regress_publication_user_dummy LOGIN NOSUPERUSER;
77
SET SESSION AUTHORIZATION 'regress_publication_user';
88
CREATE PUBLICATION testpub_default;
9+
COMMENT ON PUBLICATION testpub_default IS 'test publication';
10+
SELECT obj_description(p.oid, 'pg_publication') FROM pg_publication p;
11+
obj_description
12+
------------------
13+
test publication
14+
(1 row)
15+
916
CREATE PUBLICATION testpib_ins_trunct WITH (nopublish delete, nopublish update);
1017
ALTER PUBLICATION testpub_default WITH (nopublish insert, nopublish delete);
1118
\dRp

‎src/test/regress/expected/rules.out

Lines changed: 23 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1605,6 +1605,29 @@ UNION ALL
16051605
FROM (pg_seclabel l
16061606
JOIN pg_event_trigger evt ON (((l.classoid = evt.tableoid) AND (l.objoid = evt.oid))))
16071607
WHERE (l.objsubid = 0)
1608+
UNION ALL
1609+
SELECT l.objoid,
1610+
l.classoid,
1611+
l.objsubid,
1612+
'publication'::text AS objtype,
1613+
NULL::oid AS objnamespace,
1614+
quote_ident((p.pubname)::text) AS objname,
1615+
l.provider,
1616+
l.label
1617+
FROM (pg_seclabel l
1618+
JOIN pg_publication p ON (((l.classoid = p.tableoid) AND (l.objoid = p.oid))))
1619+
WHERE (l.objsubid = 0)
1620+
UNION ALL
1621+
SELECT l.objoid,
1622+
l.classoid,
1623+
0 AS objsubid,
1624+
'subscription'::text AS objtype,
1625+
NULL::oid AS objnamespace,
1626+
quote_ident((s.subname)::text) AS objname,
1627+
l.provider,
1628+
l.label
1629+
FROM (pg_shseclabel l
1630+
JOIN pg_subscription s ON (((l.classoid = s.tableoid) AND (l.objoid = s.oid))))
16081631
UNION ALL
16091632
SELECT l.objoid,
16101633
l.classoid,

‎src/test/regress/expected/subscription.out

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -30,6 +30,13 @@ ERROR: publication name "foo" used more than once
3030
-- ok
3131
CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (NOCONNECT);
3232
WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
33+
COMMENT ON SUBSCRIPTION testsub IS 'test subscription';
34+
SELECT obj_description(s.oid, 'pg_subscription') FROM pg_subscription s;
35+
obj_description
36+
-------------------
37+
test subscription
38+
(1 row)
39+
3340
-- fail - name already exists
3441
CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (NOCONNECT);
3542
ERROR: subscription "testsub" already exists

‎src/test/regress/sql/publication.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -8,6 +8,9 @@ SET SESSION AUTHORIZATION 'regress_publication_user';
88

99
CREATE PUBLICATION testpub_default;
1010

11+
COMMENT ON PUBLICATION testpub_default IS 'test publication';
12+
SELECT obj_description(p.oid, 'pg_publication') FROM pg_publication p;
13+
1114
CREATE PUBLICATION testpib_ins_trunct WITH (nopublish delete, nopublish update);
1215

1316
ALTER PUBLICATION testpub_default WITH (nopublish insert, nopublish delete);

‎src/test/regress/sql/subscription.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -27,6 +27,9 @@ CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION foo, te
2727
-- ok
2828
CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (NOCONNECT);
2929

30+
COMMENT ON SUBSCRIPTION testsub IS 'test subscription';
31+
SELECT obj_description(s.oid, 'pg_subscription') FROM pg_subscription s;
32+
3033
-- fail - name already exists
3134
CREATE SUBSCRIPTION testsub CONNECTION 'dbname=doesnotexist' PUBLICATION testpub WITH (NOCONNECT);
3235

0 commit comments

Comments
 (0)