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;
