Back-branch release notes up for review

Started by Tom Laneover 8 years ago3 messages

tgl@sss.pgh.pa.us

over 8 years ago

I've drafted notes for next week's brown-paper-bag releases.
If you want to review, see
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=f1b10496a55a64b2872633850e55a2cd9d1c9108

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Noah Misch

noah@leadboat.com

over 8 years ago

In reply to: Tom Lane (#1)

Re: Back-branch release notes up for review

On Sat, Aug 26, 2017 at 03:31:12PM -0400, Tom Lane wrote:

+    <listitem>
+<!--
+Author: Peter Eisentraut <peter_e@gmx.net>
+Branch: master [0659465ca] 2017-08-15 19:27:22 -0400
+Branch: REL_10_STABLE [3ea58216d] 2017-08-15 19:30:35 -0400
+Branch: REL9_6_STABLE [dce90c7c8] 2017-08-15 19:31:06 -0400
+Branch: REL9_5_STABLE [dbeefe64f] 2017-08-15 19:32:00 -0400
+Branch: REL9_4_STABLE [52427015a] 2017-08-15 19:32:41 -0400
+Branch: REL9_3_STABLE [9f0f4efc2] 2017-08-15 19:32:52 -0400
+Branch: REL9_2_STABLE [98e6784aa] 2017-08-15 19:33:04 -0400
+-->
+     <para>
+      Show foreign tables
+      in <structname>information_schema</>.<structname>table_privileges</>
+      view (Peter Eisentraut)
+     </para>
+
+     <para>
+      All other relevant <structname>information_schema</> views include
+      foreign tables, but this one ignored them.
+     </para>
+
+     <para>
+      Since this view definition is installed by <application>initdb</>,
+      merely upgrading will not fix the problem.  If you need to fix this
+      in an existing installation, you can, as a superuser, do this
+      in <application>psql</>:
+<programlisting>
+BEGIN;
+DROP SCHEMA information_schema CASCADE;
+\i <replaceable>SHAREDIR</>/information_schema.sql
+COMMIT;
+</programlisting>
+      (Run <literal>pg_config --sharedir</> if you're uncertain
+      where <replaceable>SHAREDIR</> is.)  This must be repeated in each
+      database to be fixed.
+     </para>
+    </listitem>

"DROP SCHEMA information_schema CASCADE;" will drop objects outside
information_schema that depend on objects inside information_schema. For
example, this will drop user-defined views if the view query refers to
information_schema. That's improper in a release-noted update procedure.
This could instead offer a CREATE OR REPLACE VIEW or just hand-wave about the
repaired definition being available in information_schema.sql.

I regret not reading this before today.

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Noah Misch

noah@leadboat.com

over 8 years ago

In reply to: Noah Misch (#2)

1 attachment(s)

Re: Back-branch release notes up for review

On Thu, Aug 31, 2017 at 02:53:45AM +0000, Noah Misch wrote:

On Sat, Aug 26, 2017 at 03:31:12PM -0400, Tom Lane wrote:

+    <listitem>
+<!--
+Author: Peter Eisentraut <peter_e@gmx.net>
+Branch: master [0659465ca] 2017-08-15 19:27:22 -0400
+Branch: REL_10_STABLE [3ea58216d] 2017-08-15 19:30:35 -0400
+Branch: REL9_6_STABLE [dce90c7c8] 2017-08-15 19:31:06 -0400
+Branch: REL9_5_STABLE [dbeefe64f] 2017-08-15 19:32:00 -0400
+Branch: REL9_4_STABLE [52427015a] 2017-08-15 19:32:41 -0400
+Branch: REL9_3_STABLE [9f0f4efc2] 2017-08-15 19:32:52 -0400
+Branch: REL9_2_STABLE [98e6784aa] 2017-08-15 19:33:04 -0400
+-->
+     <para>
+      Show foreign tables
+      in <structname>information_schema</>.<structname>table_privileges</>
+      view (Peter Eisentraut)
+     </para>
+
+     <para>
+      All other relevant <structname>information_schema</> views include
+      foreign tables, but this one ignored them.
+     </para>
+
+     <para>
+      Since this view definition is installed by <application>initdb</>,
+      merely upgrading will not fix the problem.  If you need to fix this
+      in an existing installation, you can, as a superuser, do this
+      in <application>psql</>:
+<programlisting>
+BEGIN;
+DROP SCHEMA information_schema CASCADE;
+\i <replaceable>SHAREDIR</>/information_schema.sql
+COMMIT;
+</programlisting>
+      (Run <literal>pg_config --sharedir</> if you're uncertain
+      where <replaceable>SHAREDIR</> is.)  This must be repeated in each
+      database to be fixed.
+     </para>
+    </listitem>

I lean toward the former, attached. Conveniently, every released branch has
the same definition for this view.

Attachments:

relnotes-dont-drop-schema-v1.patchtext/plain; charset=us-asciiDownload

diff --git a/doc/src/sgml/release-9.2.sgml b/doc/src/sgml/release-9.2.sgml
index faa7ae4..6fa21e3 100644
--- a/doc/src/sgml/release-9.2.sgml
+++ b/doc/src/sgml/release-9.2.sgml
@@ -58,14 +58,44 @@
       in an existing installation, you can, as a superuser, do this
       in <application>psql</>:
 <programlisting>
-BEGIN;
-DROP SCHEMA information_schema CASCADE;
-\i <replaceable>SHAREDIR</>/information_schema.sql
-COMMIT;
+SET search_path TO information_schema;
+CREATE OR REPLACE VIEW table_privileges AS
+    SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
+           CAST(grantee.rolname AS sql_identifier) AS grantee,
+           CAST(current_database() AS sql_identifier) AS table_catalog,
+           CAST(nc.nspname AS sql_identifier) AS table_schema,
+           CAST(c.relname AS sql_identifier) AS table_name,
+           CAST(c.prtype AS character_data) AS privilege_type,
+           CAST(
+             CASE WHEN
+                  -- object owner always has grant options
+                  pg_has_role(grantee.oid, c.relowner, 'USAGE')
+                  OR c.grantable
+                  THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable,
+           CAST(CASE WHEN c.prtype = 'SELECT' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS with_hierarchy
+
+    FROM (
+            SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
+         ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
+         pg_namespace nc,
+         pg_authid u_grantor,
+         (
+           SELECT oid, rolname FROM pg_authid
+           UNION ALL
+           SELECT 0::oid, 'PUBLIC'
+         ) AS grantee (oid, rolname)
+
+    WHERE c.relnamespace = nc.oid
+          AND c.relkind IN ('r', 'v', 'f')
+          AND c.grantee = grantee.oid
+          AND c.grantor = u_grantor.oid
+          AND c.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER')
+          AND (pg_has_role(u_grantor.oid, 'USAGE')
+               OR pg_has_role(grantee.oid, 'USAGE')
+               OR grantee.rolname = 'PUBLIC');
 </programlisting>
-      (Run <literal>pg_config --sharedir</> if you're uncertain
-      where <replaceable>SHAREDIR</> is.)  This must be repeated in each
-      database to be fixed.
+      This must be repeated in each database to be fixed,
+      including <literal>template0</>.
      </para>
     </listitem>
 
diff --git a/doc/src/sgml/release-9.3.sgml b/doc/src/sgml/release-9.3.sgml
index f3b00a7..91fbb34 100644
--- a/doc/src/sgml/release-9.3.sgml
+++ b/doc/src/sgml/release-9.3.sgml
@@ -52,14 +52,44 @@
       in an existing installation, you can, as a superuser, do this
       in <application>psql</>:
 <programlisting>
-BEGIN;
-DROP SCHEMA information_schema CASCADE;
-\i <replaceable>SHAREDIR</>/information_schema.sql
-COMMIT;
+SET search_path TO information_schema;
+CREATE OR REPLACE VIEW table_privileges AS
+    SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
+           CAST(grantee.rolname AS sql_identifier) AS grantee,
+           CAST(current_database() AS sql_identifier) AS table_catalog,
+           CAST(nc.nspname AS sql_identifier) AS table_schema,
+           CAST(c.relname AS sql_identifier) AS table_name,
+           CAST(c.prtype AS character_data) AS privilege_type,
+           CAST(
+             CASE WHEN
+                  -- object owner always has grant options
+                  pg_has_role(grantee.oid, c.relowner, 'USAGE')
+                  OR c.grantable
+                  THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable,
+           CAST(CASE WHEN c.prtype = 'SELECT' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS with_hierarchy
+
+    FROM (
+            SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
+         ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
+         pg_namespace nc,
+         pg_authid u_grantor,
+         (
+           SELECT oid, rolname FROM pg_authid
+           UNION ALL
+           SELECT 0::oid, 'PUBLIC'
+         ) AS grantee (oid, rolname)
+
+    WHERE c.relnamespace = nc.oid
+          AND c.relkind IN ('r', 'v', 'f')
+          AND c.grantee = grantee.oid
+          AND c.grantor = u_grantor.oid
+          AND c.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER')
+          AND (pg_has_role(u_grantor.oid, 'USAGE')
+               OR pg_has_role(grantee.oid, 'USAGE')
+               OR grantee.rolname = 'PUBLIC');
 </programlisting>
-      (Run <literal>pg_config --sharedir</> if you're uncertain
-      where <replaceable>SHAREDIR</> is.)  This must be repeated in each
-      database to be fixed.
+      This must be repeated in each database to be fixed,
+      including <literal>template0</>.
      </para>
     </listitem>
 
diff --git a/doc/src/sgml/release-9.4.sgml b/doc/src/sgml/release-9.4.sgml
index 227e5e2..c665f90 100644
--- a/doc/src/sgml/release-9.4.sgml
+++ b/doc/src/sgml/release-9.4.sgml
@@ -68,14 +68,44 @@ Branch: REL9_4_STABLE [b51c8efc6] 2017-08-24 15:21:32 -0700
       in an existing installation, you can, as a superuser, do this
       in <application>psql</>:
 <programlisting>
-BEGIN;
-DROP SCHEMA information_schema CASCADE;
-\i <replaceable>SHAREDIR</>/information_schema.sql
-COMMIT;
+SET search_path TO information_schema;
+CREATE OR REPLACE VIEW table_privileges AS
+    SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
+           CAST(grantee.rolname AS sql_identifier) AS grantee,
+           CAST(current_database() AS sql_identifier) AS table_catalog,
+           CAST(nc.nspname AS sql_identifier) AS table_schema,
+           CAST(c.relname AS sql_identifier) AS table_name,
+           CAST(c.prtype AS character_data) AS privilege_type,
+           CAST(
+             CASE WHEN
+                  -- object owner always has grant options
+                  pg_has_role(grantee.oid, c.relowner, 'USAGE')
+                  OR c.grantable
+                  THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable,
+           CAST(CASE WHEN c.prtype = 'SELECT' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS with_hierarchy
+
+    FROM (
+            SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
+         ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
+         pg_namespace nc,
+         pg_authid u_grantor,
+         (
+           SELECT oid, rolname FROM pg_authid
+           UNION ALL
+           SELECT 0::oid, 'PUBLIC'
+         ) AS grantee (oid, rolname)
+
+    WHERE c.relnamespace = nc.oid
+          AND c.relkind IN ('r', 'v', 'f')
+          AND c.grantee = grantee.oid
+          AND c.grantor = u_grantor.oid
+          AND c.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER')
+          AND (pg_has_role(u_grantor.oid, 'USAGE')
+               OR pg_has_role(grantee.oid, 'USAGE')
+               OR grantee.rolname = 'PUBLIC');
 </programlisting>
-      (Run <literal>pg_config --sharedir</> if you're uncertain
-      where <replaceable>SHAREDIR</> is.)  This must be repeated in each
-      database to be fixed.
+      This must be repeated in each database to be fixed,
+      including <literal>template0</>.
      </para>
     </listitem>
 
diff --git a/doc/src/sgml/release-9.5.sgml b/doc/src/sgml/release-9.5.sgml
index 62b3114..0f700dd 100644
--- a/doc/src/sgml/release-9.5.sgml
+++ b/doc/src/sgml/release-9.5.sgml
@@ -51,14 +51,44 @@
       in an existing installation, you can, as a superuser, do this
       in <application>psql</>:
 <programlisting>
-BEGIN;
-DROP SCHEMA information_schema CASCADE;
-\i <replaceable>SHAREDIR</>/information_schema.sql
-COMMIT;
+SET search_path TO information_schema;
+CREATE OR REPLACE VIEW table_privileges AS
+    SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
+           CAST(grantee.rolname AS sql_identifier) AS grantee,
+           CAST(current_database() AS sql_identifier) AS table_catalog,
+           CAST(nc.nspname AS sql_identifier) AS table_schema,
+           CAST(c.relname AS sql_identifier) AS table_name,
+           CAST(c.prtype AS character_data) AS privilege_type,
+           CAST(
+             CASE WHEN
+                  -- object owner always has grant options
+                  pg_has_role(grantee.oid, c.relowner, 'USAGE')
+                  OR c.grantable
+                  THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable,
+           CAST(CASE WHEN c.prtype = 'SELECT' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS with_hierarchy
+
+    FROM (
+            SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
+         ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
+         pg_namespace nc,
+         pg_authid u_grantor,
+         (
+           SELECT oid, rolname FROM pg_authid
+           UNION ALL
+           SELECT 0::oid, 'PUBLIC'
+         ) AS grantee (oid, rolname)
+
+    WHERE c.relnamespace = nc.oid
+          AND c.relkind IN ('r', 'v', 'f')
+          AND c.grantee = grantee.oid
+          AND c.grantor = u_grantor.oid
+          AND c.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER')
+          AND (pg_has_role(u_grantor.oid, 'USAGE')
+               OR pg_has_role(grantee.oid, 'USAGE')
+               OR grantee.rolname = 'PUBLIC');
 </programlisting>
-      (Run <literal>pg_config --sharedir</> if you're uncertain
-      where <replaceable>SHAREDIR</> is.)  This must be repeated in each
-      database to be fixed.
+      This must be repeated in each database to be fixed,
+      including <literal>template0</>.
      </para>
     </listitem>
 
diff --git a/doc/src/sgml/release-9.6.sgml b/doc/src/sgml/release-9.6.sgml
index fa5355f..dc811c4 100644
--- a/doc/src/sgml/release-9.6.sgml
+++ b/doc/src/sgml/release-9.6.sgml
@@ -61,14 +61,44 @@ Branch: REL9_2_STABLE [98e6784aa] 2017-08-15 19:33:04 -0400
       in an existing installation, you can, as a superuser, do this
       in <application>psql</>:
 <programlisting>
-BEGIN;
-DROP SCHEMA information_schema CASCADE;
-\i <replaceable>SHAREDIR</>/information_schema.sql
-COMMIT;
+SET search_path TO information_schema;
+CREATE OR REPLACE VIEW table_privileges AS
+    SELECT CAST(u_grantor.rolname AS sql_identifier) AS grantor,
+           CAST(grantee.rolname AS sql_identifier) AS grantee,
+           CAST(current_database() AS sql_identifier) AS table_catalog,
+           CAST(nc.nspname AS sql_identifier) AS table_schema,
+           CAST(c.relname AS sql_identifier) AS table_name,
+           CAST(c.prtype AS character_data) AS privilege_type,
+           CAST(
+             CASE WHEN
+                  -- object owner always has grant options
+                  pg_has_role(grantee.oid, c.relowner, 'USAGE')
+                  OR c.grantable
+                  THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable,
+           CAST(CASE WHEN c.prtype = 'SELECT' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS with_hierarchy
+
+    FROM (
+            SELECT oid, relname, relnamespace, relkind, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* FROM pg_class
+         ) AS c (oid, relname, relnamespace, relkind, relowner, grantor, grantee, prtype, grantable),
+         pg_namespace nc,
+         pg_authid u_grantor,
+         (
+           SELECT oid, rolname FROM pg_authid
+           UNION ALL
+           SELECT 0::oid, 'PUBLIC'
+         ) AS grantee (oid, rolname)
+
+    WHERE c.relnamespace = nc.oid
+          AND c.relkind IN ('r', 'v', 'f')
+          AND c.grantee = grantee.oid
+          AND c.grantor = u_grantor.oid
+          AND c.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'TRUNCATE', 'REFERENCES', 'TRIGGER')
+          AND (pg_has_role(u_grantor.oid, 'USAGE')
+               OR pg_has_role(grantee.oid, 'USAGE')
+               OR grantee.rolname = 'PUBLIC');
 </programlisting>
-      (Run <literal>pg_config --sharedir</> if you're uncertain
-      where <replaceable>SHAREDIR</> is.)  This must be repeated in each
-      database to be fixed.
+      This must be repeated in each database to be fixed,
+      including <literal>template0</>.
      </para>
     </listitem>