X-Git-Url: https://granicus.if.org/sourcecode?a=blobdiff_plain;f=src%2Fbackend%2Fcatalog%2Finformation_schema.sql;h=659d7cb5a49b465eb68c7c034fe067014f3fdadf;hb=ee943004466418595363d567f18c053bae407792;hp=a51767c507250a90a278d971846ea68c34aa2d99;hpb=55b113257c883722bef8a4dd145c6a0c3fde687f;p=postgresql diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index a51767c507..659d7cb5a4 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -1,10 +1,18 @@ /* * SQL Information Schema - * as defined in ISO 9075-2:1999 chapter 20 + * as defined in ISO/IEC 9075-11:2011 * - * Copyright 2003, PostgreSQL Global Development Group + * Copyright (c) 2003-2016, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.17 2003/11/29 22:39:40 pgsql Exp $ + * src/backend/catalog/information_schema.sql + * + * Note: this file is read in single-user -j mode, which means that the + * command terminator is semicolon-newline-newline; whenever the backend + * sees that, it stops and executes what it's got. If you write a lot of + * statements without empty lines between, they'll all get quoted to you + * in any error message about one of them, so don't do that. Also, you + * cannot write a semicolon immediately followed by an empty line in a + * string literal (including a function body!) or a multiline comment. */ /* @@ -18,20 +26,167 @@ /* - * 20.2 + * 5.1 * INFORMATION_SCHEMA schema */ CREATE SCHEMA information_schema; GRANT USAGE ON SCHEMA information_schema TO PUBLIC; -SET search_path TO information_schema, public; +SET search_path TO information_schema; + + +/* + * A few supporting functions first ... + */ + +/* Expand any 1-D array into a set with integers 1..N */ +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_keysequal(smallint[], smallint[]) RETURNS boolean + LANGUAGE sql IMMUTABLE -- intentionally not STRICT, to allow inlining + AS 'select $1 operator(pg_catalog.<@) $2 and $2 operator(pg_catalog.<@) $1'; + +/* Given an index's OID and an underlying-table column number, return the + * column's position in the index (NULL if not there) */ +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 FUNCTION _pg_truetypid(pg_attribute, pg_type) RETURNS oid + LANGUAGE sql + IMMUTABLE + RETURNS NULL ON NULL INPUT + AS +$$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typbasetype ELSE $1.atttypid END$$; + +CREATE FUNCTION _pg_truetypmod(pg_attribute, pg_type) RETURNS int4 + LANGUAGE sql + IMMUTABLE + RETURNS NULL ON NULL INPUT + AS +$$SELECT CASE WHEN $2.typtype = 'd' THEN $2.typtypmod ELSE $1.atttypmod END$$; + +-- these functions encapsulate knowledge about the encoding of typmod: + +CREATE FUNCTION _pg_char_max_length(typid oid, typmod int4) RETURNS integer + LANGUAGE sql + IMMUTABLE + RETURNS NULL ON NULL INPUT + AS +$$SELECT + CASE WHEN $2 = -1 /* default typmod */ + THEN null + WHEN $1 IN (1042, 1043) /* char, varchar */ + THEN $2 - 4 + WHEN $1 IN (1560, 1562) /* bit, varbit */ + THEN $2 + ELSE null + END$$; + +CREATE FUNCTION _pg_char_octet_length(typid oid, typmod int4) RETURNS integer + LANGUAGE sql + IMMUTABLE + RETURNS NULL ON NULL INPUT + AS +$$SELECT + CASE WHEN $1 IN (25, 1042, 1043) /* text, char, varchar */ + THEN CASE WHEN $2 = -1 /* default typmod */ + THEN CAST(2^30 AS integer) + ELSE information_schema._pg_char_max_length($1, $2) * + pg_catalog.pg_encoding_max_length((SELECT encoding FROM pg_catalog.pg_database WHERE datname = pg_catalog.current_database())) + END + ELSE null + END$$; + +CREATE FUNCTION _pg_numeric_precision(typid oid, typmod int4) RETURNS integer + LANGUAGE sql + IMMUTABLE + RETURNS NULL ON NULL INPUT + AS +$$SELECT + CASE $1 + WHEN 21 /*int2*/ THEN 16 + WHEN 23 /*int4*/ THEN 32 + WHEN 20 /*int8*/ THEN 64 + WHEN 1700 /*numeric*/ THEN + CASE WHEN $2 = -1 + THEN null + ELSE (($2 - 4) >> 16) & 65535 + END + WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/ + WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/ + ELSE null + END$$; + +CREATE FUNCTION _pg_numeric_precision_radix(typid oid, typmod int4) RETURNS integer + LANGUAGE sql + IMMUTABLE + RETURNS NULL ON NULL INPUT + AS +$$SELECT + CASE WHEN $1 IN (21, 23, 20, 700, 701) THEN 2 + WHEN $1 IN (1700) THEN 10 + ELSE null + END$$; + +CREATE FUNCTION _pg_numeric_scale(typid oid, typmod int4) RETURNS integer + LANGUAGE sql + IMMUTABLE + RETURNS NULL ON NULL INPUT + AS +$$SELECT + CASE WHEN $1 IN (21, 23, 20) THEN 0 + WHEN $1 IN (1700) THEN + CASE WHEN $2 = -1 + THEN null + ELSE ($2 - 4) & 65535 + END + ELSE null + END$$; + +CREATE FUNCTION _pg_datetime_precision(typid oid, typmod int4) RETURNS integer + LANGUAGE sql + IMMUTABLE + RETURNS NULL ON NULL INPUT + AS +$$SELECT + CASE WHEN $1 IN (1082) /* date */ + THEN 0 + WHEN $1 IN (1083, 1114, 1184, 1266) /* time, timestamp, same + tz */ + THEN CASE WHEN $2 < 0 THEN 6 ELSE $2 END + WHEN $1 IN (1186) /* interval */ + THEN CASE WHEN $2 < 0 OR $2 & 65535 = 65535 THEN 6 ELSE $2 & 65535 END + ELSE null + END$$; + +CREATE FUNCTION _pg_interval_type(typid oid, mod int4) RETURNS text + LANGUAGE sql + IMMUTABLE + RETURNS NULL ON NULL INPUT + AS +$$SELECT + CASE WHEN $1 IN (1186) /* interval */ + THEN upper(substring(format_type($1, $2) from 'interval[()0-9]* #"%#"' for '#')) + ELSE null + END$$; --- 20.3 INFORMATION_SCHEMA_CATALOG_NAME view appears later. +-- 5.2 INFORMATION_SCHEMA_CATALOG_NAME view appears later. /* - * 20.4 + * 5.3 * CARDINAL_NUMBER domain */ @@ -40,7 +195,7 @@ CREATE DOMAIN cardinal_number AS integer /* - * 20.5 + * 5.4 * CHARACTER_DATA domain */ @@ -48,7 +203,7 @@ CREATE DOMAIN character_data AS character varying; /* - * 20.6 + * 5.5 * SQL_IDENTIFIER domain */ @@ -56,7 +211,7 @@ CREATE DOMAIN sql_identifier AS character varying; /* - * 20.3 + * 5.2 * INFORMATION_SCHEMA_CATALOG_NAME view */ @@ -67,34 +222,207 @@ GRANT SELECT ON information_schema_catalog_name TO PUBLIC; /* - * 20.7 + * 5.6 * TIME_STAMP domain */ -CREATE DOMAIN time_stamp AS timestamp(2) +CREATE DOMAIN time_stamp AS timestamp(2) with time zone DEFAULT current_timestamp(2); +/* + * 5.7 + * YES_OR_NO domain + */ + +CREATE DOMAIN yes_or_no AS character varying(3) + CONSTRAINT yes_or_no_check CHECK (value IN ('YES', 'NO')); + + +-- 5.8 ADMINISTRABLE_ROLE_AUTHORIZATIONS view appears later. + /* - * 20.9 + * 5.9 * APPLICABLE_ROLES view */ CREATE VIEW applicable_roles AS - SELECT CAST(current_user AS sql_identifier) AS grantee, - CAST(g.groname AS sql_identifier) AS role_name, - CAST('NO' AS character_data) AS is_grantable + SELECT CAST(a.rolname AS sql_identifier) AS grantee, + CAST(b.rolname AS sql_identifier) AS role_name, + CAST(CASE WHEN m.admin_option THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable + FROM pg_auth_members m + JOIN pg_authid a ON (m.member = a.oid) + JOIN pg_authid b ON (m.roleid = b.oid) + WHERE pg_has_role(a.oid, 'USAGE'); - FROM pg_group g, pg_user u +GRANT SELECT ON applicable_roles TO PUBLIC; - WHERE u.usesysid = ANY (g.grolist) - AND u.usename = current_user; -GRANT SELECT ON applicable_roles TO PUBLIC; +/* + * 5.8 + * ADMINISTRABLE_ROLE_AUTHORIZATIONS view + */ + +CREATE VIEW administrable_role_authorizations AS + SELECT * + FROM applicable_roles + WHERE is_grantable = 'YES'; + +GRANT SELECT ON administrable_role_authorizations TO PUBLIC; + + +/* + * 5.10 + * ASSERTIONS view + */ + +-- feature not supported + + +/* + * 5.11 + * ATTRIBUTES view + */ + +CREATE VIEW attributes AS + SELECT CAST(current_database() AS sql_identifier) AS udt_catalog, + CAST(nc.nspname AS sql_identifier) AS udt_schema, + CAST(c.relname AS sql_identifier) AS udt_name, + CAST(a.attname AS sql_identifier) AS attribute_name, + CAST(a.attnum AS cardinal_number) AS ordinal_position, + CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS attribute_default, + CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END + AS yes_or_no) + AS is_nullable, -- This column was apparently removed between SQL:2003 and SQL:2008. + + CAST( + CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY' + WHEN nt.nspname = 'pg_catalog' THEN format_type(a.atttypid, null) + ELSE 'USER-DEFINED' END + AS character_data) + AS data_type, + + CAST( + _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t)) + AS cardinal_number) + AS character_maximum_length, + + CAST( + _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t)) + AS cardinal_number) + AS character_octet_length, + + CAST(null AS sql_identifier) AS character_set_catalog, + CAST(null AS sql_identifier) AS character_set_schema, + CAST(null AS sql_identifier) AS character_set_name, + + CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog, + CAST(nco.nspname AS sql_identifier) AS collation_schema, + CAST(co.collname AS sql_identifier) AS collation_name, + + CAST( + _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t)) + AS cardinal_number) + AS numeric_precision, + + CAST( + _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t)) + AS cardinal_number) + AS numeric_precision_radix, + + CAST( + _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t)) + AS cardinal_number) + AS numeric_scale, + + CAST( + _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t)) + AS cardinal_number) + AS datetime_precision, + + CAST( + _pg_interval_type(_pg_truetypid(a, t), _pg_truetypmod(a, t)) + AS character_data) + AS interval_type, + CAST(null AS cardinal_number) AS interval_precision, + + CAST(current_database() AS sql_identifier) AS attribute_udt_catalog, + CAST(nt.nspname AS sql_identifier) AS attribute_udt_schema, + CAST(t.typname AS sql_identifier) AS attribute_udt_name, + + CAST(null AS sql_identifier) AS scope_catalog, + CAST(null AS sql_identifier) AS scope_schema, + CAST(null AS sql_identifier) AS scope_name, + + CAST(null AS cardinal_number) AS maximum_cardinality, + CAST(a.attnum AS sql_identifier) AS dtd_identifier, + CAST('NO' AS yes_or_no) AS is_derived_reference_attribute + + FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum) + JOIN (pg_class c JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON a.attrelid = c.oid + JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON a.atttypid = t.oid + LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid)) + ON a.attcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default') + + WHERE a.attnum > 0 AND NOT a.attisdropped + AND c.relkind in ('c') + AND (pg_has_role(c.relowner, 'USAGE') + OR has_type_privilege(c.reltype, 'USAGE')); + +GRANT SELECT ON attributes TO PUBLIC; + + +/* + * 5.12 + * CHARACTER_SETS view + */ + +CREATE VIEW character_sets AS + SELECT CAST(null AS sql_identifier) AS character_set_catalog, + CAST(null AS sql_identifier) AS character_set_schema, + CAST(getdatabaseencoding() AS sql_identifier) AS character_set_name, + CAST(CASE WHEN getdatabaseencoding() = 'UTF8' THEN 'UCS' ELSE getdatabaseencoding() END AS sql_identifier) AS character_repertoire, + CAST(getdatabaseencoding() AS sql_identifier) AS form_of_use, + CAST(current_database() AS sql_identifier) AS default_collate_catalog, + CAST(nc.nspname AS sql_identifier) AS default_collate_schema, + CAST(c.collname AS sql_identifier) AS default_collate_name + FROM pg_database d + LEFT JOIN (pg_collation c JOIN pg_namespace nc ON (c.collnamespace = nc.oid)) + ON (datcollate = collcollate AND datctype = collctype) + WHERE d.datname = current_database() + ORDER BY char_length(c.collname) DESC, c.collname ASC -- prefer full/canonical name + LIMIT 1; + +GRANT SELECT ON character_sets TO PUBLIC; + + +/* + * 5.13 + * CHECK_CONSTRAINT_ROUTINE_USAGE view + */ + +CREATE VIEW check_constraint_routine_usage AS + SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog, + CAST(nc.nspname AS sql_identifier) AS constraint_schema, + CAST(c.conname AS sql_identifier) AS constraint_name, + CAST(current_database() AS sql_identifier) AS specific_catalog, + CAST(np.nspname AS sql_identifier) AS specific_schema, + CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name + FROM pg_namespace nc, pg_constraint c, pg_depend d, pg_proc p, pg_namespace np + WHERE nc.oid = c.connamespace + AND c.contype = 'c' + AND c.oid = d.objid + AND d.classid = 'pg_catalog.pg_constraint'::regclass + AND d.refobjid = p.oid + AND d.refclassid = 'pg_catalog.pg_proc'::regclass + AND p.pronamespace = np.oid + AND pg_has_role(p.proowner, 'USAGE'); + +GRANT SELECT ON check_constraint_routine_usage TO PUBLIC; /* - * 20.13 + * 5.14 * CHECK_CONSTRAINTS view */ @@ -104,22 +432,79 @@ CREATE VIEW check_constraints AS CAST(con.conname AS sql_identifier) AS constraint_name, CAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data) AS check_clause - FROM pg_namespace rs, - pg_constraint con + FROM pg_constraint con + LEFT OUTER JOIN pg_namespace rs ON (rs.oid = con.connamespace) LEFT OUTER JOIN pg_class c ON (c.oid = con.conrelid) - LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid), - pg_user u - WHERE rs.oid = con.connamespace - AND u.usesysid = coalesce(c.relowner, t.typowner) - AND u.usename = current_user - AND con.contype = 'c' - AND c.relkind = 'r'; + LEFT OUTER JOIN pg_type t ON (t.oid = con.contypid) + WHERE pg_has_role(coalesce(c.relowner, t.typowner), 'USAGE') + AND con.contype = 'c' + + UNION + -- not-null constraints + + SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog, + CAST(n.nspname AS sql_identifier) AS constraint_schema, + CAST(CAST(n.oid AS text) || '_' || CAST(r.oid AS text) || '_' || CAST(a.attnum AS text) || '_not_null' AS sql_identifier) AS constraint_name, -- XXX + CAST(a.attname || ' IS NOT NULL' AS character_data) + AS check_clause + FROM pg_namespace n, pg_class r, pg_attribute a + WHERE n.oid = r.relnamespace + AND r.oid = a.attrelid + AND a.attnum > 0 + AND NOT a.attisdropped + AND a.attnotnull + AND r.relkind = 'r' + AND pg_has_role(r.relowner, 'USAGE'); GRANT SELECT ON check_constraints TO PUBLIC; /* - * 20.15 + * 5.15 + * COLLATIONS view + */ + +CREATE VIEW collations AS + SELECT CAST(current_database() AS sql_identifier) AS collation_catalog, + CAST(nc.nspname AS sql_identifier) AS collation_schema, + CAST(c.collname AS sql_identifier) AS collation_name, + CAST('NO PAD' AS character_data) AS pad_attribute + FROM pg_collation c, pg_namespace nc + WHERE c.collnamespace = nc.oid + AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database())); + +GRANT SELECT ON collations TO PUBLIC; + + +/* + * 5.16 + * COLLATION_CHARACTER_SET_APPLICABILITY view + */ + +CREATE VIEW collation_character_set_applicability AS + SELECT CAST(current_database() AS sql_identifier) AS collation_catalog, + CAST(nc.nspname AS sql_identifier) AS collation_schema, + CAST(c.collname AS sql_identifier) AS collation_name, + CAST(null AS sql_identifier) AS character_set_catalog, + CAST(null AS sql_identifier) AS character_set_schema, + CAST(getdatabaseencoding() AS sql_identifier) AS character_set_name + FROM pg_collation c, pg_namespace nc + WHERE c.collnamespace = nc.oid + AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database())); + +GRANT SELECT ON collation_character_set_applicability TO PUBLIC; + + +/* + * 5.17 + * COLUMN_COLUMN_USAGE view + */ + +-- feature not supported + + +/* + * 5.18 * COLUMN_DOMAIN_USAGE view */ @@ -133,72 +518,97 @@ CREATE VIEW column_domain_usage AS CAST(a.attname AS sql_identifier) AS column_name FROM pg_type t, pg_namespace nt, pg_class c, pg_namespace nc, - pg_attribute a, pg_user u + pg_attribute a WHERE t.typnamespace = nt.oid AND c.relnamespace = nc.oid AND a.attrelid = c.oid AND a.atttypid = t.oid - AND t.typowner = u.usesysid AND t.typtype = 'd' - AND c.relkind IN ('r', 'v') + AND c.relkind IN ('r', 'v', 'f') AND a.attnum > 0 AND NOT a.attisdropped - AND u.usename = current_user; + AND pg_has_role(t.typowner, 'USAGE'); GRANT SELECT ON column_domain_usage TO PUBLIC; /* - * 20.16 + * 5.19 * COLUMN_PRIVILEGES */ CREATE VIEW column_privileges AS - SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor, - CAST(grantee.name AS sql_identifier) AS grantee, + 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(a.attname AS sql_identifier) AS column_name, - CAST(pr.type AS character_data) AS privilege_type, + CAST(x.relname AS sql_identifier) AS table_name, + CAST(x.attname AS sql_identifier) AS column_name, + CAST(x.prtype AS character_data) AS privilege_type, CAST( - CASE WHEN aclcontains(c.relacl, - makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, true)) - THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable + CASE WHEN + -- object owner always has grant options + pg_has_role(x.grantee, x.relowner, 'USAGE') + OR x.grantable + THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable - FROM pg_attribute a, - pg_class c, + FROM ( + SELECT pr_c.grantor, + pr_c.grantee, + attname, + relname, + relnamespace, + pr_c.prtype, + pr_c.grantable, + pr_c.relowner + FROM (SELECT oid, relname, relnamespace, relowner, (aclexplode(coalesce(relacl, acldefault('r', relowner)))).* + FROM pg_class + WHERE relkind IN ('r', 'v', 'f') + ) pr_c (oid, relname, relnamespace, relowner, grantor, grantee, prtype, grantable), + pg_attribute a + WHERE a.attrelid = pr_c.oid + AND a.attnum > 0 + AND NOT a.attisdropped + UNION + SELECT pr_a.grantor, + pr_a.grantee, + attname, + relname, + relnamespace, + pr_a.prtype, + pr_a.grantable, + c.relowner + FROM (SELECT attrelid, attname, (aclexplode(coalesce(attacl, acldefault('c', relowner)))).* + FROM pg_attribute a JOIN pg_class cc ON (a.attrelid = cc.oid) + WHERE attnum > 0 + AND NOT attisdropped + ) pr_a (attrelid, attname, grantor, grantee, prtype, grantable), + pg_class c + WHERE pr_a.attrelid = c.oid + AND relkind IN ('r', 'v', 'f') + ) x, pg_namespace nc, - pg_user u_grantor, + pg_authid u_grantor, ( - SELECT usesysid, 0, usename FROM pg_user - UNION ALL - SELECT 0, grosysid, groname FROM pg_group + SELECT oid, rolname FROM pg_authid UNION ALL - SELECT 0, 0, 'PUBLIC' - ) AS grantee (usesysid, grosysid, name), - (SELECT 'SELECT' UNION ALL - SELECT 'INSERT' UNION ALL - SELECT 'UPDATE' UNION ALL - SELECT 'REFERENCES') AS pr (type) + SELECT 0::oid, 'PUBLIC' + ) AS grantee (oid, rolname) - WHERE a.attrelid = c.oid - AND c.relnamespace = nc.oid - AND a.attnum > 0 - AND NOT a.attisdropped - AND c.relkind IN ('r', 'v') - AND aclcontains(c.relacl, - makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, false)) - AND (u_grantor.usename = current_user - OR grantee.name = current_user - OR grantee.name = 'PUBLIC'); + WHERE x.relnamespace = nc.oid + AND x.grantee = grantee.oid + AND x.grantor = u_grantor.oid + AND x.prtype IN ('INSERT', 'SELECT', 'UPDATE', 'REFERENCES') + AND (pg_has_role(u_grantor.oid, 'USAGE') + OR pg_has_role(grantee.oid, 'USAGE') + OR grantee.rolname = 'PUBLIC'); GRANT SELECT ON column_privileges TO PUBLIC; /* - * 20.17 + * 5.20 * COLUMN_UDT_USAGE view */ @@ -211,23 +621,22 @@ CREATE VIEW column_udt_usage AS CAST(c.relname AS sql_identifier) AS table_name, CAST(a.attname AS sql_identifier) AS column_name - FROM pg_attribute a, pg_class c, pg_namespace nc, pg_user u, + FROM pg_attribute a, pg_class c, pg_namespace nc, (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid)) ON (t.typtype = 'd' AND t.typbasetype = bt.oid) WHERE a.attrelid = c.oid AND a.atttypid = t.oid - AND u.usesysid = coalesce(bt.typowner, t.typowner) AND nc.oid = c.relnamespace - AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v') - AND u.usename = current_user; + AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v', 'f') + AND pg_has_role(coalesce(bt.typowner, t.typowner), 'USAGE'); GRANT SELECT ON column_udt_usage TO PUBLIC; /* - * 20.18 + * 5.21 * COLUMNS view */ @@ -237,12 +646,9 @@ CREATE VIEW columns AS CAST(c.relname AS sql_identifier) AS table_name, CAST(a.attname AS sql_identifier) AS column_name, CAST(a.attnum AS cardinal_number) AS ordinal_position, - CAST( - CASE WHEN u.usename = current_user THEN a.adsrc ELSE null END - AS character_data) - AS column_default, + CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS column_default, CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END - AS character_data) + AS yes_or_no) AS is_nullable, CAST( @@ -259,92 +665,48 @@ CREATE VIEW columns AS AS data_type, CAST( - CASE WHEN t.typtype = 'd' THEN - CASE WHEN t.typbasetype IN (25, 1042, 1043, 1560, 1562) AND t.typtypmod <> -1 - THEN t.typtypmod - 4 - ELSE null END - ELSE - CASE WHEN a.atttypid IN (25, 1042, 1043, 1560, 1562) AND a.atttypmod <> -1 - THEN a.atttypmod - 4 - ELSE null END - END + _pg_char_max_length(_pg_truetypid(a, t), _pg_truetypmod(a, t)) AS cardinal_number) AS character_maximum_length, CAST( - CASE WHEN t.typtype = 'd' THEN - CASE WHEN t.typbasetype IN (25, 1042, 1043) THEN 2^30 ELSE null END - ELSE - CASE WHEN a.atttypid IN (25, 1042, 1043) THEN 2^30 ELSE null END - END + _pg_char_octet_length(_pg_truetypid(a, t), _pg_truetypmod(a, t)) AS cardinal_number) AS character_octet_length, CAST( - CASE (CASE WHEN t.typtype = 'd' THEN t.typbasetype ELSE a.atttypid END) - WHEN 21 /*int2*/ THEN 16 - WHEN 23 /*int4*/ THEN 32 - WHEN 20 /*int8*/ THEN 64 - WHEN 1700 /*numeric*/ THEN ((CASE WHEN t.typtype = 'd' THEN t.typtypmod ELSE a.atttypmod END - 4) >> 16) & 65535 - WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/ - WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/ - ELSE null END + _pg_numeric_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t)) AS cardinal_number) AS numeric_precision, CAST( - CASE WHEN t.typtype = 'd' THEN - CASE WHEN t.typbasetype IN (21, 23, 20, 700, 701) THEN 2 - WHEN t.typbasetype IN (1700) THEN 10 - ELSE null END - ELSE - CASE WHEN a.atttypid IN (21, 23, 20, 700, 701) THEN 2 - WHEN a.atttypid IN (1700) THEN 10 - ELSE null END - END + _pg_numeric_precision_radix(_pg_truetypid(a, t), _pg_truetypmod(a, t)) AS cardinal_number) AS numeric_precision_radix, CAST( - CASE WHEN t.typtype = 'd' THEN - CASE WHEN t.typbasetype IN (21, 23, 20) THEN 0 - WHEN t.typbasetype IN (1700) THEN (t.typtypmod - 4) & 65535 - ELSE null END - ELSE - CASE WHEN a.atttypid IN (21, 23, 20) THEN 0 - WHEN a.atttypid IN (1700) THEN (a.atttypmod - 4) & 65535 - ELSE null END - END + _pg_numeric_scale(_pg_truetypid(a, t), _pg_truetypmod(a, t)) AS cardinal_number) AS numeric_scale, CAST( - CASE WHEN t.typtype = 'd' THEN - CASE WHEN t.typbasetype IN (1083, 1114, 1184, 1266) - THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod ELSE null END) - WHEN t.typbasetype IN (1186) - THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod & 65535 ELSE null END) - ELSE null END - ELSE - CASE WHEN a.atttypid IN (1083, 1114, 1184, 1266) - THEN (CASE WHEN a.atttypmod <> -1 THEN a.atttypmod ELSE null END) - WHEN a.atttypid IN (1186) - THEN (CASE WHEN a.atttypmod <> -1 THEN a.atttypmod & 65535 ELSE null END) - ELSE null END - END + _pg_datetime_precision(_pg_truetypid(a, t), _pg_truetypmod(a, t)) AS cardinal_number) AS datetime_precision, - CAST(null AS character_data) AS interval_type, -- XXX - CAST(null AS character_data) AS interval_precision, -- XXX + CAST( + _pg_interval_type(_pg_truetypid(a, t), _pg_truetypmod(a, t)) + AS character_data) + AS interval_type, + CAST(null AS cardinal_number) AS interval_precision, CAST(null AS sql_identifier) AS character_set_catalog, CAST(null AS sql_identifier) AS character_set_schema, CAST(null AS sql_identifier) AS character_set_name, - CAST(null AS sql_identifier) AS collation_catalog, - CAST(null AS sql_identifier) AS collation_schema, - CAST(null AS sql_identifier) AS collation_name, + CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog, + CAST(nco.nspname AS sql_identifier) AS collation_schema, + CAST(co.collname AS sql_identifier) AS collation_name, CAST(CASE WHEN t.typtype = 'd' THEN current_database() ELSE null END AS sql_identifier) AS domain_catalog, @@ -363,51 +725,48 @@ CREATE VIEW columns AS CAST(null AS cardinal_number) AS maximum_cardinality, CAST(a.attnum AS sql_identifier) AS dtd_identifier, - CAST('NO' AS character_data) AS is_self_referencing - - FROM (pg_attribute LEFT JOIN pg_attrdef ON attrelid = adrelid AND attnum = adnum) AS a, - pg_class c, pg_namespace nc, pg_user u, - (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) - LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid)) + CAST('NO' AS yes_or_no) AS is_self_referencing, + + CAST('NO' AS yes_or_no) AS is_identity, + CAST(null AS character_data) AS identity_generation, + CAST(null AS character_data) AS identity_start, + CAST(null AS character_data) AS identity_increment, + CAST(null AS character_data) AS identity_maximum, + CAST(null AS character_data) AS identity_minimum, + CAST(null AS yes_or_no) AS identity_cycle, + + CAST('NEVER' AS character_data) AS is_generated, + CAST(null AS character_data) AS generation_expression, + + CAST(CASE WHEN c.relkind = 'r' OR + (c.relkind IN ('v', 'f') AND + pg_column_is_updatable(c.oid, a.attnum, false)) + THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_updatable + + FROM (pg_attribute a LEFT JOIN pg_attrdef ad ON attrelid = adrelid AND attnum = adnum) + JOIN (pg_class c JOIN pg_namespace nc ON (c.relnamespace = nc.oid)) ON a.attrelid = c.oid + JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON a.atttypid = t.oid + LEFT JOIN (pg_type bt JOIN pg_namespace nbt ON (bt.typnamespace = nbt.oid)) ON (t.typtype = 'd' AND t.typbasetype = bt.oid) + LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid)) + ON a.attcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default') - WHERE a.attrelid = c.oid - AND a.atttypid = t.oid - AND u.usesysid = c.relowner - AND nc.oid = c.relnamespace + WHERE (NOT pg_is_other_temp_schema(nc.oid)) - AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v') + AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v', 'f') - AND (u.usename = current_user - OR has_table_privilege(c.oid, 'SELECT') - OR has_table_privilege(c.oid, 'INSERT') - OR has_table_privilege(c.oid, 'UPDATE') - OR has_table_privilege(c.oid, 'REFERENCES') ); + AND (pg_has_role(c.relowner, 'USAGE') + OR has_column_privilege(c.oid, a.attnum, + 'SELECT, INSERT, UPDATE, REFERENCES')); GRANT SELECT ON columns TO PUBLIC; /* - * 20.19 + * 5.22 * CONSTRAINT_COLUMN_USAGE view */ -/* This returns the integers from 1 to INDEX_MAX_KEYS/FUNC_MAX_ARGS */ -CREATE FUNCTION _pg_keypositions() RETURNS SETOF integer - LANGUAGE sql - IMMUTABLE - AS 'select 1 union all select 2 union all select 3 union all - select 4 union all select 5 union all select 6 union all - select 7 union all select 8 union all select 9 union all - select 10 union all select 11 union all select 12 union all - select 13 union all select 14 union all select 15 union all - select 16 union all select 17 union all select 18 union all - select 19 union all select 20 union all select 21 union all - select 22 union all select 23 union all select 24 union all - select 25 union all select 26 union all select 27 union all - select 28 union all select 29 union all select 30 union all - select 31 union all select 32'; - CREATE VIEW constraint_column_usage AS SELECT CAST(current_database() AS sql_identifier) AS table_catalog, CAST(tblschema AS sql_identifier) AS table_schema, @@ -431,7 +790,6 @@ CREATE VIEW constraint_column_usage AS AND c.connamespace = nc.oid AND c.contype = 'c' AND r.relkind = 'r' - AND a.attnum > 0 AND NOT a.attisdropped UNION ALL @@ -439,28 +797,33 @@ CREATE VIEW constraint_column_usage AS /* unique/primary key/foreign key constraints */ SELECT nr.nspname, r.relname, r.relowner, a.attname, nc.nspname, c.conname FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc, - pg_constraint c, _pg_keypositions() AS pos(n) + pg_constraint c WHERE nr.oid = r.relnamespace AND r.oid = a.attrelid - AND r.oid = c.conrelid AND nc.oid = c.connamespace - AND (CASE WHEN c.contype = 'f' THEN c.confkey[pos.n] = a.attnum - ELSE c.conkey[pos.n] = a.attnum END) - AND a.attnum > 0 + AND (CASE WHEN c.contype = 'f' THEN r.oid = c.confrelid AND a.attnum = ANY (c.confkey) + ELSE r.oid = c.conrelid AND a.attnum = ANY (c.conkey) END) AND NOT a.attisdropped AND c.contype IN ('p', 'u', 'f') AND r.relkind = 'r' - ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname), - pg_user u + ) AS x (tblschema, tblname, tblowner, colname, cstrschema, cstrname) - WHERE x.tblowner = u.usesysid AND u.usename = current_user; + WHERE pg_has_role(x.tblowner, 'USAGE'); GRANT SELECT ON constraint_column_usage TO PUBLIC; /* - * 20.20 + * 5.23 + * CONSTRAINT_PERIOD_USAGE view + */ + +-- feature not supported + + +/* + * 5.24 * CONSTRAINT_TABLE_USAGE view */ @@ -473,23 +836,38 @@ CREATE VIEW constraint_table_usage AS CAST(c.conname AS sql_identifier) AS constraint_name FROM pg_constraint c, pg_namespace nc, - pg_class r, pg_namespace nr, - pg_user u + pg_class r, pg_namespace nr WHERE c.connamespace = nc.oid AND r.relnamespace = nr.oid AND ( (c.contype = 'f' AND c.confrelid = r.oid) OR (c.contype IN ('p', 'u') AND c.conrelid = r.oid) ) AND r.relkind = 'r' - AND r.relowner = u.usesysid AND u.usename = current_user; + AND pg_has_role(r.relowner, 'USAGE'); GRANT SELECT ON constraint_table_usage TO PUBLIC; --- 20.21 DATA_TYPE_PRIVILEGES view appears later. +-- 5.25 DATA_TYPE_PRIVILEGES view appears later. + + +/* + * 5.26 + * DIRECT_SUPERTABLES view + */ + +-- feature not supported + + +/* + * 5.27 + * DIRECT_SUPERTYPES view + */ + +-- feature not supported /* - * 20.24 + * 5.28 * DOMAIN_CONSTRAINTS view */ @@ -501,22 +879,22 @@ CREATE VIEW domain_constraints AS CAST(n.nspname AS sql_identifier) AS domain_schema, CAST(t.typname AS sql_identifier) AS domain_name, CAST(CASE WHEN condeferrable THEN 'YES' ELSE 'NO' END - AS character_data) AS is_deferrable, + AS yes_or_no) AS is_deferrable, CAST(CASE WHEN condeferred THEN 'YES' ELSE 'NO' END - AS character_data) AS initially_deferred - FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t, pg_user u + AS yes_or_no) AS initially_deferred + FROM pg_namespace rs, pg_namespace n, pg_constraint con, pg_type t WHERE rs.oid = con.connamespace AND n.oid = t.typnamespace - AND u.usesysid = t.typowner - AND u.usename = current_user - AND t.oid = con.contypid; + AND t.oid = con.contypid + AND (pg_has_role(t.typowner, 'USAGE') + OR has_type_privilege(t.oid, 'USAGE')); GRANT SELECT ON domain_constraints TO PUBLIC; /* - * 20.25 * DOMAIN_UDT_USAGE view + * apparently removed in SQL:2003 */ CREATE VIEW domain_udt_usage AS @@ -528,21 +906,19 @@ CREATE VIEW domain_udt_usage AS CAST(t.typname AS sql_identifier) AS domain_name FROM pg_type t, pg_namespace nt, - pg_type bt, pg_namespace nbt, - pg_user u + pg_type bt, pg_namespace nbt WHERE t.typnamespace = nt.oid AND t.typbasetype = bt.oid AND bt.typnamespace = nbt.oid AND t.typtype = 'd' - AND bt.typowner = u.usesysid - AND u.usename = current_user; + AND pg_has_role(bt.typowner, 'USAGE'); GRANT SELECT ON domain_udt_usage TO PUBLIC; /* - * 20.26 + * 5.29 * DOMAINS view */ @@ -559,14 +935,12 @@ CREATE VIEW domains AS AS data_type, CAST( - CASE WHEN t.typbasetype IN (25, 1042, 1043, 1560, 1562) AND t.typtypmod <> -1 - THEN t.typtypmod - 4 - ELSE null END + _pg_char_max_length(t.typbasetype, t.typtypmod) AS cardinal_number) AS character_maximum_length, CAST( - CASE WHEN t.typbasetype IN (25, 1042, 1043) THEN 2^30 ELSE null END + _pg_char_octet_length(t.typbasetype, t.typtypmod) AS cardinal_number) AS character_octet_length, @@ -574,47 +948,35 @@ CREATE VIEW domains AS CAST(null AS sql_identifier) AS character_set_schema, CAST(null AS sql_identifier) AS character_set_name, - CAST(null AS sql_identifier) AS collation_catalog, - CAST(null AS sql_identifier) AS collation_schema, - CAST(null AS sql_identifier) AS collation_name, + CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog, + CAST(nco.nspname AS sql_identifier) AS collation_schema, + CAST(co.collname AS sql_identifier) AS collation_name, CAST( - CASE t.typbasetype - WHEN 21 /*int2*/ THEN 16 - WHEN 23 /*int4*/ THEN 32 - WHEN 20 /*int8*/ THEN 64 - WHEN 1700 /*numeric*/ THEN ((t.typtypmod - 4) >> 16) & 65535 - WHEN 700 /*float4*/ THEN 24 /*FLT_MANT_DIG*/ - WHEN 701 /*float8*/ THEN 53 /*DBL_MANT_DIG*/ - ELSE null END + _pg_numeric_precision(t.typbasetype, t.typtypmod) AS cardinal_number) AS numeric_precision, CAST( - CASE WHEN t.typbasetype IN (21, 23, 20, 700, 701) THEN 2 - WHEN t.typbasetype IN (1700) THEN 10 - ELSE null END + _pg_numeric_precision_radix(t.typbasetype, t.typtypmod) AS cardinal_number) AS numeric_precision_radix, CAST( - CASE WHEN t.typbasetype IN (21, 23, 20) THEN 0 - WHEN t.typbasetype IN (1700) THEN (t.typtypmod - 4) & 65535 - ELSE null END + _pg_numeric_scale(t.typbasetype, t.typtypmod) AS cardinal_number) AS numeric_scale, CAST( - CASE WHEN t.typbasetype IN (1083, 1114, 1184, 1266) - THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod ELSE null END) - WHEN t.typbasetype IN (1186) - THEN (CASE WHEN t.typtypmod <> -1 THEN t.typtypmod & 65535 ELSE null END) - ELSE null END + _pg_datetime_precision(t.typbasetype, t.typtypmod) AS cardinal_number) AS datetime_precision, - CAST(null AS character_data) AS interval_type, -- XXX - CAST(null AS character_data) AS interval_precision, -- XXX + CAST( + _pg_interval_type(t.typbasetype, t.typtypmod) + AS character_data) + AS interval_type, + CAST(null AS cardinal_number) AS interval_precision, CAST(t.typdefault AS character_data) AS domain_default, @@ -629,88 +991,138 @@ CREATE VIEW domains AS CAST(null AS cardinal_number) AS maximum_cardinality, CAST(1 AS sql_identifier) AS dtd_identifier - FROM pg_type t, pg_namespace nt, - pg_type bt, pg_namespace nbt + FROM (pg_type t JOIN pg_namespace nt ON t.typnamespace = nt.oid) + JOIN (pg_type bt JOIN pg_namespace nbt ON bt.typnamespace = nbt.oid) + ON (t.typbasetype = bt.oid AND t.typtype = 'd') + LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid)) + ON t.typcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default') - WHERE t.typnamespace = nt.oid - AND t.typbasetype = bt.oid - AND bt.typnamespace = nbt.oid - AND t.typtype = 'd'; + WHERE (pg_has_role(t.typowner, 'USAGE') + OR has_type_privilege(t.oid, 'USAGE')); GRANT SELECT ON domains TO PUBLIC; --- 20.27 ELEMENT_TYPES view appears later. +-- 5.30 ELEMENT_TYPES view appears later. /* - * 20.28 + * 5.31 * ENABLED_ROLES view */ CREATE VIEW enabled_roles AS - SELECT CAST(g.groname AS sql_identifier) AS role_name - FROM pg_group g, pg_user u - WHERE u.usesysid = ANY (g.grolist) - AND u.usename = current_user; + SELECT CAST(a.rolname AS sql_identifier) AS role_name + FROM pg_authid a + WHERE pg_has_role(a.oid, 'USAGE'); GRANT SELECT ON enabled_roles TO PUBLIC; /* - * 20.30 + * 5.32 + * FIELDS view + */ + +-- feature not supported + + +/* + * 5.33 * KEY_COLUMN_USAGE view */ CREATE 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(c.conname AS sql_identifier) AS constraint_name, + 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(r.relname AS sql_identifier) AS table_name, + 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(pos.n AS cardinal_number) AS ordinal_position - - FROM pg_namespace nr, pg_class r, pg_attribute a, pg_namespace nc, - pg_constraint c, pg_user u, _pg_keypositions() AS pos(n) - WHERE nr.oid = r.relnamespace - AND r.oid = a.attrelid - AND r.oid = c.conrelid - AND nc.oid = c.connamespace - AND c.conkey[pos.n] = a.attnum - AND a.attnum > 0 + CAST((ss.x).n AS cardinal_number) AS ordinal_position, + CAST(CASE WHEN contype = 'f' THEN + _pg_index_position(ss.conindid, 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, r.relowner, + nc.nspname AS nc_nspname, nr.nspname AS nr_nspname, + c.oid AS coid, c.conname, c.contype, c.conindid, + c.confkey, c.confrelid, + _pg_expandarray(c.conkey) AS x + FROM pg_namespace nr, pg_class r, pg_namespace nc, + pg_constraint c + 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)) ) AS ss + WHERE ss.roid = a.attrelid + AND a.attnum = (ss.x).x AND NOT a.attisdropped - AND c.contype IN ('p', 'u', 'f') - AND r.relkind = 'r' - AND r.relowner = u.usesysid - AND u.usename = current_user; + AND (pg_has_role(relowner, 'USAGE') + OR has_column_privilege(roid, a.attnum, + 'SELECT, INSERT, UPDATE, REFERENCES')); GRANT SELECT ON key_column_usage TO PUBLIC; /* - * 20.33 - * PARAMETERS view + * 5.34 + * KEY_PERIOD_USAGE view */ -CREATE VIEW parameters AS - SELECT CAST(current_database() AS sql_identifier) AS specific_catalog, - CAST(n.nspname AS sql_identifier) AS specific_schema, - CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name, - CAST(pos.n AS cardinal_number) AS ordinal_position, - CAST('IN' AS character_data) AS parameter_mode, - CAST('NO' AS character_data) AS is_result, - CAST('NO' AS character_data) AS as_locator, - CAST(null AS sql_identifier) AS parameter_name, - CAST( - CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY' - WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null) - ELSE 'USER-DEFINED' END AS character_data) - AS data_type, - CAST(null AS cardinal_number) AS character_maximum_length, - CAST(null AS cardinal_number) AS character_octet_length, - CAST(null AS sql_identifier) AS character_set_catalog, +-- feature not supported + + +/* + * 5.35 + * METHOD_SPECIFICATION_PARAMETERS view + */ + +-- feature not supported + + +/* + * 5.36 + * METHOD_SPECIFICATIONS view + */ + +-- feature not supported + + +/* + * 5.37 + * PARAMETERS view + */ + +CREATE VIEW parameters AS + SELECT CAST(current_database() AS sql_identifier) AS specific_catalog, + CAST(n_nspname AS sql_identifier) AS specific_schema, + CAST(proname || '_' || CAST(p_oid AS text) AS sql_identifier) AS specific_name, + CAST((ss.x).n AS cardinal_number) AS ordinal_position, + CAST( + CASE WHEN proargmodes IS NULL THEN 'IN' + WHEN proargmodes[(ss.x).n] = 'i' THEN 'IN' + WHEN proargmodes[(ss.x).n] = 'o' THEN 'OUT' + WHEN proargmodes[(ss.x).n] = 'b' THEN 'INOUT' + WHEN proargmodes[(ss.x).n] = 'v' THEN 'IN' + WHEN proargmodes[(ss.x).n] = 't' THEN 'OUT' + END AS character_data) AS parameter_mode, + CAST('NO' AS yes_or_no) AS is_result, + CAST('NO' AS yes_or_no) AS as_locator, + CAST(NULLIF(proargnames[(ss.x).n], '') AS sql_identifier) AS parameter_name, + CAST( + CASE WHEN t.typelem <> 0 AND t.typlen = -1 THEN 'ARRAY' + WHEN nt.nspname = 'pg_catalog' THEN format_type(t.oid, null) + ELSE 'USER-DEFINED' END AS character_data) + AS data_type, + CAST(null AS cardinal_number) AS character_maximum_length, + CAST(null AS cardinal_number) AS character_octet_length, + CAST(null AS sql_identifier) AS character_set_catalog, CAST(null AS sql_identifier) AS character_set_schema, CAST(null AS sql_identifier) AS character_set_name, CAST(null AS sql_identifier) AS collation_catalog, @@ -721,7 +1133,7 @@ CREATE VIEW parameters AS CAST(null AS cardinal_number) AS numeric_scale, CAST(null AS cardinal_number) AS datetime_precision, CAST(null AS character_data) AS interval_type, - CAST(null AS character_data) AS interval_precision, + CAST(null AS cardinal_number) AS interval_precision, CAST(current_database() AS sql_identifier) AS udt_catalog, CAST(nt.nspname AS sql_identifier) AS udt_schema, CAST(t.typname AS sql_identifier) AS udt_name, @@ -729,35 +1141,46 @@ CREATE VIEW parameters AS CAST(null AS sql_identifier) AS scope_schema, CAST(null AS sql_identifier) AS scope_name, CAST(null AS cardinal_number) AS maximum_cardinality, - CAST(pos.n AS sql_identifier) AS dtd_identifier - - FROM pg_namespace n, pg_proc p, pg_type t, pg_namespace nt, pg_user u, - _pg_keypositions() AS pos(n) + CAST((ss.x).n AS sql_identifier) AS dtd_identifier, + CAST( + CASE WHEN pg_has_role(proowner, 'USAGE') + THEN pg_get_function_arg_default(p_oid, (ss.x).n) + ELSE NULL END + AS character_data) AS parameter_default - WHERE n.oid = p.pronamespace AND p.pronargs >= pos.n - AND p.proargtypes[pos.n-1] = t.oid AND t.typnamespace = nt.oid - AND p.proowner = u.usesysid - AND (u.usename = current_user OR has_function_privilege(p.oid, 'EXECUTE')); + FROM pg_type t, pg_namespace nt, + (SELECT n.nspname AS n_nspname, p.proname, p.oid AS p_oid, p.proowner, + p.proargnames, p.proargmodes, + _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x + FROM pg_namespace n, pg_proc p + WHERE n.oid = p.pronamespace + AND (pg_has_role(p.proowner, 'USAGE') OR + has_function_privilege(p.oid, 'EXECUTE'))) AS ss + WHERE t.oid = (ss.x).x AND t.typnamespace = nt.oid; GRANT SELECT ON parameters TO PUBLIC; /* - * 20.35 - * REFERENTIAL_CONSTRAINTS view + * 5.38 + * PERIODS view */ -CREATE FUNCTION _pg_keyissubset(smallint[], smallint[]) RETURNS boolean - LANGUAGE sql - IMMUTABLE - RETURNS NULL ON NULL INPUT - AS 'select $1[1] is null or ($1[1] = any ($2) and coalesce(information_schema._pg_keyissubset($1[2:pg_catalog.array_upper($1,1)], $2), true))'; +-- feature not supported -CREATE FUNCTION _pg_keysequal(smallint[], smallint[]) RETURNS boolean - LANGUAGE sql - IMMUTABLE - RETURNS NULL ON NULL INPUT - AS 'select information_schema._pg_keyissubset($1, $2) and information_schema._pg_keyissubset($2, $1)'; + +/* + * 5.39 + * REFERENCED_TYPES view + */ + +-- feature not supported + + +/* + * 5.40 + * REFERENTIAL_CONSTRAINTS view + */ CREATE VIEW referential_constraints AS SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog, @@ -773,7 +1196,7 @@ CREATE VIEW referential_constraints AS CAST( CASE con.confmatchtype WHEN 'f' THEN 'FULL' WHEN 'p' THEN 'PARTIAL' - WHEN 'u' THEN 'NONE' END + WHEN 's' THEN 'NONE' END AS character_data) AS match_option, CAST( @@ -792,163 +1215,96 @@ CREATE VIEW referential_constraints AS WHEN 'a' THEN 'NO ACTION' END AS character_data) AS delete_rule - FROM (pg_namespace ncon INNER JOIN pg_constraint con ON ncon.oid = con.connamespace - INNER JOIN pg_class c ON con.conrelid = c.oid - INNER JOIN pg_user u ON c.relowner = u.usesysid) - LEFT JOIN - (pg_constraint pkc INNER JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid) - ON con.confrelid = pkc.conrelid AND _pg_keysequal(con.confkey, pkc.conkey) - - WHERE c.relkind = 'r' - AND con.contype = 'f' - AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL) - AND u.usename = current_user; + FROM (pg_namespace ncon + INNER JOIN pg_constraint con ON ncon.oid = con.connamespace + INNER JOIN pg_class c ON con.conrelid = c.oid AND con.contype = 'f') + LEFT JOIN pg_depend d1 -- find constraint's dependency on an index + ON d1.objid = con.oid AND d1.classid = 'pg_constraint'::regclass + AND d1.refclassid = 'pg_class'::regclass AND d1.refobjsubid = 0 + LEFT JOIN pg_depend d2 -- find pkey/unique constraint for that index + ON d2.refclassid = 'pg_constraint'::regclass + AND d2.classid = 'pg_class'::regclass + AND d2.objid = d1.refobjid AND d2.objsubid = 0 + AND d2.deptype = 'i' + LEFT JOIN pg_constraint pkc ON pkc.oid = d2.refobjid + AND pkc.contype IN ('p', 'u') + AND pkc.conrelid = con.confrelid + LEFT JOIN pg_namespace npkc ON pkc.connamespace = npkc.oid + + WHERE pg_has_role(c.relowner, 'USAGE') + -- SELECT privilege omitted, per SQL standard + OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') + OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') ; GRANT SELECT ON referential_constraints TO PUBLIC; /* - * 20.36 + * 5.41 * ROLE_COLUMN_GRANTS view */ CREATE VIEW role_column_grants AS - SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor, - CAST(g_grantee.groname 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(a.attname AS sql_identifier) AS column_name, - CAST(pr.type AS character_data) AS privilege_type, - CAST( - CASE WHEN aclcontains(c.relacl, - makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, true)) - THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable - - FROM pg_attribute a, - pg_class c, - pg_namespace nc, - pg_user u_grantor, - pg_group g_grantee, - (SELECT 'SELECT' UNION ALL - SELECT 'INSERT' UNION ALL - SELECT 'UPDATE' UNION ALL - SELECT 'REFERENCES') AS pr (type) - - WHERE a.attrelid = c.oid - AND c.relnamespace = nc.oid - AND a.attnum > 0 - AND NOT a.attisdropped - AND c.relkind IN ('r', 'v') - AND aclcontains(c.relacl, - makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, false)) - AND g_grantee.groname IN (SELECT role_name FROM enabled_roles); + SELECT grantor, + grantee, + table_catalog, + table_schema, + table_name, + column_name, + privilege_type, + is_grantable + FROM column_privileges + WHERE grantor IN (SELECT role_name FROM enabled_roles) + OR grantee IN (SELECT role_name FROM enabled_roles); GRANT SELECT ON role_column_grants TO PUBLIC; -/* - * 20.37 - * ROLE_ROUTINE_GRANTS view - */ +-- 5.42 ROLE_ROUTINE_GRANTS view is based on 5.49 ROUTINE_PRIVILEGES and is defined there instead. -CREATE VIEW role_routine_grants AS - SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor, - CAST(g_grantee.groname AS sql_identifier) AS grantee, - CAST(current_database() AS sql_identifier) AS specific_catalog, - CAST(n.nspname AS sql_identifier) AS specific_schema, - CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name, - CAST(current_database() AS sql_identifier) AS routine_catalog, - CAST(n.nspname AS sql_identifier) AS routine_schema, - CAST(p.proname AS sql_identifier) AS routine_name, - CAST('EXECUTE' AS character_data) AS privilege_type, - CAST( - CASE WHEN aclcontains(p.proacl, - makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, 'EXECUTE', true)) - THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable - FROM pg_proc p, - pg_namespace n, - pg_user u_grantor, - pg_group g_grantee - - WHERE p.pronamespace = n.oid - AND aclcontains(p.proacl, - makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, 'EXECUTE', false)) - AND g_grantee.groname IN (SELECT role_name FROM enabled_roles); - -GRANT SELECT ON role_routine_grants TO PUBLIC; +-- 5.43 ROLE_TABLE_GRANTS view is based on 5.62 TABLE_PRIVILEGES and is defined there instead. /* - * 20.38 - * ROLE_TABLE_GRANTS view + * 5.44 + * ROLE_TABLE_METHOD_GRANTS view */ -CREATE VIEW role_table_grants AS - SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor, - CAST(g_grantee.groname 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(pr.type AS character_data) AS privilege_type, - CAST( - CASE WHEN aclcontains(c.relacl, - makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, true)) - THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable, - CAST('NO' AS character_data) AS with_hierarchy +-- feature not supported - FROM pg_class c, - pg_namespace nc, - pg_user u_grantor, - pg_group g_grantee, - (SELECT 'SELECT' UNION ALL - SELECT 'DELETE' UNION ALL - SELECT 'INSERT' UNION ALL - SELECT 'UPDATE' UNION ALL - SELECT 'REFERENCES' UNION ALL - SELECT 'RULE' UNION ALL - SELECT 'TRIGGER') AS pr (type) - WHERE c.relnamespace = nc.oid - AND c.relkind IN ('r', 'v') - AND aclcontains(c.relacl, - makeaclitem(0, g_grantee.grosysid, u_grantor.usesysid, pr.type, false)) - AND g_grantee.groname IN (SELECT role_name FROM enabled_roles); -GRANT SELECT ON role_table_grants TO PUBLIC; +-- 5.45 ROLE_USAGE_GRANTS view is based on 5.74 USAGE_PRIVILEGES and is defined there instead. + + +-- 5.46 ROLE_UDT_GRANTS view is based on 5.73 UDT_PRIVILEGES and is defined there instead. /* - * 20.40 - * ROLE_USAGE_GRANTS view + * 5.47 + * ROUTINE_COLUMN_USAGE view */ --- See USAGE_PRIVILEGES. +-- not tracked by PostgreSQL -CREATE VIEW role_usage_grants AS - SELECT CAST(null AS sql_identifier) AS grantor, - CAST(null AS sql_identifier) AS grantee, - CAST(current_database() AS sql_identifier) AS object_catalog, - CAST(null AS sql_identifier) AS object_schema, - CAST(null AS sql_identifier) AS object_name, - CAST(null AS character_data) AS object_type, - CAST('USAGE' AS character_data) AS privilege_type, - CAST(null AS character_data) AS is_grantable - WHERE false; +/* + * 5.48 + * ROUTINE_PERIOD_USAGE view + */ -GRANT SELECT ON role_usage_grants TO PUBLIC; +-- feature not supported /* - * 20.43 + * 5.49 * ROUTINE_PRIVILEGES view */ CREATE VIEW routine_privileges AS - SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor, - CAST(grantee.name AS sql_identifier) AS grantee, + 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 specific_catalog, CAST(n.nspname AS sql_identifier) AS specific_schema, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name, @@ -957,33 +1313,83 @@ CREATE VIEW routine_privileges AS CAST(p.proname AS sql_identifier) AS routine_name, CAST('EXECUTE' AS character_data) AS privilege_type, CAST( - CASE WHEN aclcontains(p.proacl, - makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, 'EXECUTE', true)) - THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable + CASE WHEN + -- object owner always has grant options + pg_has_role(grantee.oid, p.proowner, 'USAGE') + OR p.grantable + THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable - FROM pg_proc p, + FROM ( + SELECT oid, proname, proowner, pronamespace, (aclexplode(coalesce(proacl, acldefault('f', proowner)))).* FROM pg_proc + ) p (oid, proname, proowner, pronamespace, grantor, grantee, prtype, grantable), pg_namespace n, - pg_user u_grantor, + pg_authid u_grantor, ( - SELECT usesysid, 0, usename FROM pg_user - UNION ALL - SELECT 0, grosysid, groname FROM pg_group + SELECT oid, rolname FROM pg_authid UNION ALL - SELECT 0, 0, 'PUBLIC' - ) AS grantee (usesysid, grosysid, name) + SELECT 0::oid, 'PUBLIC' + ) AS grantee (oid, rolname) WHERE p.pronamespace = n.oid - AND aclcontains(p.proacl, - makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, 'EXECUTE', false)) - AND (u_grantor.usename = current_user - OR grantee.name = current_user - OR grantee.name = 'PUBLIC'); + AND grantee.oid = p.grantee + AND u_grantor.oid = p.grantor + AND p.prtype IN ('EXECUTE') + AND (pg_has_role(u_grantor.oid, 'USAGE') + OR pg_has_role(grantee.oid, 'USAGE') + OR grantee.rolname = 'PUBLIC'); GRANT SELECT ON routine_privileges TO PUBLIC; /* - * 20.45 + * 5.42 + * ROLE_ROUTINE_GRANTS view + */ + +CREATE VIEW role_routine_grants AS + SELECT grantor, + grantee, + specific_catalog, + specific_schema, + specific_name, + routine_catalog, + routine_schema, + routine_name, + privilege_type, + is_grantable + FROM routine_privileges + WHERE grantor IN (SELECT role_name FROM enabled_roles) + OR grantee IN (SELECT role_name FROM enabled_roles); + +GRANT SELECT ON role_routine_grants TO PUBLIC; + + +/* + * 5.50 + * ROUTINE_ROUTINE_USAGE view + */ + +-- not tracked by PostgreSQL + + +/* + * 5.51 + * ROUTINE_SEQUENCE_USAGE view + */ + +-- not tracked by PostgreSQL + + +/* + * 5.52 + * ROUTINE_TABLE_USAGE view + */ + +-- not tracked by PostgreSQL + + +/* + * 5.53 * ROUTINES view */ @@ -1020,7 +1426,7 @@ CREATE VIEW routines AS CAST(null AS cardinal_number) AS numeric_scale, CAST(null AS cardinal_number) AS datetime_precision, CAST(null AS character_data) AS interval_type, - CAST(null AS character_data) AS interval_precision, + CAST(null AS cardinal_number) AS interval_precision, CAST(current_database() AS sql_identifier) AS type_udt_catalog, CAST(nt.nspname AS sql_identifier) AS type_udt_schema, CAST(t.typname AS sql_identifier) AS type_udt_name, @@ -1033,7 +1439,7 @@ CREATE VIEW routines AS CAST(CASE WHEN l.lanname = 'sql' THEN 'SQL' ELSE 'EXTERNAL' END AS character_data) AS routine_body, CAST( - CASE WHEN u.usename = current_user THEN p.prosrc ELSE null END + CASE WHEN pg_has_role(p.proowner, 'USAGE') THEN p.prosrc ELSE null END AS character_data) AS routine_definition, CAST( CASE WHEN l.lanname = 'c' THEN p.prosrc ELSE null END @@ -1041,51 +1447,111 @@ CREATE VIEW routines AS CAST(upper(l.lanname) AS character_data) AS external_language, CAST('GENERAL' AS character_data) AS parameter_style, - CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS character_data) AS is_deterministic, + CAST(CASE WHEN p.provolatile = 'i' THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_deterministic, CAST('MODIFIES' AS character_data) AS sql_data_access, - CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS character_data) AS is_null_call, + CAST(CASE WHEN p.proisstrict THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_null_call, CAST(null AS character_data) AS sql_path, - CAST('YES' AS character_data) AS schema_level_routine, + CAST('YES' AS yes_or_no) AS schema_level_routine, CAST(0 AS cardinal_number) AS max_dynamic_result_sets, - CAST(null AS character_data) AS is_user_defined_cast, - CAST(null AS character_data) AS is_implicitly_invocable, + CAST(null AS yes_or_no) AS is_user_defined_cast, + CAST(null AS yes_or_no) AS is_implicitly_invocable, CAST(CASE WHEN p.prosecdef THEN 'DEFINER' ELSE 'INVOKER' END AS character_data) AS security_type, CAST(null AS sql_identifier) AS to_sql_specific_catalog, CAST(null AS sql_identifier) AS to_sql_specific_schema, CAST(null AS sql_identifier) AS to_sql_specific_name, - CAST('NO' AS character_data) AS as_locator - - FROM pg_namespace n, pg_proc p, pg_language l, pg_user u, + CAST('NO' AS yes_or_no) AS as_locator, + CAST(null AS time_stamp) AS created, + CAST(null AS time_stamp) AS last_altered, + CAST(null AS yes_or_no) AS new_savepoint_level, + CAST('NO' AS yes_or_no) AS is_udt_dependent, + + CAST(null AS character_data) AS result_cast_from_data_type, + CAST(null AS yes_or_no) AS result_cast_as_locator, + CAST(null AS cardinal_number) AS result_cast_char_max_length, + CAST(null AS cardinal_number) AS result_cast_char_octet_length, + CAST(null AS sql_identifier) AS result_cast_char_set_catalog, + CAST(null AS sql_identifier) AS result_cast_char_set_schema, + CAST(null AS sql_identifier) AS result_cast_character_set_name, + CAST(null AS sql_identifier) AS result_cast_collation_catalog, + CAST(null AS sql_identifier) AS result_cast_collation_schema, + CAST(null AS sql_identifier) AS result_cast_collation_name, + CAST(null AS cardinal_number) AS result_cast_numeric_precision, + CAST(null AS cardinal_number) AS result_cast_numeric_precision_radix, + CAST(null AS cardinal_number) AS result_cast_numeric_scale, + CAST(null AS cardinal_number) AS result_cast_datetime_precision, + CAST(null AS character_data) AS result_cast_interval_type, + CAST(null AS cardinal_number) AS result_cast_interval_precision, + CAST(null AS sql_identifier) AS result_cast_type_udt_catalog, + CAST(null AS sql_identifier) AS result_cast_type_udt_schema, + CAST(null AS sql_identifier) AS result_cast_type_udt_name, + CAST(null AS sql_identifier) AS result_cast_scope_catalog, + CAST(null AS sql_identifier) AS result_cast_scope_schema, + CAST(null AS sql_identifier) AS result_cast_scope_name, + CAST(null AS cardinal_number) AS result_cast_maximum_cardinality, + CAST(null AS sql_identifier) AS result_cast_dtd_identifier + + FROM pg_namespace n, pg_proc p, pg_language l, pg_type t, pg_namespace nt - WHERE n.oid = p.pronamespace AND p.prolang = l.oid AND p.proowner = u.usesysid + WHERE n.oid = p.pronamespace AND p.prolang = l.oid AND p.prorettype = t.oid AND t.typnamespace = nt.oid - AND (u.usename = current_user OR has_function_privilege(p.oid, 'EXECUTE')); + AND (pg_has_role(p.proowner, 'USAGE') + OR has_function_privilege(p.oid, 'EXECUTE')); GRANT SELECT ON routines TO PUBLIC; /* - * 20.46 + * 5.54 * SCHEMATA view */ CREATE VIEW schemata AS SELECT CAST(current_database() AS sql_identifier) AS catalog_name, CAST(n.nspname AS sql_identifier) AS schema_name, - CAST(u.usename AS sql_identifier) AS schema_owner, + CAST(u.rolname AS sql_identifier) AS schema_owner, CAST(null AS sql_identifier) AS default_character_set_catalog, CAST(null AS sql_identifier) AS default_character_set_schema, CAST(null AS sql_identifier) AS default_character_set_name, CAST(null AS character_data) AS sql_path - FROM pg_namespace n, pg_user u - WHERE n.nspowner = u.usesysid AND u.usename = current_user; + FROM pg_namespace n, pg_authid u + WHERE n.nspowner = u.oid + AND (pg_has_role(n.nspowner, 'USAGE') + OR has_schema_privilege(n.oid, 'CREATE, USAGE')); GRANT SELECT ON schemata TO PUBLIC; /* - * 20.47 + * 5.55 + * SEQUENCES view + */ + +CREATE VIEW sequences AS + SELECT CAST(current_database() AS sql_identifier) AS sequence_catalog, + CAST(nc.nspname AS sql_identifier) AS sequence_schema, + CAST(c.relname AS sql_identifier) AS sequence_name, + CAST('bigint' AS character_data) AS data_type, + CAST(64 AS cardinal_number) AS numeric_precision, + CAST(2 AS cardinal_number) AS numeric_precision_radix, + CAST(0 AS cardinal_number) AS numeric_scale, + CAST(p.start_value AS character_data) AS start_value, + CAST(p.minimum_value AS character_data) AS minimum_value, + CAST(p.maximum_value AS character_data) AS maximum_value, + CAST(p.increment AS character_data) AS increment, + CAST(CASE WHEN p.cycle_option THEN 'YES' ELSE 'NO' END AS yes_or_no) AS cycle_option + FROM pg_namespace nc, pg_class c, LATERAL pg_sequence_parameters(c.oid) p + WHERE c.relnamespace = nc.oid + AND c.relkind = 'S' + AND (NOT pg_is_other_temp_schema(nc.oid)) + AND (pg_has_role(c.relowner, 'USAGE') + OR has_sequence_privilege(c.oid, 'SELECT, UPDATE, USAGE') ); + +GRANT SELECT ON sequences TO PUBLIC; + + +/* + * 5.56 * SQL_FEATURES table */ @@ -1094,7 +1560,7 @@ CREATE TABLE sql_features ( feature_name character_data, sub_feature_id character_data, sub_feature_name character_data, - is_supported character_data, + is_supported yes_or_no, is_verified_by character_data, comments character_data ) WITHOUT OIDS; @@ -1105,12 +1571,12 @@ GRANT SELECT ON sql_features TO PUBLIC; /* - * 20.48 + * 5.57 * SQL_IMPLEMENTATION_INFO table */ --- Note: Implementation information items are defined in ISO 9075-3:1999, --- clause 7.1. +-- Note: Implementation information items are defined in ISO/IEC 9075-3:2008, +-- clause 9.1. CREATE TABLE sql_implementation_info ( implementation_info_id character_data, @@ -1121,7 +1587,7 @@ CREATE TABLE sql_implementation_info ( ) WITHOUT OIDS; INSERT INTO sql_implementation_info VALUES ('10003', 'CATALOG NAME', NULL, 'Y', NULL); -INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, '', 'not supported'); +INSERT INTO sql_implementation_info VALUES ('10004', 'COLLATING SEQUENCE', NULL, (SELECT default_collate_name FROM character_sets), NULL); INSERT INTO sql_implementation_info VALUES ('23', 'CURSOR COMMIT BEHAVIOR', 1, NULL, 'close cursors and retain prepared statements'); INSERT INTO sql_implementation_info VALUES ('2', 'DATA SOURCE NAME', NULL, '', NULL); INSERT INTO sql_implementation_info VALUES ('17', 'DBMS NAME', NULL, (select trim(trailing ' ' from substring(version() from '^[^0-9]*'))), NULL); @@ -1137,8 +1603,8 @@ GRANT SELECT ON sql_implementation_info TO PUBLIC; /* - * 20.49 * SQL_LANGUAGES table + * apparently removed in SQL:2008 */ CREATE TABLE sql_languages ( @@ -1153,19 +1619,21 @@ CREATE TABLE sql_languages ( INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'DIRECT', NULL); INSERT INTO sql_languages VALUES ('ISO 9075', '1999', 'CORE', NULL, NULL, 'EMBEDDED', 'C'); +INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'DIRECT', NULL); +INSERT INTO sql_languages VALUES ('ISO 9075', '2003', 'CORE', NULL, NULL, 'EMBEDDED', 'C'); GRANT SELECT ON sql_languages TO PUBLIC; /* - * 20.50 * SQL_PACKAGES table + * removed in SQL:2011 */ CREATE TABLE sql_packages ( feature_id character_data, feature_name character_data, - is_supported character_data, + is_supported yes_or_no, is_verified_by character_data, comments character_data ) WITHOUT OIDS; @@ -1179,17 +1647,41 @@ INSERT INTO sql_packages VALUES ('PKG005', 'CLI', 'NO', NULL, 'ODBC is similar.' INSERT INTO sql_packages VALUES ('PKG006', 'Basic object support', 'NO', NULL, ''); INSERT INTO sql_packages VALUES ('PKG007', 'Enhanced object support', 'NO', NULL, ''); INSERT INTO sql_packages VALUES ('PKG008', 'Active database', 'NO', NULL, ''); -INSERT INTO sql_packages VALUES ('PKG009', 'SQL/MM support', 'NO', NULL, ''); +INSERT INTO sql_packages VALUES ('PKG010', 'OLAP', 'NO', NULL, 'NO'); GRANT SELECT ON sql_packages TO PUBLIC; /* - * 20.51 + * 5.58 + * SQL_PARTS table + */ + +CREATE TABLE sql_parts ( + feature_id character_data, + feature_name character_data, + is_supported yes_or_no, + is_verified_by character_data, + comments character_data +) WITHOUT OIDS; + +INSERT INTO sql_parts VALUES ('1', 'Framework (SQL/Framework)', 'NO', NULL, ''); +INSERT INTO sql_parts VALUES ('2', 'Foundation (SQL/Foundation)', 'NO', NULL, ''); +INSERT INTO sql_parts VALUES ('3', 'Call-Level Interface (SQL/CLI)', 'NO', NULL, ''); +INSERT INTO sql_parts VALUES ('4', 'Persistent Stored Modules (SQL/PSM)', 'NO', NULL, ''); +INSERT INTO sql_parts VALUES ('9', 'Management of External Data (SQL/MED)', 'NO', NULL, ''); +INSERT INTO sql_parts VALUES ('10', 'Object Language Bindings (SQL/OLB)', 'NO', NULL, ''); +INSERT INTO sql_parts VALUES ('11', 'Information and Definition Schema (SQL/Schemata)', 'NO', NULL, ''); +INSERT INTO sql_parts VALUES ('13', 'Routines and Types Using the Java Programming Language (SQL/JRT)', 'NO', NULL, ''); +INSERT INTO sql_parts VALUES ('14', 'XML-Related Specifications (SQL/XML)', 'YES', NULL, ''); + + +/* + * 5.59 * SQL_SIZING table */ --- Note: Sizing items are defined in ISO 9075-3:1999, clause 7.2. +-- Note: Sizing items are defined in ISO/IEC 9075-3:2008, clause 9.2. CREATE TABLE sql_sizing ( sizing_id cardinal_number, @@ -1231,8 +1723,8 @@ GRANT SELECT ON sql_sizing TO PUBLIC; /* - * 20.52 * SQL_SIZING_PROFILES table + * removed in SQL:2011 */ -- The data in this table are defined by various profiles of SQL. @@ -1251,7 +1743,7 @@ GRANT SELECT ON sql_sizing_profiles TO PUBLIC; /* - * 20.53 + * 5.60 * TABLE_CONSTRAINTS view */ @@ -1268,76 +1760,133 @@ CREATE VIEW table_constraints AS WHEN 'p' THEN 'PRIMARY KEY' WHEN 'u' THEN 'UNIQUE' END AS character_data) AS constraint_type, - CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS character_data) + CAST(CASE WHEN c.condeferrable THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_deferrable, - CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS character_data) + CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS yes_or_no) AS initially_deferred FROM pg_namespace nc, pg_namespace nr, pg_constraint c, - pg_class r, - pg_user u + pg_class r WHERE nc.oid = c.connamespace AND nr.oid = r.relnamespace - AND c.conrelid = r.oid AND r.relowner = u.usesysid + AND c.conrelid = r.oid + AND c.contype NOT IN ('t', 'x') -- ignore nonstandard constraints AND r.relkind = 'r' - AND u.usename = current_user; + AND (NOT pg_is_other_temp_schema(nr.oid)) + AND (pg_has_role(r.relowner, 'USAGE') + -- SELECT privilege omitted, per SQL standard + OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') + OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') ) + + UNION ALL + + -- not-null constraints --- FIMXE: Not-null constraints are missing here. + SELECT CAST(current_database() AS sql_identifier) AS constraint_catalog, + CAST(nr.nspname AS sql_identifier) AS constraint_schema, + CAST(CAST(nr.oid AS text) || '_' || CAST(r.oid AS text) || '_' || CAST(a.attnum AS text) || '_not_null' AS sql_identifier) AS constraint_name, -- XXX + CAST(current_database() AS sql_identifier) AS table_catalog, + CAST(nr.nspname AS sql_identifier) AS table_schema, + CAST(r.relname AS sql_identifier) AS table_name, + CAST('CHECK' AS character_data) AS constraint_type, + CAST('NO' AS yes_or_no) AS is_deferrable, + CAST('NO' AS yes_or_no) AS initially_deferred + + FROM pg_namespace nr, + pg_class r, + pg_attribute a + + WHERE nr.oid = r.relnamespace + AND r.oid = a.attrelid + AND a.attnotnull + AND a.attnum > 0 + AND NOT a.attisdropped + AND r.relkind = 'r' + AND (NOT pg_is_other_temp_schema(nr.oid)) + AND (pg_has_role(r.relowner, 'USAGE') + -- SELECT privilege omitted, per SQL standard + OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') + OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') ); GRANT SELECT ON table_constraints TO PUBLIC; /* - * 20.55 + * 5.61 + * TABLE_METHOD_PRIVILEGES view + */ + +-- feature not supported + + +/* + * 5.62 * TABLE_PRIVILEGES view */ CREATE VIEW table_privileges AS - SELECT CAST(u_grantor.usename AS sql_identifier) AS grantor, - CAST(grantee.name AS sql_identifier) AS grantee, + 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(pr.type AS character_data) AS privilege_type, + CAST(c.prtype AS character_data) AS privilege_type, CAST( - CASE WHEN aclcontains(c.relacl, - makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, true)) - THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable, - CAST('NO' AS character_data) AS with_hierarchy + 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 pg_class c, + 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_user u_grantor, + pg_authid u_grantor, ( - SELECT usesysid, 0, usename FROM pg_user - UNION ALL - SELECT 0, grosysid, groname FROM pg_group + SELECT oid, rolname FROM pg_authid UNION ALL - SELECT 0, 0, 'PUBLIC' - ) AS grantee (usesysid, grosysid, name), - (SELECT 'SELECT' UNION ALL - SELECT 'DELETE' UNION ALL - SELECT 'INSERT' UNION ALL - SELECT 'UPDATE' UNION ALL - SELECT 'REFERENCES' UNION ALL - SELECT 'RULE' UNION ALL - SELECT 'TRIGGER') AS pr (type) + SELECT 0::oid, 'PUBLIC' + ) AS grantee (oid, rolname) WHERE c.relnamespace = nc.oid AND c.relkind IN ('r', 'v') - AND aclcontains(c.relacl, - makeaclitem(grantee.usesysid, grantee.grosysid, u_grantor.usesysid, pr.type, false)) - AND (u_grantor.usename = current_user - OR grantee.name = current_user - OR grantee.name = 'PUBLIC'); + 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'); GRANT SELECT ON table_privileges TO PUBLIC; /* - * 20.56 + * 5.43 + * ROLE_TABLE_GRANTS view + */ + +CREATE VIEW role_table_grants AS + SELECT grantor, + grantee, + table_catalog, + table_schema, + table_name, + privilege_type, + is_grantable, + with_hierarchy + FROM table_privileges + WHERE grantor IN (SELECT role_name FROM enabled_roles) + OR grantee IN (SELECT role_name FROM enabled_roles); + +GRANT SELECT ON role_table_grants TO PUBLIC; + + +/* + * 5.63 * TABLES view */ @@ -1347,58 +1896,163 @@ CREATE VIEW tables AS CAST(c.relname AS sql_identifier) AS table_name, CAST( - CASE WHEN nc.nspname LIKE 'pg!_temp!_%' ESCAPE '!' THEN 'LOCAL TEMPORARY' + CASE WHEN nc.oid = pg_my_temp_schema() THEN 'LOCAL TEMPORARY' WHEN c.relkind = 'r' THEN 'BASE TABLE' WHEN c.relkind = 'v' THEN 'VIEW' + WHEN c.relkind = 'f' THEN 'FOREIGN TABLE' ELSE null END AS character_data) AS table_type, CAST(null AS sql_identifier) AS self_referencing_column_name, CAST(null AS character_data) AS reference_generation, - CAST(null AS sql_identifier) AS user_defined_type_catalog, - CAST(null AS sql_identifier) AS user_defined_type_schema, - CAST(null AS sql_identifier) AS user_defined_name + CAST(CASE WHEN t.typname IS NOT NULL THEN current_database() ELSE null END AS sql_identifier) AS user_defined_type_catalog, + CAST(nt.nspname AS sql_identifier) AS user_defined_type_schema, + CAST(t.typname AS sql_identifier) AS user_defined_type_name, - FROM pg_namespace nc, pg_class c, pg_user u + CAST(CASE WHEN c.relkind = 'r' OR + (c.relkind IN ('v', 'f') AND + -- 1 << CMD_INSERT + pg_relation_is_updatable(c.oid, false) & 8 = 8) + THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_insertable_into, - WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner - AND c.relkind IN ('r', 'v') - AND (u.usename = current_user - OR has_table_privilege(c.oid, 'SELECT') - OR has_table_privilege(c.oid, 'INSERT') - OR has_table_privilege(c.oid, 'UPDATE') - OR has_table_privilege(c.oid, 'DELETE') - OR has_table_privilege(c.oid, 'RULE') - OR has_table_privilege(c.oid, 'REFERENCES') - OR has_table_privilege(c.oid, 'TRIGGER') ); + CAST(CASE WHEN t.typname IS NOT NULL THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_typed, + CAST(null AS character_data) AS commit_action + + FROM pg_namespace nc JOIN pg_class c ON (nc.oid = c.relnamespace) + LEFT JOIN (pg_type t JOIN pg_namespace nt ON (t.typnamespace = nt.oid)) ON (c.reloftype = t.oid) + + WHERE c.relkind IN ('r', 'v', 'f') + AND (NOT pg_is_other_temp_schema(nc.oid)) + AND (pg_has_role(c.relowner, 'USAGE') + OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') + OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') ); GRANT SELECT ON tables TO PUBLIC; /* - * 20.59 - * TRIGGERED_UPDATE_COLUMNS view + * 5.64 + * TRANSFORMS view + */ + +CREATE VIEW transforms AS + SELECT CAST(current_database() AS sql_identifier) AS udt_catalog, + CAST(nt.nspname AS sql_identifier) AS udt_schema, + CAST(t.typname AS sql_identifier) AS udt_name, + CAST(current_database() AS sql_identifier) AS specific_catalog, + CAST(np.nspname AS sql_identifier) AS specific_schema, + CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name, + CAST(l.lanname AS sql_identifier) AS group_name, + CAST('FROM SQL' AS character_data) AS transform_type + FROM pg_type t JOIN pg_transform x ON t.oid = x.trftype + JOIN pg_language l ON x.trflang = l.oid + JOIN pg_proc p ON x.trffromsql = p.oid + JOIN pg_namespace nt ON t.typnamespace = nt.oid + JOIN pg_namespace np ON p.pronamespace = np.oid + + UNION + + SELECT CAST(current_database() AS sql_identifier) AS udt_catalog, + CAST(nt.nspname AS sql_identifier) AS udt_schema, + CAST(t.typname AS sql_identifier) AS udt_name, + CAST(current_database() AS sql_identifier) AS specific_catalog, + CAST(np.nspname AS sql_identifier) AS specific_schema, + CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name, + CAST(l.lanname AS sql_identifier) AS group_name, + CAST('TO SQL' AS character_data) AS transform_type + FROM pg_type t JOIN pg_transform x ON t.oid = x.trftype + JOIN pg_language l ON x.trflang = l.oid + JOIN pg_proc p ON x.trftosql = p.oid + JOIN pg_namespace nt ON t.typnamespace = nt.oid + JOIN pg_namespace np ON p.pronamespace = np.oid + + ORDER BY udt_catalog, udt_schema, udt_name, group_name, transform_type -- some sensible grouping for interactive use +; + + +/* + * 5.65 + * TRANSLATIONS view */ --- PostgreSQL doesn't allow the specification of individual triggered --- update columns, so this view is empty. +-- feature not supported + + +/* + * 5.66 + * TRIGGERED_UPDATE_COLUMNS view + */ CREATE VIEW triggered_update_columns AS SELECT CAST(current_database() AS sql_identifier) AS trigger_catalog, - CAST(null AS sql_identifier) AS trigger_schema, - CAST(null AS sql_identifier) AS trigger_name, + CAST(n.nspname AS sql_identifier) AS trigger_schema, + CAST(t.tgname AS sql_identifier) AS trigger_name, CAST(current_database() AS sql_identifier) AS event_object_catalog, - CAST(null AS sql_identifier) AS event_object_schema, - CAST(null AS sql_identifier) AS event_object_table, - CAST(null AS sql_identifier) AS event_object_column - WHERE false; + CAST(n.nspname AS sql_identifier) AS event_object_schema, + CAST(c.relname AS sql_identifier) AS event_object_table, + CAST(a.attname AS sql_identifier) AS event_object_column + + FROM pg_namespace n, pg_class c, pg_trigger t, + (SELECT tgoid, (ta0.tgat).x AS tgattnum, (ta0.tgat).n AS tgattpos + FROM (SELECT oid AS tgoid, information_schema._pg_expandarray(tgattr) AS tgat FROM pg_trigger) AS ta0) AS ta, + pg_attribute a + + WHERE n.oid = c.relnamespace + AND c.oid = t.tgrelid + AND t.oid = ta.tgoid + AND (a.attrelid, a.attnum) = (t.tgrelid, ta.tgattnum) + AND NOT t.tgisinternal + AND (NOT pg_is_other_temp_schema(n.oid)) + AND (pg_has_role(c.relowner, 'USAGE') + -- SELECT privilege omitted, per SQL standard + OR has_column_privilege(c.oid, a.attnum, 'INSERT, UPDATE, REFERENCES') ); GRANT SELECT ON triggered_update_columns TO PUBLIC; /* - * 20.62 + * 5.67 + * TRIGGER_COLUMN_USAGE view + */ + +-- not tracked by PostgreSQL + + +/* + * 5.68 + * TRIGGER_PERIOD_USAGE view + */ + +-- feature not supported + + +/* + * 5.69 + * TRIGGER_ROUTINE_USAGE view + */ + +-- not tracked by PostgreSQL + + +/* + * 5.70 + * TRIGGER_SEQUENCE_USAGE view + */ + +-- not tracked by PostgreSQL + + +/* + * 5.71 + * TRIGGER_TABLE_USAGE view + */ + +-- not tracked by PostgreSQL + + +/* + * 5.72 * TRIGGERS view */ @@ -1411,67 +2065,353 @@ CREATE VIEW triggers AS CAST(n.nspname AS sql_identifier) AS event_object_schema, CAST(c.relname AS sql_identifier) AS event_object_table, CAST(null AS cardinal_number) AS action_order, - CAST(null AS character_data) AS action_condition, + -- XXX strange hacks follow + CAST( + CASE WHEN pg_has_role(c.relowner, 'USAGE') + THEN (SELECT m[1] FROM regexp_matches(pg_get_triggerdef(t.oid), E'.{35,} WHEN \\((.+)\\) EXECUTE PROCEDURE') AS rm(m) LIMIT 1) + ELSE null END + AS character_data) AS action_condition, CAST( substring(pg_get_triggerdef(t.oid) from position('EXECUTE PROCEDURE' in substring(pg_get_triggerdef(t.oid) from 48)) + 47) AS character_data) AS action_statement, CAST( - CASE WHEN t.tgtype & 1 = 1 THEN 'ROW' ELSE 'STATEMENT' END + -- hard-wired reference to TRIGGER_TYPE_ROW + CASE t.tgtype & 1 WHEN 1 THEN 'ROW' ELSE 'STATEMENT' END AS character_data) AS action_orientation, CAST( - CASE WHEN t.tgtype & 2 = 2 THEN 'BEFORE' ELSE 'AFTER' END - AS character_data) AS condition_timing, - CAST(null AS sql_identifier) AS condition_reference_old_table, - CAST(null AS sql_identifier) AS condition_reference_new_table - - FROM pg_namespace n, pg_class c, pg_trigger t, pg_user u, - (SELECT 4, 'INSERT' UNION ALL - SELECT 8, 'DELETE' UNION ALL - SELECT 16, 'UPDATE') AS em (num, text) + -- hard-wired refs to TRIGGER_TYPE_BEFORE, TRIGGER_TYPE_INSTEAD + CASE t.tgtype & 66 WHEN 2 THEN 'BEFORE' WHEN 64 THEN 'INSTEAD OF' ELSE 'AFTER' END + AS character_data) AS action_timing, + CAST(null AS sql_identifier) AS action_reference_old_table, + CAST(null AS sql_identifier) AS action_reference_new_table, + CAST(null AS sql_identifier) AS action_reference_old_row, + CAST(null AS sql_identifier) AS action_reference_new_row, + CAST(null AS time_stamp) AS created + + FROM pg_namespace n, pg_class c, pg_trigger t, + -- hard-wired refs to TRIGGER_TYPE_INSERT, TRIGGER_TYPE_DELETE, + -- TRIGGER_TYPE_UPDATE; we intentionally omit TRIGGER_TYPE_TRUNCATE + (VALUES (4, 'INSERT'), + (8, 'DELETE'), + (16, 'UPDATE')) AS em (num, text) WHERE n.oid = c.relnamespace AND c.oid = t.tgrelid - AND c.relowner = u.usesysid AND t.tgtype & em.num <> 0 - AND NOT t.tgisconstraint - AND u.usename = current_user; + AND NOT t.tgisinternal + AND (NOT pg_is_other_temp_schema(n.oid)) + AND (pg_has_role(c.relowner, 'USAGE') + -- SELECT privilege omitted, per SQL standard + OR has_table_privilege(c.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') + OR has_any_column_privilege(c.oid, 'INSERT, UPDATE, REFERENCES') ); GRANT SELECT ON triggers TO PUBLIC; /* - * 20.63 - * USAGE_PRIVILEGES view + * 5.73 + * UDT_PRIVILEGES view */ --- Of the things currently implemented in PostgreSQL, usage privileges --- apply only to domains. Since domains have no real privileges, we --- represent all domains with implicit usage privilege here. +CREATE VIEW udt_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 udt_catalog, + CAST(n.nspname AS sql_identifier) AS udt_schema, + CAST(t.typname AS sql_identifier) AS udt_name, + CAST('TYPE USAGE' AS character_data) AS privilege_type, -- sic + CAST( + CASE WHEN + -- object owner always has grant options + pg_has_role(grantee.oid, t.typowner, 'USAGE') + OR t.grantable + THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable + + FROM ( + SELECT oid, typname, typnamespace, typtype, typowner, (aclexplode(coalesce(typacl, acldefault('T', typowner)))).* FROM pg_type + ) AS t (oid, typname, typnamespace, typtype, typowner, grantor, grantee, prtype, grantable), + pg_namespace n, + pg_authid u_grantor, + ( + SELECT oid, rolname FROM pg_authid + UNION ALL + SELECT 0::oid, 'PUBLIC' + ) AS grantee (oid, rolname) + + WHERE t.typnamespace = n.oid + AND t.typtype = 'c' + AND t.grantee = grantee.oid + AND t.grantor = u_grantor.oid + AND t.prtype IN ('USAGE') + AND (pg_has_role(u_grantor.oid, 'USAGE') + OR pg_has_role(grantee.oid, 'USAGE') + OR grantee.rolname = 'PUBLIC'); + +GRANT SELECT ON udt_privileges TO PUBLIC; + + +/* + * 5.46 + * ROLE_UDT_GRANTS view + */ + +CREATE VIEW role_udt_grants AS + SELECT grantor, + grantee, + udt_catalog, + udt_schema, + udt_name, + privilege_type, + is_grantable + FROM udt_privileges + WHERE grantor IN (SELECT role_name FROM enabled_roles) + OR grantee IN (SELECT role_name FROM enabled_roles); + +GRANT SELECT ON role_udt_grants TO PUBLIC; + + +/* + * 5.74 + * USAGE_PRIVILEGES view + */ CREATE VIEW usage_privileges AS - SELECT CAST(u.usename AS sql_identifier) AS grantor, + + /* collations */ + -- Collations have no real privileges, so we represent all collations with implicit usage privilege here. + SELECT CAST(u.rolname AS sql_identifier) AS grantor, CAST('PUBLIC' AS sql_identifier) AS grantee, CAST(current_database() AS sql_identifier) AS object_catalog, CAST(n.nspname AS sql_identifier) AS object_schema, + CAST(c.collname AS sql_identifier) AS object_name, + CAST('COLLATION' AS character_data) AS object_type, + CAST('USAGE' AS character_data) AS privilege_type, + CAST('NO' AS yes_or_no) AS is_grantable + + FROM pg_authid u, + pg_namespace n, + pg_collation c + + WHERE u.oid = c.collowner + AND c.collnamespace = n.oid + AND collencoding IN (-1, (SELECT encoding FROM pg_database WHERE datname = current_database())) + + UNION ALL + + /* domains */ + 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 object_catalog, + CAST(n.nspname AS sql_identifier) AS object_schema, CAST(t.typname AS sql_identifier) AS object_name, CAST('DOMAIN' AS character_data) AS object_type, CAST('USAGE' AS character_data) AS privilege_type, - CAST('NO' AS character_data) AS is_grantable + CAST( + CASE WHEN + -- object owner always has grant options + pg_has_role(grantee.oid, t.typowner, 'USAGE') + OR t.grantable + THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable - FROM pg_user u, + FROM ( + SELECT oid, typname, typnamespace, typtype, typowner, (aclexplode(coalesce(typacl, acldefault('T', typowner)))).* FROM pg_type + ) AS t (oid, typname, typnamespace, typtype, typowner, grantor, grantee, prtype, grantable), pg_namespace n, - pg_type t + pg_authid u_grantor, + ( + SELECT oid, rolname FROM pg_authid + UNION ALL + SELECT 0::oid, 'PUBLIC' + ) AS grantee (oid, rolname) + + WHERE t.typnamespace = n.oid + AND t.typtype = 'd' + AND t.grantee = grantee.oid + AND t.grantor = u_grantor.oid + AND t.prtype IN ('USAGE') + AND (pg_has_role(u_grantor.oid, 'USAGE') + OR pg_has_role(grantee.oid, 'USAGE') + OR grantee.rolname = 'PUBLIC') + + UNION ALL + + /* foreign-data wrappers */ + 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 object_catalog, + CAST('' AS sql_identifier) AS object_schema, + CAST(fdw.fdwname AS sql_identifier) AS object_name, + CAST('FOREIGN DATA WRAPPER' AS character_data) AS object_type, + CAST('USAGE' AS character_data) AS privilege_type, + CAST( + CASE WHEN + -- object owner always has grant options + pg_has_role(grantee.oid, fdw.fdwowner, 'USAGE') + OR fdw.grantable + THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable + + FROM ( + SELECT fdwname, fdwowner, (aclexplode(coalesce(fdwacl, acldefault('F', fdwowner)))).* FROM pg_foreign_data_wrapper + ) AS fdw (fdwname, fdwowner, grantor, grantee, prtype, grantable), + pg_authid u_grantor, + ( + SELECT oid, rolname FROM pg_authid + UNION ALL + SELECT 0::oid, 'PUBLIC' + ) AS grantee (oid, rolname) + + WHERE u_grantor.oid = fdw.grantor + AND grantee.oid = fdw.grantee + AND fdw.prtype IN ('USAGE') + AND (pg_has_role(u_grantor.oid, 'USAGE') + OR pg_has_role(grantee.oid, 'USAGE') + OR grantee.rolname = 'PUBLIC') + + UNION ALL + + /* foreign servers */ + 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 object_catalog, + CAST('' AS sql_identifier) AS object_schema, + CAST(srv.srvname AS sql_identifier) AS object_name, + CAST('FOREIGN SERVER' AS character_data) AS object_type, + CAST('USAGE' AS character_data) AS privilege_type, + CAST( + CASE WHEN + -- object owner always has grant options + pg_has_role(grantee.oid, srv.srvowner, 'USAGE') + OR srv.grantable + THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_grantable + + FROM ( + SELECT srvname, srvowner, (aclexplode(coalesce(srvacl, acldefault('S', srvowner)))).* FROM pg_foreign_server + ) AS srv (srvname, srvowner, grantor, grantee, prtype, grantable), + pg_authid u_grantor, + ( + SELECT oid, rolname FROM pg_authid + UNION ALL + SELECT 0::oid, 'PUBLIC' + ) AS grantee (oid, rolname) - WHERE u.usesysid = t.typowner - AND t.typnamespace = n.oid - AND t.typtype = 'd'; + WHERE u_grantor.oid = srv.grantor + AND grantee.oid = srv.grantee + AND srv.prtype IN ('USAGE') + AND (pg_has_role(u_grantor.oid, 'USAGE') + OR pg_has_role(grantee.oid, 'USAGE') + OR grantee.rolname = 'PUBLIC') + + UNION ALL + + /* sequences */ + 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 object_catalog, + CAST(n.nspname AS sql_identifier) AS object_schema, + CAST(c.relname AS sql_identifier) AS object_name, + CAST('SEQUENCE' AS character_data) AS object_type, + CAST('USAGE' 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 + + 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 n, + pg_authid u_grantor, + ( + SELECT oid, rolname FROM pg_authid + UNION ALL + SELECT 0::oid, 'PUBLIC' + ) AS grantee (oid, rolname) + + WHERE c.relnamespace = n.oid + AND c.relkind = 'S' + AND c.grantee = grantee.oid + AND c.grantor = u_grantor.oid + AND c.prtype IN ('USAGE') + AND (pg_has_role(u_grantor.oid, 'USAGE') + OR pg_has_role(grantee.oid, 'USAGE') + OR grantee.rolname = 'PUBLIC'); GRANT SELECT ON usage_privileges TO PUBLIC; /* - * 20.65 + * 5.45 + * ROLE_USAGE_GRANTS view + */ + +CREATE VIEW role_usage_grants AS + SELECT grantor, + grantee, + object_catalog, + object_schema, + object_name, + object_type, + privilege_type, + is_grantable + FROM usage_privileges + WHERE grantor IN (SELECT role_name FROM enabled_roles) + OR grantee IN (SELECT role_name FROM enabled_roles); + +GRANT SELECT ON role_usage_grants TO PUBLIC; + + +/* + * 5.75 + * USER_DEFINED_TYPES view + */ + +CREATE VIEW user_defined_types AS + SELECT CAST(current_database() AS sql_identifier) AS user_defined_type_catalog, + CAST(n.nspname AS sql_identifier) AS user_defined_type_schema, + CAST(c.relname AS sql_identifier) AS user_defined_type_name, + CAST('STRUCTURED' AS character_data) AS user_defined_type_category, + CAST('YES' AS yes_or_no) AS is_instantiable, + CAST(null AS yes_or_no) AS is_final, + CAST(null AS character_data) AS ordering_form, + CAST(null AS character_data) AS ordering_category, + CAST(null AS sql_identifier) AS ordering_routine_catalog, + CAST(null AS sql_identifier) AS ordering_routine_schema, + CAST(null AS sql_identifier) AS ordering_routine_name, + CAST(null AS character_data) AS reference_type, + CAST(null AS character_data) AS data_type, + CAST(null AS cardinal_number) AS character_maximum_length, + CAST(null AS cardinal_number) AS character_octet_length, + CAST(null AS sql_identifier) AS character_set_catalog, + CAST(null AS sql_identifier) AS character_set_schema, + CAST(null AS sql_identifier) AS character_set_name, + CAST(null AS sql_identifier) AS collation_catalog, + CAST(null AS sql_identifier) AS collation_schema, + CAST(null AS sql_identifier) AS collation_name, + CAST(null AS cardinal_number) AS numeric_precision, + CAST(null AS cardinal_number) AS numeric_precision_radix, + CAST(null AS cardinal_number) AS numeric_scale, + CAST(null AS cardinal_number) AS datetime_precision, + CAST(null AS character_data) AS interval_type, + CAST(null AS cardinal_number) AS interval_precision, + CAST(null AS sql_identifier) AS source_dtd_identifier, + CAST(null AS sql_identifier) AS ref_dtd_identifier + + FROM pg_namespace n, pg_class c, pg_type t + + WHERE n.oid = c.relnamespace + AND t.typrelid = c.oid + AND c.relkind = 'c' + AND (pg_has_role(t.typowner, 'USAGE') + OR has_type_privilege(t.oid, 'USAGE')); + +GRANT SELECT ON user_defined_types TO PUBLIC; + + +/* + * 5.76 * VIEW_COLUMN_USAGE */ @@ -1485,9 +2425,9 @@ CREATE VIEW view_column_usage AS CAST(t.relname AS sql_identifier) AS table_name, CAST(a.attname AS sql_identifier) AS column_name - FROM pg_user, pg_namespace nv, pg_class v, pg_depend dv, + FROM pg_namespace nv, pg_class v, pg_depend dv, pg_depend dt, pg_class t, pg_namespace nt, - pg_attribute a, pg_user u + pg_attribute a WHERE nv.oid = v.relnamespace AND v.relkind = 'v' @@ -1501,16 +2441,57 @@ CREATE VIEW view_column_usage AS AND dt.refclassid = 'pg_catalog.pg_class'::regclass AND dt.refobjid = t.oid AND t.relnamespace = nt.oid - AND t.relkind IN ('r', 'v') + AND t.relkind IN ('r', 'v', 'f') AND t.oid = a.attrelid AND dt.refobjsubid = a.attnum - AND t.relowner = u.usesysid AND u.usename = current_user; + AND pg_has_role(t.relowner, 'USAGE'); GRANT SELECT ON view_column_usage TO PUBLIC; /* - * 20.66 + * 5.77 + * VIEW_PERIOD_USAGE + */ + +-- feature not supported + + +/* + * 5.78 + * VIEW_ROUTINE_USAGE + */ + +CREATE VIEW view_routine_usage AS + SELECT DISTINCT + CAST(current_database() AS sql_identifier) AS table_catalog, + CAST(nv.nspname AS sql_identifier) AS table_schema, + CAST(v.relname AS sql_identifier) AS table_name, + CAST(current_database() AS sql_identifier) AS specific_catalog, + CAST(np.nspname AS sql_identifier) AS specific_schema, + CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier) AS specific_name + + FROM pg_namespace nv, pg_class v, pg_depend dv, + pg_depend dp, pg_proc p, pg_namespace np + + WHERE nv.oid = v.relnamespace + AND v.relkind = 'v' + AND v.oid = dv.refobjid + AND dv.refclassid = 'pg_catalog.pg_class'::regclass + AND dv.classid = 'pg_catalog.pg_rewrite'::regclass + AND dv.deptype = 'i' + AND dv.objid = dp.objid + AND dp.classid = 'pg_catalog.pg_rewrite'::regclass + AND dp.refclassid = 'pg_catalog.pg_proc'::regclass + AND dp.refobjid = p.oid + AND p.pronamespace = np.oid + AND pg_has_role(p.proowner, 'USAGE'); + +GRANT SELECT ON view_routine_usage TO PUBLIC; + + +/* + * 5.79 * VIEW_TABLE_USAGE */ @@ -1523,9 +2504,8 @@ CREATE VIEW view_table_usage AS CAST(nt.nspname AS sql_identifier) AS table_schema, CAST(t.relname AS sql_identifier) AS table_name - FROM pg_user, pg_namespace nv, pg_class v, pg_depend dv, - pg_depend dt, pg_class t, pg_namespace nt, - pg_user u + FROM pg_namespace nv, pg_class v, pg_depend dv, + pg_depend dt, pg_class t, pg_namespace nt WHERE nv.oid = v.relnamespace AND v.relkind = 'v' @@ -1539,14 +2519,14 @@ CREATE VIEW view_table_usage AS AND dt.refclassid = 'pg_catalog.pg_class'::regclass AND dt.refobjid = t.oid AND t.relnamespace = nt.oid - AND t.relkind IN ('r', 'v') - AND t.relowner = u.usesysid AND u.usename = current_user; + AND t.relkind IN ('r', 'v', 'f') + AND pg_has_role(t.relowner, 'USAGE'); GRANT SELECT ON view_table_usage TO PUBLIC; /* - * 20.68 + * 5.80 * VIEWS view */ @@ -1556,26 +2536,57 @@ CREATE VIEW views AS CAST(c.relname AS sql_identifier) AS table_name, CAST( - CASE WHEN u.usename = current_user THEN pg_get_viewdef(c.oid) + CASE WHEN pg_has_role(c.relowner, 'USAGE') + THEN pg_get_viewdef(c.oid) ELSE null END AS character_data) AS view_definition, - CAST('NONE' AS character_data) AS check_option, - CAST(null AS character_data) AS is_updatable, -- FIXME - CAST(null AS character_data) AS is_insertable_into -- FIXME + CAST( + CASE WHEN 'check_option=cascaded' = ANY (c.reloptions) + THEN 'CASCADED' + WHEN 'check_option=local' = ANY (c.reloptions) + THEN 'LOCAL' + ELSE 'NONE' END + AS character_data) AS check_option, + + CAST( + -- (1 << CMD_UPDATE) + (1 << CMD_DELETE) + CASE WHEN pg_relation_is_updatable(c.oid, false) & 20 = 20 + THEN 'YES' ELSE 'NO' END + AS yes_or_no) AS is_updatable, + + CAST( + -- 1 << CMD_INSERT + CASE WHEN pg_relation_is_updatable(c.oid, false) & 8 = 8 + THEN 'YES' ELSE 'NO' END + AS yes_or_no) AS is_insertable_into, - FROM pg_namespace nc, pg_class c, pg_user u + CAST( + -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_UPDATE + CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 81 = 81) + THEN 'YES' ELSE 'NO' END + AS yes_or_no) AS is_trigger_updatable, + + CAST( + -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_DELETE + CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 73 = 73) + THEN 'YES' ELSE 'NO' END + AS yes_or_no) AS is_trigger_deletable, - WHERE c.relnamespace = nc.oid AND u.usesysid = c.relowner + CAST( + -- TRIGGER_TYPE_ROW + TRIGGER_TYPE_INSTEAD + TRIGGER_TYPE_INSERT + CASE WHEN EXISTS (SELECT 1 FROM pg_trigger WHERE tgrelid = c.oid AND tgtype & 69 = 69) + THEN 'YES' ELSE 'NO' END + AS yes_or_no) AS is_trigger_insertable_into + + FROM pg_namespace nc, pg_class c + + WHERE c.relnamespace = nc.oid AND c.relkind = 'v' - AND (u.usename = current_user - OR has_table_privilege(c.oid, 'SELECT') - OR has_table_privilege(c.oid, 'INSERT') - OR has_table_privilege(c.oid, 'UPDATE') - OR has_table_privilege(c.oid, 'DELETE') - OR has_table_privilege(c.oid, 'RULE') - OR has_table_privilege(c.oid, 'REFERENCES') - OR has_table_privilege(c.oid, 'TRIGGER') ); + AND (NOT pg_is_other_temp_schema(nc.oid)) + AND (pg_has_role(c.relowner, 'USAGE') + OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') + OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES') ); GRANT SELECT ON views TO PUBLIC; @@ -1583,7 +2594,7 @@ GRANT SELECT ON views TO PUBLIC; -- The following views have dependencies that force them to appear out of order. /* - * 20.21 + * 5.25 * DATA_TYPE_PRIVILEGES view */ @@ -1596,6 +2607,8 @@ CREATE VIEW data_type_privileges AS FROM ( + SELECT udt_schema, udt_name, 'USER-DEFINED TYPE'::text, dtd_identifier FROM attributes + UNION ALL SELECT table_schema, table_name, 'TABLE'::text, dtd_identifier FROM columns UNION ALL SELECT domain_schema, domain_name, 'DOMAIN'::text, dtd_identifier FROM domains @@ -1609,7 +2622,7 @@ GRANT SELECT ON data_type_privileges TO PUBLIC; /* - * 20.27 + * 5.30 * ELEMENT_TYPES view */ @@ -1618,7 +2631,7 @@ CREATE VIEW element_types AS CAST(n.nspname AS sql_identifier) AS object_schema, CAST(x.objname AS sql_identifier) AS object_name, CAST(x.objtype AS character_data) AS object_type, - CAST(x.objdtdid AS sql_identifier) AS array_type_identifier, + CAST(x.objdtdid AS sql_identifier) AS collection_type_identifier, CAST( CASE WHEN nbt.nspname = 'pg_catalog' THEN format_type(bt.oid, null) ELSE 'USER-DEFINED' END AS character_data) AS data_type, @@ -1628,16 +2641,16 @@ CREATE VIEW element_types AS CAST(null AS sql_identifier) AS character_set_catalog, CAST(null AS sql_identifier) AS character_set_schema, CAST(null AS sql_identifier) AS character_set_name, - CAST(null AS sql_identifier) AS collation_catalog, - CAST(null AS sql_identifier) AS collation_schema, - CAST(null AS sql_identifier) AS collation_name, + CAST(CASE WHEN nco.nspname IS NOT NULL THEN current_database() END AS sql_identifier) AS collation_catalog, + CAST(nco.nspname AS sql_identifier) AS collation_schema, + CAST(co.collname AS sql_identifier) AS collation_name, CAST(null AS cardinal_number) AS numeric_precision, CAST(null AS cardinal_number) AS numeric_precision_radix, CAST(null AS cardinal_number) AS numeric_scale, CAST(null AS cardinal_number) AS datetime_precision, CAST(null AS character_data) AS interval_type, - CAST(null AS character_data) AS interval_precision, - + CAST(null AS cardinal_number) AS interval_precision, + CAST(null AS character_data) AS domain_default, -- XXX maybe a bug in the standard CAST(current_database() AS sql_identifier) AS udt_catalog, @@ -1649,42 +2662,46 @@ CREATE VIEW element_types AS CAST(null AS sql_identifier) AS scope_name, CAST(null AS cardinal_number) AS maximum_cardinality, - CAST('a' || x.objdtdid AS sql_identifier) AS dtd_identifier + CAST('a' || CAST(x.objdtdid AS text) AS sql_identifier) AS dtd_identifier FROM pg_namespace n, pg_type at, pg_namespace nbt, pg_type bt, ( - /* columns */ + /* columns, attributes */ SELECT c.relnamespace, CAST(c.relname AS sql_identifier), - 'TABLE'::text, a.attnum, a.atttypid + CASE WHEN c.relkind = 'c' THEN 'USER-DEFINED TYPE'::text ELSE 'TABLE'::text END, + a.attnum, a.atttypid, a.attcollation FROM pg_class c, pg_attribute a WHERE c.oid = a.attrelid - AND c.relkind IN ('r', 'v') + AND c.relkind IN ('r', 'v', 'f', 'c') AND attnum > 0 AND NOT attisdropped UNION ALL /* domains */ SELECT t.typnamespace, CAST(t.typname AS sql_identifier), - 'DOMAIN'::text, 1, t.typbasetype + 'DOMAIN'::text, 1, t.typbasetype, t.typcollation FROM pg_type t WHERE t.typtype = 'd' UNION ALL /* parameters */ - SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier), - 'ROUTINE'::text, pos.n, p.proargtypes[pos.n-1] - FROM pg_proc p, _pg_keypositions() AS pos(n) - WHERE p.pronargs >= pos.n + SELECT pronamespace, CAST(proname || '_' || CAST(oid AS text) AS sql_identifier), + 'ROUTINE'::text, (ss.x).n, (ss.x).x, 0 + FROM (SELECT p.pronamespace, p.proname, p.oid, + _pg_expandarray(coalesce(p.proallargtypes, p.proargtypes::oid[])) AS x + FROM pg_proc p) AS ss UNION ALL /* result types */ SELECT p.pronamespace, CAST(p.proname || '_' || CAST(p.oid AS text) AS sql_identifier), - 'ROUTINE'::text, 0, p.prorettype + 'ROUTINE'::text, 0, p.prorettype, 0 FROM pg_proc p - ) AS x (objschema, objname, objtype, objdtdid, objtypeid) + ) AS x (objschema, objname, objtype, objdtdid, objtypeid, objcollation) + LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid)) + ON x.objcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default') WHERE n.oid = x.objschema AND at.oid = x.objtypeid @@ -1692,8 +2709,236 @@ CREATE VIEW element_types AS AND at.typelem = bt.oid AND nbt.oid = bt.typnamespace - AND (n.nspname, x.objname, x.objtype, x.objdtdid) IN + AND (n.nspname, x.objname, x.objtype, CAST(x.objdtdid AS sql_identifier)) IN ( SELECT object_schema, object_name, object_type, dtd_identifier FROM data_type_privileges ); GRANT SELECT ON element_types TO PUBLIC; + + +-- SQL/MED views; these use section numbers from part 9 of the standard. +-- (still SQL:2008; there is no SQL:2011 SQL/MED) + +/* Base view for foreign table columns */ +CREATE VIEW _pg_foreign_table_columns AS + SELECT n.nspname, + c.relname, + a.attname, + a.attfdwoptions + FROM pg_foreign_table t, pg_authid u, pg_namespace n, pg_class c, + pg_attribute a + WHERE u.oid = c.relowner + AND (pg_has_role(c.relowner, 'USAGE') + OR has_column_privilege(c.oid, a.attnum, 'SELECT, INSERT, UPDATE, REFERENCES')) + AND n.oid = c.relnamespace + AND c.oid = t.ftrelid + AND c.relkind = 'f' + AND a.attrelid = c.oid + AND a.attnum > 0; + +/* + * 24.2 + * COLUMN_OPTIONS view + */ +CREATE VIEW column_options AS + SELECT CAST(current_database() AS sql_identifier) AS table_catalog, + c.nspname AS table_schema, + c.relname AS table_name, + c.attname AS column_name, + CAST((pg_options_to_table(c.attfdwoptions)).option_name AS sql_identifier) AS option_name, + CAST((pg_options_to_table(c.attfdwoptions)).option_value AS character_data) AS option_value + FROM _pg_foreign_table_columns c; + +GRANT SELECT ON column_options TO PUBLIC; + + +/* Base view for foreign-data wrappers */ +CREATE VIEW _pg_foreign_data_wrappers AS + SELECT w.oid, + w.fdwowner, + w.fdwoptions, + CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog, + CAST(fdwname AS sql_identifier) AS foreign_data_wrapper_name, + CAST(u.rolname AS sql_identifier) AS authorization_identifier, + CAST('c' AS character_data) AS foreign_data_wrapper_language + FROM pg_foreign_data_wrapper w, pg_authid u + WHERE u.oid = w.fdwowner + AND (pg_has_role(fdwowner, 'USAGE') + OR has_foreign_data_wrapper_privilege(w.oid, 'USAGE')); + + +/* + * 24.4 + * FOREIGN_DATA_WRAPPER_OPTIONS view + */ +CREATE VIEW foreign_data_wrapper_options AS + SELECT foreign_data_wrapper_catalog, + foreign_data_wrapper_name, + CAST((pg_options_to_table(w.fdwoptions)).option_name AS sql_identifier) AS option_name, + CAST((pg_options_to_table(w.fdwoptions)).option_value AS character_data) AS option_value + FROM _pg_foreign_data_wrappers w; + +GRANT SELECT ON foreign_data_wrapper_options TO PUBLIC; + + +/* + * 24.5 + * FOREIGN_DATA_WRAPPERS view + */ +CREATE VIEW foreign_data_wrappers AS + SELECT foreign_data_wrapper_catalog, + foreign_data_wrapper_name, + authorization_identifier, + CAST(NULL AS character_data) AS library_name, + foreign_data_wrapper_language + FROM _pg_foreign_data_wrappers w; + +GRANT SELECT ON foreign_data_wrappers TO PUBLIC; + + +/* Base view for foreign servers */ +CREATE VIEW _pg_foreign_servers AS + SELECT s.oid, + s.srvoptions, + CAST(current_database() AS sql_identifier) AS foreign_server_catalog, + CAST(srvname AS sql_identifier) AS foreign_server_name, + CAST(current_database() AS sql_identifier) AS foreign_data_wrapper_catalog, + CAST(w.fdwname AS sql_identifier) AS foreign_data_wrapper_name, + CAST(srvtype AS character_data) AS foreign_server_type, + CAST(srvversion AS character_data) AS foreign_server_version, + CAST(u.rolname AS sql_identifier) AS authorization_identifier + FROM pg_foreign_server s, pg_foreign_data_wrapper w, pg_authid u + WHERE w.oid = s.srvfdw + AND u.oid = s.srvowner + AND (pg_has_role(s.srvowner, 'USAGE') + OR has_server_privilege(s.oid, 'USAGE')); + + +/* + * 24.6 + * FOREIGN_SERVER_OPTIONS view + */ +CREATE VIEW foreign_server_options AS + SELECT foreign_server_catalog, + foreign_server_name, + CAST((pg_options_to_table(s.srvoptions)).option_name AS sql_identifier) AS option_name, + CAST((pg_options_to_table(s.srvoptions)).option_value AS character_data) AS option_value + FROM _pg_foreign_servers s; + +GRANT SELECT ON TABLE foreign_server_options TO PUBLIC; + + +/* + * 24.7 + * FOREIGN_SERVERS view + */ +CREATE VIEW foreign_servers AS + SELECT foreign_server_catalog, + foreign_server_name, + foreign_data_wrapper_catalog, + foreign_data_wrapper_name, + foreign_server_type, + foreign_server_version, + authorization_identifier + FROM _pg_foreign_servers; + +GRANT SELECT ON foreign_servers TO PUBLIC; + + +/* Base view for foreign tables */ +CREATE VIEW _pg_foreign_tables AS + SELECT + CAST(current_database() AS sql_identifier) AS foreign_table_catalog, + n.nspname AS foreign_table_schema, + c.relname AS foreign_table_name, + t.ftoptions AS ftoptions, + CAST(current_database() AS sql_identifier) AS foreign_server_catalog, + CAST(srvname AS sql_identifier) AS foreign_server_name, + CAST(u.rolname AS sql_identifier) AS authorization_identifier + FROM pg_foreign_table t, pg_foreign_server s, pg_foreign_data_wrapper w, + pg_authid u, pg_namespace n, pg_class c + WHERE w.oid = s.srvfdw + AND u.oid = c.relowner + AND (pg_has_role(c.relowner, 'USAGE') + OR has_table_privilege(c.oid, 'SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') + OR has_any_column_privilege(c.oid, 'SELECT, INSERT, UPDATE, REFERENCES')) + AND n.oid = c.relnamespace + AND c.oid = t.ftrelid + AND c.relkind = 'f' + AND s.oid = t.ftserver; + + +/* + * 24.8 + * FOREIGN_TABLE_OPTIONS view + */ +CREATE VIEW foreign_table_options AS + SELECT foreign_table_catalog, + foreign_table_schema, + foreign_table_name, + CAST((pg_options_to_table(t.ftoptions)).option_name AS sql_identifier) AS option_name, + CAST((pg_options_to_table(t.ftoptions)).option_value AS character_data) AS option_value + FROM _pg_foreign_tables t; + +GRANT SELECT ON TABLE foreign_table_options TO PUBLIC; + + +/* + * 24.9 + * FOREIGN_TABLES view + */ +CREATE VIEW foreign_tables AS + SELECT foreign_table_catalog, + foreign_table_schema, + foreign_table_name, + foreign_server_catalog, + foreign_server_name + FROM _pg_foreign_tables; + +GRANT SELECT ON foreign_tables TO PUBLIC; + + + +/* Base view for user mappings */ +CREATE VIEW _pg_user_mappings AS + SELECT um.oid, + um.umoptions, + um.umuser, + CAST(COALESCE(u.rolname,'PUBLIC') AS sql_identifier ) AS authorization_identifier, + s.foreign_server_catalog, + s.foreign_server_name, + s.authorization_identifier AS srvowner + FROM pg_user_mapping um LEFT JOIN pg_authid u ON (u.oid = um.umuser), + _pg_foreign_servers s + WHERE s.oid = um.umserver; + + +/* + * 24.12 + * USER_MAPPING_OPTIONS view + */ +CREATE VIEW user_mapping_options AS + SELECT authorization_identifier, + foreign_server_catalog, + foreign_server_name, + CAST((pg_options_to_table(um.umoptions)).option_name AS sql_identifier) AS option_name, + CAST(CASE WHEN (umuser <> 0 AND authorization_identifier = current_user) + OR (umuser = 0 AND pg_has_role(srvowner, 'USAGE')) + OR (SELECT rolsuper FROM pg_authid WHERE rolname = current_user) THEN (pg_options_to_table(um.umoptions)).option_value + ELSE NULL END AS character_data) AS option_value + FROM _pg_user_mappings um; + +GRANT SELECT ON user_mapping_options TO PUBLIC; + + +/* + * 24.13 + * USER_MAPPINGS view + */ +CREATE VIEW user_mappings AS + SELECT authorization_identifier, + foreign_server_catalog, + foreign_server_name + FROM _pg_user_mappings; + +GRANT SELECT ON user_mappings TO PUBLIC;