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 <[email protected]>
> > +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 lean toward the former, attached. Conveniently, every released branch has
the same definition for this view.
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>
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
