Here is a "fixup" patch for
0002-Add-PUBLICATION-catalogs-and-DDL-v11.patch.gz with some minor fixes.
Two issues that should be addressed:
1. I think ALTER PUBLICATION does not need to require CREATE privilege
on the database. That should be easy to change.
2. By requiring only SELECT privilege to include a table in a
publication, someone could include a table without replica identity into
a publication and thus prevent updates to the table.
A while ago I had been working on a patch to create a new PUBLICATION
privilege for this purpose. I have attached the in-progress patch here.
We could either finish that up and include it, or commit your patch
initially with requiring superuser and then refine the permissions later.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
From 38aba08eb00ffc0b1f0d52a38864f825435a1694 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Thu, 8 Dec 2016 12:00:00 -0500
Subject: [PATCH] fixup! Add PUBLICATION catalogs and DDL
---
doc/src/sgml/catalogs.sgml | 35 ++++++++++-------------
doc/src/sgml/ref/alter_publication.sgml | 41 +++++++++------------------
doc/src/sgml/ref/create_publication.sgml | 46 +++++++++++++++++--------------
doc/src/sgml/ref/drop_publication.sgml | 6 ++--
doc/src/sgml/ref/psql-ref.sgml | 6 ++--
src/backend/catalog/Makefile | 2 +-
src/backend/catalog/dependency.c | 4 +--
src/backend/catalog/objectaddress.c | 25 +++++++----------
src/backend/catalog/pg_publication.c | 27 ++++++++++--------
src/backend/commands/publicationcmds.c | 12 ++++----
src/backend/commands/tablecmds.c | 9 +++---
src/backend/parser/gram.y | 2 +-
src/backend/utils/cache/relcache.c | 2 +-
src/backend/utils/cache/syscache.c | 2 +-
src/bin/pg_dump/pg_dump.c | 3 --
src/bin/psql/tab-complete.c | 8 +++---
src/test/regress/expected/publication.out | 22 +++++++--------
17 files changed, 115 insertions(+), 137 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index aacd4bcb23..5213aa4f5e 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -5149,12 +5149,11 @@ <title><structname>pg_publication</structname></title>
</indexterm>
<para>
- The <structname>pg_publication</structname> catalog contains
- all publications created in the database.
+ The catalog <structname>pg_publication</structname> contains all
+ publications created in the database.
</para>
<table>
-
<title><structname>pg_publication</structname> Columns</title>
<tgroup cols="4">
@@ -5179,7 +5178,7 @@ <title><structname>pg_publication</structname> Columns</title>
<entry><structfield>pubname</structfield></entry>
<entry><type>Name</type></entry>
<entry></entry>
- <entry>A unique, database-wide identifier for the publication.</entry>
+ <entry>Name of the publication</entry>
</row>
<row>
@@ -5202,26 +5201,25 @@ <title><structname>pg_publication</structname> Columns</title>
<entry><structfield>pubinsert</structfield></entry>
<entry><type>bool</type></entry>
<entry></entry>
- <entry>If true, INSERT operations are replicated for tables in the
- publication.</entry>
+ <entry>If true, <command>INSERT</command> operations are replicated for
+ tables in the publication.</entry>
</row>
<row>
<entry><structfield>pubupdate</structfield></entry>
<entry><type>bool</type></entry>
<entry></entry>
- <entry>If true, UPDATE operations are replicated for tables in the
- publication.</entry>
+ <entry>If true, <command>UPDATE</command> operations are replicated for
+ tables in the publication.</entry>
</row>
<row>
<entry><structfield>pubdelete</structfield></entry>
<entry><type>bool</type></entry>
<entry></entry>
- <entry>If true, DELETE operations are replicated for tables in the
- publication.</entry>
+ <entry>If true, <command>DELETE</command> operations are replicated for
+ tables in the publication.</entry>
</row>
-
</tbody>
</tgroup>
</table>
@@ -5235,13 +5233,12 @@ <title><structname>pg_publication_rel</structname></title>
</indexterm>
<para>
- The <structname>pg_publication_rel</structname> catalog contains
- mapping between tables and publications in the database. This is many to
- many mapping.
+ The catalog <structname>pg_publication_rel</structname> contains the
+ mapping between relations and publications in the database. This is a
+ many-to-many mapping.
</para>
<table>
-
<title><structname>pg_publication_rel</structname> Columns</title>
<tgroup cols="4">
@@ -5255,21 +5252,19 @@ <title><structname>pg_publication_rel</structname> Columns</title>
</thead>
<tbody>
-
<row>
<entry><structfield>prpubid</structfield></entry>
<entry><type>oid</type></entry>
<entry><literal><link linkend="catalog-pg-publication"><structname>pg_publication</structname></link>.oid</literal></entry>
- <entry>Publication reference.</entry>
+ <entry>Reference to publication</entry>
</row>
<row>
<entry><structfield>prrelid</structfield></entry>
- <entry><type>bool</type></entry>
+ <entry><type>oid</type></entry>
<entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
- <entry>Relation reference.</entry>
+ <entry>Reference to relation</entry>
</row>
-
</tbody>
</tgroup>
</table>
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index c17666c97f..eb8cb07126 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -40,21 +40,20 @@ <title>Description</title>
<para>
The first variant of this command listed in the synopsis can change
- all of the publication attributes specified in
- <xref linkend="sql-createpublication">.
- Attributes not mentioned in the command retain their previous settings.
- Database superusers can change any of these settings for any role.
+ all of the publication properties specified in
+ <xref linkend="sql-createpublication">. Properties not mentioned in the
+ command retain their previous settings. Database superusers can change any
+ of these settings for any role.
</para>
<para>
- The other variants of this command deal with table membership in the
- publication. The <literal>SET TABLE</literal> clause will replace
- the current list of tables in the publication with the specified one.
- Similarly the <literal>ADD TABLE</literal> and
- <literal>DROP TABLE</literal> will add and remove one or more table from
- publication.
+ The other variants of this command deal with the table membership of the
+ publication. The <literal>SET TABLE</literal> clause will replace the
+ list of tables in the publication with the specified one.
+ The <literal>ADD TABLE</literal> and
+ <literal>DROP TABLE</literal> will add and remove one or more tables from
+ the publication.
</para>
-
</refsect1>
<refsect1>
@@ -65,8 +64,7 @@ <title>Parameters</title>
<term><replaceable class="parameter">name</replaceable></term>
<listitem>
<para>
- The name of an existing publication whose attributes are to be
- altered.
+ The name of an existing publication whose definition is to be altered.
</para>
</listitem>
</varlistentry>
@@ -80,9 +78,8 @@ <title>Parameters</title>
<term><literal>NOPUBLISH DELETE</literal></term>
<listitem>
<para>
- These clauses alter attributes originally set by
- <xref linkend="SQL-CREATEPUBLICATION">. For more information, see the
- <command>CREATE PUBLICATION</command> reference page.
+ These clauses alter properties originally set by
+ <xref linkend="SQL-CREATEPUBLICATION">. See there for more information.
</para>
</listitem>
</varlistentry>
@@ -95,16 +92,6 @@ <title>Parameters</title>
</para>
</listitem>
</varlistentry>
-
- <varlistentry>
- <term><replaceable class="parameter">schema_name</replaceable></term>
- <listitem>
- <para>
- Name of a schema.
- </para>
- </listitem>
- </varlistentry>
-
</variablelist>
</refsect1>
@@ -124,7 +111,6 @@ <title>Examples</title>
ALTER PUBLICATION mypublication ADD TABLE users, departments;
</programlisting>
</para>
-
</refsect1>
<refsect1>
@@ -144,5 +130,4 @@ <title>See Also</title>
<member><xref linkend="sql-droppublication"></member>
</simplelist>
</refsect1>
-
</refentry>
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index a6bd3602fa..87becb4c0a 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -16,7 +16,7 @@
<refnamediv>
<refname>CREATE PUBLICATION</refname>
- <refpurpose>define new publication</refpurpose>
+ <refpurpose>define a new publication</refpurpose>
</refnamediv>
<refsynopsisdiv>
@@ -39,14 +39,14 @@ <title>Description</title>
<para>
<command>CREATE PUBLICATION</command> adds a new publication
- into the current database. The publication name must be distinct from
+ into the current database. The publication name must be distinct from
the name of any existing publication in the current database.
</para>
<para>
- A publication is essentially a group of tables intended for managing
- logical replication.
- </para>
+ A publication is essentially a group of tables whose data changes are
+ intended to be replicated through logical replication.
+ </para>
</refsect1>
<refsect1>
@@ -66,7 +66,7 @@ <title>Parameters</title>
<term><literal>FOR TABLE</literal></term>
<listitem>
<para>
- Specifies optional list of tables to add to the publication.
+ Specifies a list of tables to add to the publication.
</para>
</listitem>
</varlistentry>
@@ -75,8 +75,8 @@ <title>Parameters</title>
<term><literal>FOR ALL TABLES</literal></term>
<listitem>
<para>
- Marks the publication as one that replicates changes for all
- tables in the database, including the ones created in the future.
+ Marks the publication as one that replicates changes for all tables in
+ the database, including tables created in the future.
</para>
</listitem>
</varlistentry>
@@ -124,8 +124,14 @@ <title>Parameters</title>
<title>Notes</title>
<para>
- This operation itself does start replication. It only defines grouping
- and filtering logic for future subscribers.
+ If neither <literal>FOR TABLE</literal> nor <literal>FOR ALL
+ TABLES</literal> is specified, then the publication starts out with an
+ empty set of tables. That is useful if tables are to be added later.
+ </para>
+
+ <para>
+ The creation of a publication does not start replication. It only defines
+ a grouping and filtering logic for future subscribers.
</para>
<para>
@@ -135,23 +141,23 @@ <title>Notes</title>
</para>
<para>
- To add table to a publication, the invoking user must have
+ To add a table to a publication, the invoking user must have
<command>SELECT</command> privilege on given table. The
<command>FOR ALL TABLES</command> clause requires superuser.
</para>
<para>
- The tables added to a publication which publishes <command>UPDATE</command>
+ The tables added to a publication that publishes <command>UPDATE</command>
and/or <command>DELETE</command> operations must have
- <literal>REPLICA IDENTITY</> defined, otherwise these operations will not
- be allowed on them.
+ <literal>REPLICA IDENTITY</> defined. Otherwise those operations will be
+ disallowed on those tables.
</para>
<para>
- For the <command>INSERT ... ON CONFLICT</> command, the publication will
- publish resulting tuple operation. So depending of the outcome, it may
- be published as either <command>INSERT</command> or
- <command>UPDATE</command> or it may not be published at all.
+ For an <command>INSERT ... ON CONFLICT</> command, the publication will
+ publish the operation that actually results from the command. So depending
+ of the outcome, it may be published as either <command>INSERT</command> or
+ <command>UPDATE</command>, or it may not be published at all.
</para>
<para>
@@ -171,12 +177,11 @@ <title>Examples</title>
</para>
<para>
- Create an insert only publication:
+ Create an insert-only publication:
<programlisting>
CREATE PUBLICATION insert_only WITH (NOPUBLISH UPDATE, NOPUBLISH DELETE);
</programlisting>
</para>
-
</refsect1>
<refsect1>
@@ -196,5 +201,4 @@ <title>See Also</title>
<member><xref linkend="sql-droppublication"></member>
</simplelist>
</refsect1>
-
</refentry>
diff --git a/doc/src/sgml/ref/drop_publication.sgml b/doc/src/sgml/ref/drop_publication.sgml
index 6c0e3ba959..d05d522041 100644
--- a/doc/src/sgml/ref/drop_publication.sgml
+++ b/doc/src/sgml/ref/drop_publication.sgml
@@ -16,7 +16,7 @@
<refnamediv>
<refname>DROP PUBLICATION</refname>
- <refpurpose>remove an existing publication</refpurpose>
+ <refpurpose>remove a publication</refpurpose>
</refnamediv>
<refsynopsisdiv>
@@ -29,7 +29,8 @@
<title>Description</title>
<para>
- <command>DROP PUBLICATION</command> removes publications from the database.
+ <command>DROP PUBLICATION</command> removes an existing publication from
+ the database.
</para>
<para>
@@ -82,5 +83,4 @@ <title>See Also</title>
<member><xref linkend="sql-alterpublication"></member>
</simplelist>
</refsect1>
-
</refentry>
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index 678b6f7fe5..93d024b4c7 100644
--- a/doc/src/sgml/ref/psql-ref.sgml
+++ b/doc/src/sgml/ref/psql-ref.sgml
@@ -1604,11 +1604,11 @@ <title>Meta-Commands</title>
<term><literal>\dRp[+] [ <link linkend="APP-PSQL-patterns"><replaceable class="parameter">pattern</replaceable></link> ]</literal></term>
<listitem>
<para>
- List replication publications.
+ Lists replication publications.
If <replaceable class="parameter">pattern</replaceable> is
- specified, only publications whose names match the pattern are
+ specified, only those publications whose names match the pattern are
listed.
- If <literal>+</literal> is appended to the command name, tables
+ If <literal>+</literal> is appended to the command name, the tables
associated with each publication are shown as well.
</para>
</listitem>
diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile
index 5866097dd7..73b514c04c 100644
--- a/src/backend/catalog/Makefile
+++ b/src/backend/catalog/Makefile
@@ -15,7 +15,7 @@ OBJS = catalog.o dependency.o heap.o index.o indexing.o namespace.o aclchk.o \
pg_constraint.o pg_conversion.o \
pg_depend.o pg_enum.o pg_inherits.o pg_largeobject.o pg_namespace.o \
pg_operator.o pg_proc.o pg_publication.o pg_range.o \
- pg_db_role_setting.o pg_shdepend.o pg_type.o storage.o toasting.o
+ pg_db_role_setting.o pg_shdepend.o pg_type.o storage.o toasting.o
BKIFILES = postgres.bki postgres.description postgres.shdescription
diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index d72c948342..4e5b68f8b9 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -166,8 +166,8 @@ static const Oid object_classes[] = {
ExtensionRelationId, /* OCLASS_EXTENSION */
EventTriggerRelationId, /* OCLASS_EVENT_TRIGGER */
PolicyRelationId, /* OCLASS_POLICY */
- PublicationRelationId, /* OCLASS_PUBCLICATION */
- PublicationRelRelationId, /* OCLASS_PUBCLICATION_REL */
+ PublicationRelationId, /* OCLASS_PUBLICATION */
+ PublicationRelRelationId, /* OCLASS_PUBLICATION_REL */
TransformRelationId /* OCLASS_TRANSFORM */
};
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 8fb5c0a683..59249804bd 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -1781,9 +1781,8 @@ get_object_address_usermapping(List *objname, List *objargs, bool missing_ok)
}
/*
- * Find the ObjectAddress for a publication relation.
- * The objname parameter is relation name while objargs contains publication
- * name.
+ * Find the ObjectAddress for a publication relation. The objname parameter
+ * is the relation name; objargs contains the publication name.
*/
static ObjectAddress
get_object_address_publication_rel(List *objname, List *objargs,
@@ -2267,6 +2266,11 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address,
format_type_be(targettypeid))));
}
break;
+ case OBJECT_PUBLICATION:
+ if (!pg_publication_ownercheck(address.objectId, roleid))
+ aclcheck_error(ACLCHECK_NOT_OWNER, ACL_KIND_PUBLICATION,
+ NameListToString(objname));
+ break;
case OBJECT_TRANSFORM:
{
TypeName *typename = (TypeName *) linitial(objname);
@@ -2315,7 +2319,6 @@ check_object_ownership(Oid roleid, ObjectType objtype, ObjectAddress address,
case OBJECT_TSPARSER:
case OBJECT_TSTEMPLATE:
case OBJECT_ACCESS_METHOD:
- case OBJECT_PUBLICATION:
/* We treat these object types as being owned by superusers */
if (!superuser_arg(roleid))
ereport(ERROR,
@@ -3278,16 +3281,8 @@ getObjectDescription(const ObjectAddress *object)
case OCLASS_PUBLICATION:
{
- HeapTuple tup;
-
- tup = SearchSysCache1(PUBLICATIONOID,
- ObjectIdGetDatum(object->objectId));
- if (!HeapTupleIsValid(tup))
- elog(ERROR, "cache lookup failed for publication %u",
- object->objectId);
- appendStringInfo(&buffer, _("publicaton %s"),
- NameStr(((Form_pg_publication) GETSTRUCT(tup))->pubname));
- ReleaseSysCache(tup);
+ appendStringInfo(&buffer, _("publication %s"),
+ get_publication_name(object->objectId));
break;
}
@@ -4802,7 +4797,7 @@ getObjectIdentityParts(const ObjectAddress *object,
prform = (Form_pg_publication_rel) GETSTRUCT(tup);
pubname = get_publication_name(prform->prpubid);
- appendStringInfo(&buffer, _("%s in publication %s"),
+ appendStringInfo(&buffer, _("publication table %s in publication %s"),
get_rel_name(prform->prrelid), pubname);
if (objname)
diff --git a/src/backend/catalog/pg_publication.c b/src/backend/catalog/pg_publication.c
index d77db2043f..e3560b7f94 100644
--- a/src/backend/catalog/pg_publication.c
+++ b/src/backend/catalog/pg_publication.c
@@ -1,9 +1,9 @@
/*-------------------------------------------------------------------------
*
* publication.c
- * publication C api manipulation
+ * publication C API manipulation
*
- * Copyright (c) 2015, PostgreSQL Global Development Group
+ * Copyright (c) 2016, PostgreSQL Global Development Group
*
* IDENTIFICATION
* publication.c
@@ -60,23 +60,25 @@ check_publication_add_relation(Relation targetrel)
if (RelationGetForm(targetrel)->relkind != RELKIND_RELATION)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("only tables can be added to publication"),
- errdetail("%s is not a table",
- RelationGetRelationName(targetrel))));
+ errmsg("\"%s\" is not a table",
+ RelationGetRelationName(targetrel)),
+ errdetail("Only tables can be added to publications.")));
/* Can't be system table */
if (IsCatalogRelation(targetrel))
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("only user tables can be added to publication"),
- errdetail("%s is a system table",
- RelationGetRelationName(targetrel))));
+ errmsg("\"%s\" is a system table",
+ RelationGetRelationName(targetrel)),
+ errdetail("System tables cannot be added to publications.")));
/* UNLOGGED and TEMP relations cannot be part of publication. */
if (!RelationNeedsWAL(targetrel))
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("UNLOGGED and TEMP relations cannot be replicated")));
+ errmsg("table \"%s\" cannot be replicated",
+ RelationGetRelationName(targetrel)),
+ errdetail("Temporary and unlogged relations cannot be replicated.")));
}
/*
@@ -125,8 +127,8 @@ publication_add_relation(Oid pubid, Relation targetrel,
return InvalidObjectAddress;
ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_OBJECT),
- errmsg("relation %s is already member of publication %s",
+ (errcode(ERRCODE_DUPLICATE_OBJECT),
+ errmsg("relation \"%s\" is already member of publication \"%s\"",
RelationGetRelationName(targetrel), pub->name)));
}
@@ -148,8 +150,9 @@ publication_add_relation(Oid pubid, Relation targetrel,
CatalogUpdateIndexes(rel, tup);
heap_freetuple(tup);
- /* Add dependency on the publication */
ObjectAddressSet(myself, PublicationRelRelationId, prrelid);
+
+ /* Add dependency on the publication */
ObjectAddressSet(referenced, PublicationRelationId, pubid);
recordDependencyOn(&myself, &referenced, DEPENDENCY_AUTO);
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index ebdef06665..bf16b653b1 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -3,7 +3,7 @@
* publicationcmds.c
* publication manipulation
*
- * Copyright (c) 2015, PostgreSQL Global Development Group
+ * Copyright (c) 2016, PostgreSQL Global Development Group
*
* IDENTIFICATION
* publicationcmds.c
@@ -240,8 +240,6 @@ CreatePublication(CreatePublicationStmt *stmt)
heap_close(rel, RowExclusiveLock);
- CommandCounterIncrement();
-
return myself;
}
@@ -320,10 +318,10 @@ AlterPublicationTables(AlterPublicationStmt *stmt, Relation rel,
/* Check that user is allowed to manipulate the publication tables. */
if (pubform->puballtables)
ereport(ERROR,
- (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
- errmsg("tables cannot be added to or dropped from FOR ALL TABLES publications"),
- errdetail("publication %s is defined as FOR ALL TABLES",
- NameStr(pubform->pubname))));
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("publication \"%s\" is defined as FOR ALL TABLES",
+ NameStr(pubform->pubname)),
+ errdetail("Tables cannot be added to or dropped from FOR ALL TABLES publications.")));
Assert(list_length(stmt->tables) > 0);
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index c9dbe1857f..a64e2ca2de 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -11341,11 +11341,12 @@ ATPrepChangePersistence(Relation rel, bool toLogged)
* UNLOGGED as UNLOGGED tables can't be published.
*/
if (!toLogged &&
- list_length(GetRelationPublications(RelationGetRelid(rel))))
+ list_length(GetRelationPublications(RelationGetRelid(rel))) > 0)
ereport(ERROR,
- (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
- errmsg("could not change table \"%s\" to unlogged because it is published",
- RelationGetRelationName(rel))));
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("cannot change table \"%s\" to unlogged because it is part of a publication",
+ RelationGetRelationName(rel)),
+ errdetail("Unlogged relations cannot be replicated.")));
/*
* Check existing foreign key constraints to preserve the invariant that
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0e2aef1fb0..a7f220791a 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -384,7 +384,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <list> group_by_list
%type <node> group_by_item empty_grouping_set rollup_clause cube_clause
%type <node> grouping_sets_clause
-%type <node> opt_publication_for_tables publication_for_tables
+%type <node> opt_publication_for_tables publication_for_tables
%type <list> opt_fdw_options fdw_options
%type <defelt> fdw_option
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 96b6c8b09a..07fdca16cd 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -4698,7 +4698,7 @@ GetRelationPublicationActions(Relation relation)
relation->rd_pubactions = NULL;
}
- /* Now save copy of the bitmap in the relcache entry. */
+ /* Now save copy of the actions in the relcache entry. */
oldcxt = MemoryContextSwitchTo(CacheMemoryContext);
relation->rd_pubactions = palloc(sizeof(PublicationActions));
memcpy(relation->rd_pubactions, pubactions, sizeof(PublicationActions));
diff --git a/src/backend/utils/cache/syscache.c b/src/backend/utils/cache/syscache.c
index c7a4127402..395d379be6 100644
--- a/src/backend/utils/cache/syscache.c
+++ b/src/backend/utils/cache/syscache.c
@@ -49,9 +49,9 @@
#include "catalog/pg_operator.h"
#include "catalog/pg_opfamily.h"
#include "catalog/pg_proc.h"
-#include "catalog/pg_range.h"
#include "catalog/pg_publication.h"
#include "catalog/pg_publication_rel.h"
+#include "catalog/pg_range.h"
#include "catalog/pg_rewrite.h"
#include "catalog/pg_seclabel.h"
#include "catalog/pg_shdepend.h"
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 4bcde1bede..d173f59a74 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -3293,9 +3293,6 @@ getPublications(Archive *fout)
query = createPQExpBuffer();
- if (g_verbose)
- write_msg(NULL, "reading publications\n");
-
resetPQExpBuffer(query);
/* Get the publications. */
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index d7216a06b8..d6b333a72d 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -960,7 +960,7 @@ static const pgsql_thing_t words_after_create[] = {
{"OWNED", NULL, NULL, THING_NO_CREATE}, /* for DROP OWNED BY ... */
{"PARSER", Query_for_list_of_ts_parsers, NULL, THING_NO_SHOW},
{"POLICY", NULL, NULL},
- {"bPUBLICATION", NULL, NULL},
+ {"PUBLICATION", NULL, NULL},
{"ROLE", Query_for_list_of_roles},
{"RULE", "SELECT pg_catalog.quote_ident(rulename) FROM pg_catalog.pg_rules WHERE substring(pg_catalog.quote_ident(rulename),1,%d)='%s'"},
{"SCHEMA", Query_for_list_of_schemas},
@@ -2195,9 +2195,9 @@ psql_completion(const char *text, int start, int end)
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
/* Complete "CREATE PUBLICATION <name> [...] WITH" */
else if (HeadMatches2("CREATE", "PUBLICATION") && TailMatches2("WITH", "("))
- COMPLETE_WITH_LIST6("REPLICATE_INSERT", "NOREPLICATE_INSERT",
- "REPLICATE_UPDATE", "NOREPLICATE_UPDATE",
- "REPLICATE_DELETE", "NOREPLICATE_DELETE");
+ COMPLETE_WITH_LIST2("PUBLISH", "NOPUBLISH");
+ else if (HeadMatches2("CREATE", "PUBLICATION") && TailMatches3("WITH", "(", MatchAny))
+ COMPLETE_WITH_LIST3("INSERT", "UPDATE", "DELETE");
/* CREATE RULE */
/* Complete "CREATE RULE <sth>" with "AS ON" */
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index b281649bc8..ebbb8c0880 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -33,16 +33,16 @@ ALTER PUBLICATION testpub_foralltables WITH (publish update);
CREATE TABLE testpub_tbl2 (id serial primary key, data text);
-- fail - can't add to for all tables publication
ALTER PUBLICATION testpub_foralltables ADD TABLE testpub_tbl2;
-ERROR: tables cannot be added to or dropped from FOR ALL TABLES publications
-DETAIL: publication testpub_foralltables is defined as FOR ALL TABLES
+ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES
+DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications.
-- fail - can't drop from all tables publication
ALTER PUBLICATION testpub_foralltables DROP TABLE testpub_tbl2;
-ERROR: tables cannot be added to or dropped from FOR ALL TABLES publications
-DETAIL: publication testpub_foralltables is defined as FOR ALL TABLES
+ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES
+DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications.
-- fail - can't add to for all tables publication
ALTER PUBLICATION testpub_foralltables SET TABLE pub_test.testpub_nopk;
-ERROR: tables cannot be added to or dropped from FOR ALL TABLES publications
-DETAIL: publication testpub_foralltables is defined as FOR ALL TABLES
+ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES
+DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications.
SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_foralltables';
pubname | puballtables
----------------------+--------------
@@ -64,12 +64,12 @@ DROP TABLE testpub_tbl2;
DROP PUBLICATION testpub_foralltables;
-- fail - view
CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_view;
-ERROR: only tables can be added to publication
-DETAIL: testpub_view is not a table
+ERROR: "testpub_view" is not a table
+DETAIL: Only tables can be added to publications.
CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1, pub_test.testpub_nopk;
-- fail - already added
ALTER PUBLICATION testpub_fortbl ADD TABLE testpub_tbl1;
-ERROR: relation testpub_tbl1 is already member of publication testpub_fortbl
+ERROR: relation "testpub_tbl1" is already member of publication "testpub_fortbl"
-- fail - already added
CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1;
ERROR: publication "testpub_fortbl" already exists
@@ -84,8 +84,8 @@ Tables:
-- fail - view
ALTER PUBLICATION testpub_default ADD TABLE testpub_view;
-ERROR: only tables can be added to publication
-DETAIL: testpub_view is not a table
+ERROR: "testpub_view" is not a table
+DETAIL: Only tables can be added to publications.
ALTER PUBLICATION testpub_default ADD TABLE testpub_tbl1;
ALTER PUBLICATION testpub_default SET TABLE testpub_tbl1;
ALTER PUBLICATION testpub_default ADD TABLE pub_test.testpub_nopk;
--
2.11.0
From ce569a3f72fd04955e514585157cff207268a81c Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Wed, 5 Oct 2016 12:00:00 -0400
Subject: [PATCH] Add PUBLICATION privilege
---
doc/src/sgml/ref/grant.sgml | 15 ++++++++++--
src/backend/catalog/aclchk.c | 4 ++++
src/backend/commands/publicationcmds.c | 10 ++++++++
src/backend/utils/adt/acl.c | 9 +++++++
src/bin/pg_dump/dumputils.c | 2 ++
src/include/nodes/parsenodes.h | 3 ++-
src/include/utils/acl.h | 5 ++--
src/test/regress/expected/dependency.out | 22 +++++++++---------
src/test/regress/expected/privileges.out | 40 ++++++++++++++++----------------
src/test/regress/sql/dependency.sql | 2 +-
10 files changed, 75 insertions(+), 37 deletions(-)
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index d8ca39f869..6b0fbb1ff4 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -21,7 +21,7 @@
<refsynopsisdiv>
<synopsis>
-GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
+GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | PUBLICATION }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] <replaceable class="PARAMETER">table_name</replaceable> [, ...]
| ALL TABLES IN SCHEMA <replaceable class="PARAMETER">schema_name</replaceable> [, ...] }
@@ -276,6 +276,16 @@ <title>GRANT on Database Objects</title>
</varlistentry>
<varlistentry>
+ <term>PUBLICATION</term>
+ <listitem>
+ <para>
+ Allows the use of the specified table in a publication. (See the
+ <xref linkend="sql-createpublication"> statement.)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term>CREATE</term>
<listitem>
<para>
@@ -531,12 +541,13 @@ <title>Notes</title>
D -- TRUNCATE
x -- REFERENCES
t -- TRIGGER
+ p -- PUBLICATION
X -- EXECUTE
U -- USAGE
C -- CREATE
c -- CONNECT
T -- TEMPORARY
- arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
+ arwdDxtp -- ALL PRIVILEGES (for tables, varies for other objects)
* -- grant option for preceding privilege
/yyyy -- role that granted this privilege
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index c12f31a376..3e60c5476b 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -3249,6 +3249,8 @@ string_to_privilege(const char *privname)
return ACL_CREATE_TEMP;
if (strcmp(privname, "connect") == 0)
return ACL_CONNECT;
+ if (strcmp(privname, "publication") == 0)
+ return ACL_PUBLICATION;
if (strcmp(privname, "rule") == 0)
return 0; /* ignore old RULE privileges */
ereport(ERROR,
@@ -3286,6 +3288,8 @@ privilege_to_string(AclMode privilege)
return "TEMP";
case ACL_CONNECT:
return "CONNECT";
+ case ACL_PUBLICATION:
+ return "PUBLICATION";
default:
elog(ERROR, "unrecognized privilege: %d", (int) privilege);
}
diff --git a/src/backend/commands/publicationcmds.c b/src/backend/commands/publicationcmds.c
index eb5436f72b..3e00414879 100644
--- a/src/backend/commands/publicationcmds.c
+++ b/src/backend/commands/publicationcmds.c
@@ -568,6 +568,8 @@ RemovePublicationRelById(Oid prid)
/*
* Gather all tables optinally filtered by schema name.
* The gathered tables are locked in access share lock mode.
+ *
+ * TODO check table permissions/wait for code changes
*/
static List *
GatherTables(char *nspname)
@@ -651,9 +653,17 @@ GatherTableList(List *tables)
Relation rel;
bool recurse = interpretInhOption(rv->inhOpt);
Oid myrelid;
+ AclResult aclresult;
rel = heap_openrv(rv, AccessShareLock);
myrelid = RelationGetRelid(rel);
+
+ aclresult = pg_class_aclcheck(myrelid, GetUserId(), ACL_PUBLICATION);
+ if (aclresult != ACLCHECK_OK)
+ if (aclresult != ACLCHECK_OK)
+ aclcheck_error(aclresult, ACL_KIND_CLASS,
+ RelationGetRelationName(rel));
+
/* don't throw error for "foo, foo" */
if (list_member_oid(relids, myrelid))
{
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index 025a99e55a..5ebd86a061 100644
--- a/src/backend/utils/adt/acl.c
+++ b/src/backend/utils/adt/acl.c
@@ -314,6 +314,9 @@ aclparse(const char *s, AclItem *aip)
case ACL_CONNECT_CHR:
read = ACL_CONNECT;
break;
+ case ACL_PUBLICATION_CHR:
+ read = ACL_PUBLICATION;
+ break;
case 'R': /* ignore old RULE privileges */
read = 0;
break;
@@ -1625,6 +1628,8 @@ convert_priv_string(text *priv_type_text)
return ACL_CREATE_TEMP;
if (pg_strcasecmp(priv_type, "CONNECT") == 0)
return ACL_CONNECT;
+ if (pg_strcasecmp(priv_type, "PUBLICATION") == 0)
+ return ACL_PUBLICATION;
if (pg_strcasecmp(priv_type, "RULE") == 0)
return 0; /* ignore old RULE privileges */
@@ -1721,6 +1726,8 @@ convert_aclright_to_string(int aclright)
return "TEMPORARY";
case ACL_CONNECT:
return "CONNECT";
+ case ACL_PUBLICATION:
+ return "PUBLICATION";
default:
elog(ERROR, "unrecognized aclright: %d", aclright);
return NULL;
@@ -2032,6 +2039,8 @@ convert_table_priv_string(text *priv_type_text)
{"REFERENCES WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_REFERENCES)},
{"TRIGGER", ACL_TRIGGER},
{"TRIGGER WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_TRIGGER)},
+ {"PUBLICATION", ACL_PUBLICATION},
+ {"PUBLICATION WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_PUBLICATION)},
{"RULE", 0}, /* ignore old RULE privileges */
{"RULE WITH GRANT OPTION", 0},
{NULL, 0}
diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.c
index cd1e8c4a68..c668ec7494 100644
--- a/src/bin/pg_dump/dumputils.c
+++ b/src/bin/pg_dump/dumputils.c
@@ -492,6 +492,8 @@ do { \
CONVERT_PRIV('a', "INSERT");
if (remoteVersion >= 70200)
CONVERT_PRIV('x', "REFERENCES");
+ if (remoteVersion >= 100000)
+ CONVERT_PRIV('p', "PUBLICATION");
/* rest are not applicable to columns */
if (subname == NULL)
{
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index f017ee4721..260c48d364 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -73,7 +73,8 @@ typedef uint32 AclMode; /* a bitmask of privilege bits */
#define ACL_CREATE (1<<9) /* for namespaces and databases */
#define ACL_CREATE_TEMP (1<<10) /* for databases */
#define ACL_CONNECT (1<<11) /* for databases */
-#define N_ACL_RIGHTS 12 /* 1 plus the last 1<<x */
+#define ACL_PUBLICATION (1<<12)
+#define N_ACL_RIGHTS 13 /* 1 plus the last 1<<x */
#define ACL_NO_RIGHTS 0
/* Currently, SELECT ... FOR [KEY] UPDATE/SHARE requires UPDATE privileges */
#define ACL_SELECT_FOR_UPDATE ACL_UPDATE
diff --git a/src/include/utils/acl.h b/src/include/utils/acl.h
index 6b25eb9101..8008cfef71 100644
--- a/src/include/utils/acl.h
+++ b/src/include/utils/acl.h
@@ -139,15 +139,16 @@ typedef ArrayType Acl;
#define ACL_CREATE_CHR 'C'
#define ACL_CREATE_TEMP_CHR 'T'
#define ACL_CONNECT_CHR 'c'
+#define ACL_PUBLICATION_CHR 'p'
/* string holding all privilege code chars, in order by bitmask position */
-#define ACL_ALL_RIGHTS_STR "arwdDxtXUCTc"
+#define ACL_ALL_RIGHTS_STR "arwdDxtXUCTcp"
/*
* Bitmasks defining "all rights" for each supported object type
*/
#define ACL_ALL_RIGHTS_COLUMN (ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_REFERENCES)
-#define ACL_ALL_RIGHTS_RELATION (ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_DELETE|ACL_TRUNCATE|ACL_REFERENCES|ACL_TRIGGER)
+#define ACL_ALL_RIGHTS_RELATION (ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_DELETE|ACL_TRUNCATE|ACL_REFERENCES|ACL_TRIGGER|ACL_PUBLICATION)
#define ACL_ALL_RIGHTS_SEQUENCE (ACL_USAGE|ACL_SELECT|ACL_UPDATE)
#define ACL_ALL_RIGHTS_DATABASE (ACL_CREATE|ACL_CREATE_TEMP|ACL_CONNECT)
#define ACL_ALL_RIGHTS_FDW (ACL_USAGE)
diff --git a/src/test/regress/expected/dependency.out b/src/test/regress/expected/dependency.out
index 8e50f8ffbb..33b11c5732 100644
--- a/src/test/regress/expected/dependency.out
+++ b/src/test/regress/expected/dependency.out
@@ -19,7 +19,7 @@ DETAIL: privileges for table deptest
REVOKE SELECT ON deptest FROM GROUP regress_dep_group;
DROP GROUP regress_dep_group;
-- can't drop the user if we revoke the privileges partially
-REVOKE SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES ON deptest FROM regress_dep_user;
+REVOKE SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, PUBLICATION ON deptest FROM regress_dep_user;
DROP USER regress_dep_user;
ERROR: role "regress_dep_user" cannot be dropped because some objects depend on it
DETAIL: privileges for table deptest
@@ -63,21 +63,21 @@ CREATE TABLE deptest (a serial primary key, b text);
GRANT ALL ON deptest1 TO regress_dep_user2;
RESET SESSION AUTHORIZATION;
\z deptest1
- Access privileges
- Schema | Name | Type | Access privileges | Column privileges | Policies
---------+----------+-------+----------------------------------------------------+-------------------+----------
- public | deptest1 | table | regress_dep_user0=arwdDxt/regress_dep_user0 +| |
- | | | regress_dep_user1=a*r*w*d*D*x*t*/regress_dep_user0+| |
- | | | regress_dep_user2=arwdDxt/regress_dep_user1 | |
+ Access privileges
+ Schema | Name | Type | Access privileges | Column privileges | Policies
+--------+----------+-------+------------------------------------------------------+-------------------+----------
+ public | deptest1 | table | regress_dep_user0=arwdDxtp/regress_dep_user0 +| |
+ | | | regress_dep_user1=a*r*w*d*D*x*t*p*/regress_dep_user0+| |
+ | | | regress_dep_user2=arwdDxtp/regress_dep_user1 | |
(1 row)
DROP OWNED BY regress_dep_user1;
-- all grants revoked
\z deptest1
- Access privileges
- Schema | Name | Type | Access privileges | Column privileges | Policies
---------+----------+-------+---------------------------------------------+-------------------+----------
- public | deptest1 | table | regress_dep_user0=arwdDxt/regress_dep_user0 | |
+ Access privileges
+ Schema | Name | Type | Access privileges | Column privileges | Policies
+--------+----------+-------+----------------------------------------------+-------------------+----------
+ public | deptest1 | table | regress_dep_user0=arwdDxtp/regress_dep_user0 | |
(1 row)
-- table was dropped
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index f66b4432a1..fa47c3d0b3 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -1487,38 +1487,38 @@ set session role regress_user4;
grant select on dep_priv_test to regress_user5;
\dp dep_priv_test
Access privileges
- Schema | Name | Type | Access privileges | Column privileges | Policies
---------+---------------+-------+-------------------------------------+-------------------+----------
- public | dep_priv_test | table | regress_user1=arwdDxt/regress_user1+| |
- | | | regress_user2=r*/regress_user1 +| |
- | | | regress_user3=r*/regress_user1 +| |
- | | | regress_user4=r*/regress_user2 +| |
- | | | regress_user4=r*/regress_user3 +| |
- | | | regress_user5=r/regress_user4 | |
+ Schema | Name | Type | Access privileges | Column privileges | Policies
+--------+---------------+-------+--------------------------------------+-------------------+----------
+ public | dep_priv_test | table | regress_user1=arwdDxtp/regress_user1+| |
+ | | | regress_user2=r*/regress_user1 +| |
+ | | | regress_user3=r*/regress_user1 +| |
+ | | | regress_user4=r*/regress_user2 +| |
+ | | | regress_user4=r*/regress_user3 +| |
+ | | | regress_user5=r/regress_user4 | |
(1 row)
set session role regress_user2;
revoke select on dep_priv_test from regress_user4 cascade;
\dp dep_priv_test
Access privileges
- Schema | Name | Type | Access privileges | Column privileges | Policies
---------+---------------+-------+-------------------------------------+-------------------+----------
- public | dep_priv_test | table | regress_user1=arwdDxt/regress_user1+| |
- | | | regress_user2=r*/regress_user1 +| |
- | | | regress_user3=r*/regress_user1 +| |
- | | | regress_user4=r*/regress_user3 +| |
- | | | regress_user5=r/regress_user4 | |
+ Schema | Name | Type | Access privileges | Column privileges | Policies
+--------+---------------+-------+--------------------------------------+-------------------+----------
+ public | dep_priv_test | table | regress_user1=arwdDxtp/regress_user1+| |
+ | | | regress_user2=r*/regress_user1 +| |
+ | | | regress_user3=r*/regress_user1 +| |
+ | | | regress_user4=r*/regress_user3 +| |
+ | | | regress_user5=r/regress_user4 | |
(1 row)
set session role regress_user3;
revoke select on dep_priv_test from regress_user4 cascade;
\dp dep_priv_test
Access privileges
- Schema | Name | Type | Access privileges | Column privileges | Policies
---------+---------------+-------+-------------------------------------+-------------------+----------
- public | dep_priv_test | table | regress_user1=arwdDxt/regress_user1+| |
- | | | regress_user2=r*/regress_user1 +| |
- | | | regress_user3=r*/regress_user1 | |
+ Schema | Name | Type | Access privileges | Column privileges | Policies
+--------+---------------+-------+--------------------------------------+-------------------+----------
+ public | dep_priv_test | table | regress_user1=arwdDxtp/regress_user1+| |
+ | | | regress_user2=r*/regress_user1 +| |
+ | | | regress_user3=r*/regress_user1 | |
(1 row)
set session role regress_user1;
diff --git a/src/test/regress/sql/dependency.sql b/src/test/regress/sql/dependency.sql
index f5c45e4666..2bdd51532c 100644
--- a/src/test/regress/sql/dependency.sql
+++ b/src/test/regress/sql/dependency.sql
@@ -21,7 +21,7 @@ CREATE TABLE deptest (f1 serial primary key, f2 text);
DROP GROUP regress_dep_group;
-- can't drop the user if we revoke the privileges partially
-REVOKE SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES ON deptest FROM regress_dep_user;
+REVOKE SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, PUBLICATION ON deptest FROM regress_dep_user;
DROP USER regress_dep_user;
-- now we are OK to drop him
--
2.11.0
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
