On Thu, Aug 31, 2017 at 02:53:45AM +, Noah Misch wrote:
> On Sat, Aug 26, 2017 at 03:31:12PM -0400, Tom Lane wrote:
> > +
> > +
> > +
> > + Show foreign tables
> > + in information_schema.table_privileges
> > + view (Peter Eisentraut)
> > +
> > +
> > +
> > + All other relevant information_schema views include
> > + foreign tables, but this one ignored them.
> > +
> > +
> > +
> > + Since this view definition is installed by 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 psql:
> > +
> > +BEGIN;
> > +DROP SCHEMA information_schema CASCADE;
> > +\i SHAREDIR/information_schema.sql
> > +COMMIT;
> > +
> > + (Run pg_config --sharedir if you're uncertain
> > + where SHAREDIR is.) This must be repeated in each
> > + database to be fixed.
> > +
> > +
>
> "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 psql:
-BEGIN;
-DROP SCHEMA information_schema CASCADE;
-\i 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');
- (Run pg_config --sharedir if you're uncertain
- where SHAREDIR is.) This must be repeated in each
- database to be fixed.
+ This must be repeated in each database to be fixed,
+ including template0.
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 psql:
-BEGIN;
-DROP SCHEMA information_schema CASCADE;
-\i 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
