proper way to fix information_schema.key_column_usage view - Mailing list pgsql-hackers

Tom Lane commits
(http://www.postgresql.org/community/weeklynews/pwn20070121.html)

- Fix incorrect permissions check in
information_schema.key_column_usage view: it was checking a
pg_constraint OID instead of pg_class OID, resulting in "relation with
OID nnnnn does not exist" failures for anyone who wasn't owner of the
table being examined. Per bug #2848 from Laurence Rowe. Note: for
existing 8.2 installations a simple version update won't fix this; the
easiest fix is to CREATE OR REPLACE this view with the corrected
definition.

and from http://www.postgresql.org/docs/8.2/interactive/release-8-2-2.html

Fix incorrect permission check in information_schema.key_column_usage view (Tom)

The symptom is "relation with OID nnnnn does not exist" errors. To get
this fix without using initdb, use CREATE OR REPLACE VIEW to install
the corrected definition found in share/information_schema.sql. Note
you will need to do this in each database.

***********************

I had to feel my way carrying out this fix, and I don't know if I did
it right - I only know that it appears I no longer have the error.
Please confirm whether I was supposed to execute all of
share/information_schema.sql --- or just the portion that CREATEs or
REPLACEs key_column_usage view.

I did not execute the whole share/information_schema.sql --- I only
executed the part of it referring to the key_column_usage view and
each of the functions necessary to support that. CREATE OR REPLACE the
key_column_usage view gave errors otherwise for numerous missing
functions. That worries me because it makes me think I should execute
the whole thing. Instead I just kept adding the function definitions
until it successfully executed.

This is against a very large database which would take a long time to
restore if running the complete information_schema.sql screws things
up. Also I am not sure I would know right away if it was screwed up or
not. So hopefully you can tell me something such as "it's just a view
of info and won't cause changes in your data"?

Here's what I executed, successfully, and apparently the error in the
log file has ceased:

CREATE FUNCTION _pg_underlying_index(oid) RETURNS oid   LANGUAGE sql STRICT STABLE   AS $$
SELECT refobjid FROM pg_catalog.pg_depend WHERE classid = 'pg_catalog.pg_constraint'::pg_catalog.regclass AND
objid= $1 AND       refclassid = 'pg_catalog.pg_class'::pg_catalog.regclass AND       refobjsubid = 0 AND deptype =
'n'
$$;

CREATE DOMAIN cardinal_number AS integer   CONSTRAINT cardinal_number_domain_check CHECK (value >= 0);

CREATE DOMAIN sql_identifier AS character varying;

CREATE FUNCTION _pg_expandarray(IN anyarray, OUT x anyelement, OUT n int)   RETURNS SETOF RECORD   LANGUAGE sql STRICT
IMMUTABLE  AS 'select $1[s], s - pg_catalog.array_lower($1,1) + 1       from
pg_catalog.generate_series(pg_catalog.array_lower($1,1),
pg_catalog.array_upper($1,1),                                      1) as g(s)';
 

CREATE FUNCTION _pg_index_position(oid, smallint) RETURNS int   LANGUAGE sql STRICT STABLE   AS $$
SELECT (ss.a).n FROM (SELECT information_schema._pg_expandarray(indkey) AS a  FROM pg_catalog.pg_index WHERE indexrelid
=$1) ss WHERE (ss.a).x = $2;
 
$$;



CREATE or REPLACE VIEW key_column_usage AS   SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog,
      CAST(nc_nspname AS sql_identifier) AS constraint_schema,          CAST(conname AS sql_identifier) AS
constraint_name,         CAST(current_database() AS sql_identifier) AS table_catalog,          CAST(nr_nspname AS
sql_identifier)AS table_schema,          CAST(relname AS sql_identifier) AS table_name,          CAST(a.attname AS
sql_identifier)AS column_name,          CAST((ss.x).n AS cardinal_number) AS ordinal_position,          CAST(CASE WHEN
contype= 'f' THEN                 _pg_index_position(_pg_underlying_index(ss.coid),
ss.confkey[(ss.x).n])                   ELSE NULL               END AS cardinal_number)            AS
position_in_unique_constraint  FROM pg_attribute a,        (SELECT r.oid AS roid, r.relname, nc.nspname AS nc_nspname,
             nr.nspname AS nr_nspname,                c.oid AS coid, c.conname, c.contype, c.confkey, c.confrelid,
         _pg_expandarray(c.conkey) AS x         FROM pg_namespace nr, pg_class r, pg_namespace nc,
pg_constraintc         WHERE nr.oid = r.relnamespace               AND r.oid = c.conrelid               AND nc.oid =
c.connamespace              AND c.contype IN ('p', 'u', 'f')               AND r.relkind = 'r'               AND (NOT
pg_is_other_temp_schema(nr.oid))              AND (pg_has_role(r.relowner, 'USAGE')                    OR
has_table_privilege(r.oid,'SELECT')                    OR has_table_privilege(r.oid, 'INSERT')                    OR
has_table_privilege(r.oid,'UPDATE')                    OR has_table_privilege(r.oid, 'REFERENCES')) ) AS ss   WHERE
ss.roid= a.attrelid         AND a.attnum = (ss.x).x         AND NOT a.attisdropped;
 

GRANT SELECT ON key_column_usage TO PUBLIC;