diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/backend/catalog/aclchk.c | 99 | ||||
| -rw-r--r-- | src/backend/catalog/information_schema.sql | 212 | ||||
| -rw-r--r-- | src/backend/catalog/system_views.sql | 4 | ||||
| -rw-r--r-- | src/backend/commands/copy.c | 39 | ||||
| -rw-r--r-- | src/backend/utils/adt/acl.c | 1186 | ||||
| -rw-r--r-- | src/include/catalog/catversion.h | 4 | ||||
| -rw-r--r-- | src/include/catalog/pg_proc.h | 39 | ||||
| -rw-r--r-- | src/include/utils/builtins.h | 20 | ||||
| -rw-r--r-- | src/test/regress/expected/foreign_data.out | 8 | ||||
| -rw-r--r-- | src/test/regress/expected/privileges.out | 10 | ||||
| -rw-r--r-- | src/test/regress/expected/rules.out | 6 | ||||
| -rw-r--r-- | src/test/regress/sql/privileges.sql | 8 |
12 files changed, 1219 insertions, 416 deletions
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c index b49c80e485b..2924dddf625 100644 --- a/src/backend/catalog/aclchk.c +++ b/src/backend/catalog/aclchk.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/catalog/aclchk.c,v 1.152 2009/01/22 20:16:00 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/aclchk.c,v 1.153 2009/02/06 21:15:11 tgl Exp $ * * NOTES * See acl.h. @@ -2292,22 +2292,7 @@ pg_attribute_aclmask(Oid table_oid, AttrNumber attnum, Oid roleid, Oid ownerId; /* - * Must get the relation's tuple from pg_class (only needed for ownerId) - */ - classTuple = SearchSysCache(RELOID, - ObjectIdGetDatum(table_oid), - 0, 0, 0); - if (!HeapTupleIsValid(classTuple)) - ereport(ERROR, - (errcode(ERRCODE_UNDEFINED_TABLE), - errmsg("relation with OID %u does not exist", - table_oid))); - classForm = (Form_pg_class) GETSTRUCT(classTuple); - - ownerId = classForm->relowner; - - /* - * Next, get the column's ACL from pg_attribute + * First, get the column's ACL from its pg_attribute entry */ attTuple = SearchSysCache(ATTNUM, ObjectIdGetDatum(table_oid), @@ -2330,17 +2315,41 @@ pg_attribute_aclmask(Oid table_oid, AttrNumber attnum, Oid roleid, aclDatum = SysCacheGetAttr(ATTNUM, attTuple, Anum_pg_attribute_attacl, &isNull); + /* + * Here we hard-wire knowledge that the default ACL for a column + * grants no privileges, so that we can fall out quickly in the + * very common case where attacl is null. + */ if (isNull) { - /* No ACL, so build default ACL */ - acl = acldefault(ACL_OBJECT_COLUMN, ownerId); - aclDatum = (Datum) 0; + ReleaseSysCache(attTuple); + return 0; } - else + + /* + * Must get the relation's ownerId from pg_class. Since we already found + * a pg_attribute entry, the only likely reason for this to fail is that + * a concurrent DROP of the relation committed since then (which could + * only happen if we don't have lock on the relation). We prefer to + * report "no privileges" rather than failing in such a case, so as to + * avoid unwanted failures in has_column_privilege() tests. + */ + classTuple = SearchSysCache(RELOID, + ObjectIdGetDatum(table_oid), + 0, 0, 0); + if (!HeapTupleIsValid(classTuple)) { - /* detoast column's ACL if necessary */ - acl = DatumGetAclP(aclDatum); + ReleaseSysCache(attTuple); + return 0; } + classForm = (Form_pg_class) GETSTRUCT(classTuple); + + ownerId = classForm->relowner; + + ReleaseSysCache(classTuple); + + /* detoast column's ACL if necessary */ + acl = DatumGetAclP(aclDatum); result = aclmask(acl, roleid, ownerId, mask, how); @@ -2349,7 +2358,6 @@ pg_attribute_aclmask(Oid table_oid, AttrNumber attnum, Oid roleid, pfree(acl); ReleaseSysCache(attTuple); - ReleaseSysCache(classTuple); return result; } @@ -2922,7 +2930,7 @@ pg_attribute_aclcheck(Oid table_oid, AttrNumber attnum, * ACLCHECK_NO_PRIV). * * If 'how' is ACLMASK_ALL, then returns ACLCHECK_OK if user has any of the - * privileges identified by 'mode' on all non-dropped columns in the relation + * privileges identified by 'mode' on each non-dropped column in the relation * (and there must be at least one such column); otherwise returns a suitable * error code (in practice, always ACLCHECK_NO_PRIV). * @@ -2942,15 +2950,16 @@ pg_attribute_aclcheck_all(Oid table_oid, Oid roleid, AclMode mode, AttrNumber nattrs; AttrNumber curr_att; - /* Must fetch pg_class row to check number of attributes */ + /* + * Must fetch pg_class row to check number of attributes. As in + * pg_attribute_aclmask, we prefer to return "no privileges" instead + * of throwing an error if we get any unexpected lookup errors. + */ classTuple = SearchSysCache(RELOID, ObjectIdGetDatum(table_oid), 0, 0, 0); if (!HeapTupleIsValid(classTuple)) - ereport(ERROR, - (errcode(ERRCODE_UNDEFINED_TABLE), - errmsg("relation with OID %u does not exist", - table_oid))); + return ACLCHECK_NO_PRIV; classForm = (Form_pg_class) GETSTRUCT(classTuple); nattrs = classForm->relnatts; @@ -2966,26 +2975,36 @@ pg_attribute_aclcheck_all(Oid table_oid, Oid roleid, AclMode mode, for (curr_att = 1; curr_att <= nattrs; curr_att++) { HeapTuple attTuple; - bool isdropped; + AclMode attmask; attTuple = SearchSysCache(ATTNUM, ObjectIdGetDatum(table_oid), Int16GetDatum(curr_att), 0, 0); if (!HeapTupleIsValid(attTuple)) - elog(ERROR, "cache lookup failed for attribute %d of relation %u", - curr_att, table_oid); - - isdropped = ((Form_pg_attribute) GETSTRUCT(attTuple))->attisdropped; - - ReleaseSysCache(attTuple); + continue; /* ignore dropped columns */ - if (isdropped) + if (((Form_pg_attribute) GETSTRUCT(attTuple))->attisdropped) + { + ReleaseSysCache(attTuple); continue; + } + + /* + * Here we hard-wire knowledge that the default ACL for a column + * grants no privileges, so that we can fall out quickly in the + * very common case where attacl is null. + */ + if (heap_attisnull(attTuple, Anum_pg_attribute_attacl)) + attmask = 0; + else + attmask = pg_attribute_aclmask(table_oid, curr_att, roleid, + mode, ACLMASK_ANY); + + ReleaseSysCache(attTuple); - if (pg_attribute_aclmask(table_oid, curr_att, roleid, - mode, ACLMASK_ANY) != 0) + if (attmask != 0) { result = ACLCHECK_OK; if (how == ACLMASK_ANY) diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index 470a454f690..d03083aac3c 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -4,7 +4,7 @@ * * Copyright (c) 2003-2009, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.50 2009/01/20 09:10:20 petere Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/information_schema.sql,v 1.51 2009/02/06 21:15:11 tgl Exp $ */ /* @@ -494,8 +494,13 @@ CREATE VIEW column_privileges AS 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(grantee.oid, u_grantor.oid, pr.type, true)) + CASE WHEN + -- object owner always has grant options + pg_has_role(grantee.oid, c.relowner, 'USAGE') + OR aclcontains(c.relacl, + makeaclitem(grantee.oid, u_grantor.oid, pr.type, true)) + OR aclcontains(a.attacl, + makeaclitem(grantee.oid, u_grantor.oid, pr.type, true)) THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable FROM pg_attribute a, @@ -507,18 +512,20 @@ CREATE VIEW column_privileges AS UNION ALL SELECT 0::oid, 'PUBLIC' ) AS grantee (oid, rolname), - (SELECT 'SELECT' UNION ALL - SELECT 'INSERT' UNION ALL - SELECT 'UPDATE' UNION ALL - SELECT 'REFERENCES') AS pr (type) + (VALUES ('SELECT'), + ('INSERT'), + ('UPDATE'), + ('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(grantee.oid, u_grantor.oid, pr.type, false)) + AND (aclcontains(c.relacl, + makeaclitem(grantee.oid, u_grantor.oid, pr.type, false)) + OR aclcontains(a.attacl, + makeaclitem(grantee.oid, u_grantor.oid, pr.type, false))) AND (pg_has_role(u_grantor.oid, 'USAGE') OR pg_has_role(grantee.oid, 'USAGE') OR grantee.rolname = 'PUBLIC'); @@ -674,10 +681,8 @@ CREATE VIEW columns AS AND a.attnum > 0 AND NOT a.attisdropped AND c.relkind in ('r', 'v') AND (pg_has_role(c.relowner, 'USAGE') - 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') ); + OR has_column_privilege(c.oid, a.attnum, + 'SELECT, INSERT, UPDATE, REFERENCES')); GRANT SELECT ON columns TO PUBLIC; @@ -954,8 +959,8 @@ CREATE VIEW key_column_usage AS END AS cardinal_number) AS position_in_unique_constraint FROM pg_attribute a, - (SELECT r.oid AS roid, r.relname, nc.nspname AS nc_nspname, - nr.nspname AS nr_nspname, + (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.confkey, c.confrelid, _pg_expandarray(c.conkey) AS x FROM pg_namespace nr, pg_class r, pg_namespace nc, @@ -965,15 +970,13 @@ CREATE VIEW key_column_usage AS AND nc.oid = c.connamespace AND c.contype IN ('p', 'u', 'f') AND r.relkind = 'r' - AND (NOT pg_is_other_temp_schema(nr.oid)) - AND (pg_has_role(r.relowner, 'USAGE') - OR has_table_privilege(r.oid, 'SELECT') - OR has_table_privilege(r.oid, 'INSERT') - OR has_table_privilege(r.oid, 'UPDATE') - OR has_table_privilege(r.oid, 'REFERENCES')) ) AS ss + 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 NOT a.attisdropped + 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; @@ -1114,7 +1117,10 @@ CREATE VIEW referential_constraints AS WHERE c.relkind = 'r' AND con.contype = 'f' AND (pkc.contype IN ('p', 'u') OR pkc.contype IS NULL) - AND pg_has_role(c.relowner, 'USAGE'); + 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 referential_constraints TO PUBLIC; @@ -1133,8 +1139,13 @@ CREATE VIEW role_column_grants AS 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(g_grantee.oid, u_grantor.oid, pr.type, true)) + CASE WHEN + -- object owner always has grant options + pg_has_role(g_grantee.oid, c.relowner, 'USAGE') + OR aclcontains(c.relacl, + makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, true)) + OR aclcontains(a.attacl, + makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, true)) THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable FROM pg_attribute a, @@ -1142,18 +1153,20 @@ CREATE VIEW role_column_grants AS pg_namespace nc, pg_authid u_grantor, pg_authid g_grantee, - (SELECT 'SELECT' UNION ALL - SELECT 'INSERT' UNION ALL - SELECT 'UPDATE' UNION ALL - SELECT 'REFERENCES') AS pr (type) + (VALUES ('SELECT'), + ('INSERT'), + ('UPDATE'), + ('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(g_grantee.oid, u_grantor.oid, pr.type, false)) + AND (aclcontains(c.relacl, + makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, false)) + OR aclcontains(a.attacl, + makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, false))) AND (u_grantor.rolname IN (SELECT role_name FROM enabled_roles) OR g_grantee.rolname IN (SELECT role_name FROM enabled_roles)); @@ -1176,8 +1189,11 @@ CREATE VIEW role_routine_grants 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(g_grantee.oid, u_grantor.oid, 'EXECUTE', true)) + CASE WHEN + -- object owner always has grant options + pg_has_role(g_grantee.oid, p.proowner, 'USAGE') + OR aclcontains(p.proacl, + makeaclitem(g_grantee.oid, u_grantor.oid, 'EXECUTE', true)) THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable FROM pg_proc p, @@ -1207,8 +1223,11 @@ CREATE VIEW role_table_grants AS 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(g_grantee.oid, u_grantor.oid, pr.type, true)) + CASE WHEN + -- object owner always has grant options + pg_has_role(g_grantee.oid, c.relowner, 'USAGE') + OR aclcontains(c.relacl, + makeaclitem(g_grantee.oid, u_grantor.oid, pr.type, true)) THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable, CAST('NO' AS character_data) AS with_hierarchy @@ -1216,13 +1235,13 @@ CREATE VIEW role_table_grants AS pg_namespace nc, pg_authid u_grantor, pg_authid g_grantee, - (SELECT 'SELECT' UNION ALL - SELECT 'INSERT' UNION ALL - SELECT 'UPDATE' UNION ALL - SELECT 'DELETE' UNION ALL - SELECT 'TRUNCATE' UNION ALL - SELECT 'REFERENCES' UNION ALL - SELECT 'TRIGGER') AS pr (type) + (VALUES ('SELECT'), + ('INSERT'), + ('UPDATE'), + ('DELETE'), + ('TRUNCATE'), + ('REFERENCES'), + ('TRIGGER')) AS pr (type) WHERE c.relnamespace = nc.oid AND c.relkind IN ('r', 'v') @@ -1258,8 +1277,11 @@ CREATE VIEW role_usage_grants AS CAST('FOREIGN DATA WRAPPER' AS character_data) AS object_type, CAST('USAGE' AS character_data) AS privilege_type, CAST( - CASE WHEN aclcontains(fdw.fdwacl, - makeaclitem(g_grantee.oid, u_grantor.oid, 'USAGE', true)) + CASE WHEN + -- object owner always has grant options + pg_has_role(g_grantee.oid, fdw.fdwowner, 'USAGE') + OR aclcontains(fdw.fdwacl, + makeaclitem(g_grantee.oid, u_grantor.oid, 'USAGE', true)) THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable FROM pg_foreign_data_wrapper fdw, @@ -1282,8 +1304,11 @@ CREATE VIEW role_usage_grants AS CAST('FOREIGN SERVER' AS character_data) AS object_type, CAST('USAGE' AS character_data) AS privilege_type, CAST( - CASE WHEN aclcontains(srv.srvacl, - makeaclitem(g_grantee.oid, u_grantor.oid, 'USAGE', true)) + CASE WHEN + -- object owner always has grant options + pg_has_role(g_grantee.oid, srv.srvowner, 'USAGE') + OR aclcontains(srv.srvacl, + makeaclitem(g_grantee.oid, u_grantor.oid, 'USAGE', true)) THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable FROM pg_foreign_server srv, @@ -1330,8 +1355,11 @@ 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.oid, u_grantor.oid, 'EXECUTE', true)) + CASE WHEN + -- object owner always has grant options + pg_has_role(grantee.oid, p.proowner, 'USAGE') + OR aclcontains(p.proacl, + makeaclitem(grantee.oid, u_grantor.oid, 'EXECUTE', true)) THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable FROM pg_proc p, @@ -1531,8 +1559,7 @@ CREATE VIEW sequences AS AND c.relkind = 'S' AND (NOT pg_is_other_temp_schema(nc.oid)) AND (pg_has_role(c.relowner, 'USAGE') - OR has_table_privilege(c.oid, 'SELECT') - OR has_table_privilege(c.oid, 'UPDATE') ); + OR has_table_privilege(c.oid, 'SELECT, UPDATE') ); GRANT SELECT ON sequences TO PUBLIC; @@ -1763,12 +1790,8 @@ CREATE VIEW table_constraints AS 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') - OR has_table_privilege(r.oid, 'UPDATE') - OR has_table_privilege(r.oid, 'DELETE') - OR has_table_privilege(r.oid, 'TRUNCATE') - OR has_table_privilege(r.oid, 'REFERENCES') - OR has_table_privilege(r.oid, 'TRIGGER') ) + OR has_table_privilege(r.oid, 'INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER') + OR has_any_column_privilege(r.oid, 'INSERT, UPDATE, REFERENCES') ) UNION @@ -1796,13 +1819,9 @@ CREATE VIEW table_constraints AS AND r.relkind = 'r' AND (NOT pg_is_other_temp_schema(nr.oid)) AND (pg_has_role(r.relowner, 'USAGE') - OR has_table_privilege(r.oid, 'SELECT') - OR has_table_privilege(r.oid, 'INSERT') - OR has_table_privilege(r.oid, 'UPDATE') - OR has_table_privilege(r.oid, 'DELETE') - OR has_table_privilege(r.oid, 'TRUNCATE') - OR has_table_privilege(r.oid, 'REFERENCES') - OR has_table_privilege(r.oid, 'TRIGGER') ); + -- 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; @@ -1828,8 +1847,11 @@ CREATE VIEW table_privileges AS 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(grantee.oid, u_grantor.oid, pr.type, true)) + CASE WHEN + -- object owner always has grant options + pg_has_role(grantee.oid, c.relowner, 'USAGE') + OR aclcontains(c.relacl, + makeaclitem(grantee.oid, u_grantor.oid, pr.type, true)) THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable, CAST('NO' AS character_data) AS with_hierarchy @@ -1841,13 +1863,13 @@ CREATE VIEW table_privileges AS UNION ALL SELECT 0::oid, 'PUBLIC' ) AS grantee (oid, rolname), - (SELECT 'SELECT' UNION ALL - SELECT 'INSERT' UNION ALL - SELECT 'UPDATE' UNION ALL - SELECT 'DELETE' UNION ALL - SELECT 'TRUNCATE' UNION ALL - SELECT 'REFERENCES' UNION ALL - SELECT 'TRIGGER') AS pr (type) + (VALUES ('SELECT'), + ('INSERT'), + ('UPDATE'), + ('DELETE'), + ('TRUNCATE'), + ('REFERENCES'), + ('TRIGGER')) AS pr (type) WHERE c.relnamespace = nc.oid AND c.relkind IN ('r', 'v') @@ -1901,13 +1923,8 @@ CREATE VIEW tables AS AND c.relkind IN ('r', 'v') AND (NOT pg_is_other_temp_schema(nc.oid)) AND (pg_has_role(c.relowner, 'USAGE') - 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, 'TRUNCATE') - OR has_table_privilege(c.oid, 'REFERENCES') - OR has_table_privilege(c.oid, 'TRIGGER') ); + 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; @@ -2013,9 +2030,9 @@ CREATE VIEW triggers AS CAST(null AS time_stamp) AS created FROM pg_namespace n, pg_class c, pg_trigger t, - (SELECT 4, 'INSERT' UNION ALL - SELECT 8, 'DELETE' UNION ALL - SELECT 16, 'UPDATE') AS em (num, text) + (VALUES (4, 'INSERT'), + (8, 'DELETE'), + (16, 'UPDATE')) AS em (num, text) WHERE n.oid = c.relnamespace AND c.oid = t.tgrelid @@ -2024,12 +2041,8 @@ CREATE VIEW triggers AS 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') - OR has_table_privilege(c.oid, 'UPDATE') - OR has_table_privilege(c.oid, 'DELETE') - OR has_table_privilege(c.oid, 'TRUNCATE') - OR has_table_privilege(c.oid, 'REFERENCES') - OR has_table_privilege(c.oid, 'TRIGGER') ); + 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; @@ -2079,8 +2092,11 @@ CREATE VIEW usage_privileges AS CAST('FOREIGN DATA WRAPPER' AS character_data) AS object_type, CAST('USAGE' AS character_data) AS privilege_type, CAST( - CASE WHEN aclcontains(fdw.fdwacl, - makeaclitem(grantee.oid, u_grantor.oid, 'USAGE', true)) + CASE WHEN + -- object owner always has grant options + pg_has_role(grantee.oid, fdw.fdwowner, 'USAGE') + OR aclcontains(fdw.fdwacl, + makeaclitem(grantee.oid, u_grantor.oid, 'USAGE', true)) THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable FROM pg_foreign_data_wrapper fdw, @@ -2108,8 +2124,11 @@ CREATE VIEW usage_privileges AS CAST('FOREIGN SERVER' AS character_data) AS object_type, CAST('USAGE' AS character_data) AS privilege_type, CAST( - CASE WHEN aclcontains(srv.srvacl, - makeaclitem(grantee.oid, u_grantor.oid, 'USAGE', true)) + CASE WHEN + -- object owner always has grant options + pg_has_role(grantee.oid, srv.srvowner, 'USAGE') + OR aclcontains(srv.srvacl, + makeaclitem(grantee.oid, u_grantor.oid, 'USAGE', true)) THEN 'YES' ELSE 'NO' END AS character_data) AS is_grantable FROM pg_foreign_server srv, @@ -2279,13 +2298,8 @@ CREATE VIEW views AS AND c.relkind = 'v' AND (NOT pg_is_other_temp_schema(nc.oid)) AND (pg_has_role(c.relowner, 'USAGE') - 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, 'TRUNCATE') - OR has_table_privilege(c.oid, 'REFERENCES') - OR has_table_privilege(c.oid, 'TRIGGER') ); + 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; diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 33c32108192..897d930bfeb 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -3,7 +3,7 @@ * * Copyright (c) 1996-2009, PostgreSQL Global Development Group * - * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.58 2009/01/01 17:23:37 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.59 2009/02/06 21:15:11 tgl Exp $ */ CREATE VIEW pg_roles AS @@ -137,7 +137,7 @@ CREATE VIEW pg_stats AS FROM pg_statistic s JOIN pg_class c ON (c.oid = s.starelid) JOIN pg_attribute a ON (c.oid = attrelid AND attnum = s.staattnum) LEFT JOIN pg_namespace n ON (n.oid = c.relnamespace) - WHERE has_table_privilege(c.oid, 'select'); + WHERE NOT attisdropped AND has_column_privilege(c.oid, a.attnum, 'select'); REVOKE ALL on pg_statistic FROM public; diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c index c2e2c822052..550e7e661fd 100644 --- a/src/backend/commands/copy.c +++ b/src/backend/commands/copy.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/commands/copy.c,v 1.304 2009/01/02 20:42:00 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/commands/copy.c,v 1.305 2009/02/06 21:15:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -711,7 +711,7 @@ CopyLoadRawBuf(CopyState cstate) * or write to a file. * * Do not allow the copy if user doesn't have proper permission to access - * the table. + * the table or the specifically requested columns. */ uint64 DoCopy(const CopyStmt *stmt, const char *queryString) @@ -723,7 +723,8 @@ DoCopy(const CopyStmt *stmt, const char *queryString) List *force_quote = NIL; List *force_notnull = NIL; AclMode required_access = (is_from ? ACL_INSERT : ACL_SELECT); - AclResult aclresult; + AclMode relPerms; + AclMode remainingPerms; ListCell *option; TupleDesc tupDesc; int num_phys_attrs; @@ -973,13 +974,31 @@ DoCopy(const CopyStmt *stmt, const char *queryString) cstate->rel = heap_openrv(stmt->relation, (is_from ? RowExclusiveLock : AccessShareLock)); + tupDesc = RelationGetDescr(cstate->rel); + /* Check relation permissions. */ - aclresult = pg_class_aclcheck(RelationGetRelid(cstate->rel), - GetUserId(), - required_access); - if (aclresult != ACLCHECK_OK) - aclcheck_error(aclresult, ACL_KIND_CLASS, - RelationGetRelationName(cstate->rel)); + relPerms = pg_class_aclmask(RelationGetRelid(cstate->rel), GetUserId(), + required_access, ACLMASK_ALL); + remainingPerms = required_access & ~relPerms; + if (remainingPerms != 0) + { + /* We don't have table permissions, check per-column permissions */ + List *attnums; + ListCell *cur; + + attnums = CopyGetAttnums(tupDesc, cstate->rel, attnamelist); + foreach(cur, attnums) + { + int attnum = lfirst_int(cur); + + if (pg_attribute_aclcheck(RelationGetRelid(cstate->rel), + attnum, + GetUserId(), + remainingPerms) != ACLCHECK_OK) + aclcheck_error(ACLCHECK_NO_PRIV, ACL_KIND_CLASS, + RelationGetRelationName(cstate->rel)); + } + } /* check read-only transaction */ if (XactReadOnly && is_from && @@ -994,8 +1013,6 @@ DoCopy(const CopyStmt *stmt, const char *queryString) (errcode(ERRCODE_UNDEFINED_COLUMN), errmsg("table \"%s\" does not have OIDs", RelationGetRelationName(cstate->rel)))); - - tupDesc = RelationGetDescr(cstate->rel); } else { diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c index cb0ebf46941..64d55b6b1ce 100644 --- a/src/backend/utils/adt/acl.c +++ b/src/backend/utils/adt/acl.c @@ -8,7 +8,7 @@ * * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/utils/adt/acl.c,v 1.146 2009/01/22 20:16:06 tgl Exp $ + * $PostgreSQL: pgsql/src/backend/utils/adt/acl.c,v 1.147 2009/02/06 21:15:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -32,6 +32,12 @@ #include "utils/syscache.h" +typedef struct +{ + const char *name; + AclMode value; +} priv_map; + /* * We frequently need to test whether a given role is a member of some other * role. In most of these tests the "given role" is the same, namely the @@ -77,17 +83,25 @@ static Acl *recursive_revoke(Acl *acl, Oid grantee, AclMode revoke_privs, static int oidComparator(const void *arg1, const void *arg2); static AclMode convert_priv_string(text *priv_type_text); +static AclMode convert_any_priv_string(text *priv_type_text, + const priv_map *privileges); static Oid convert_table_name(text *tablename); static AclMode convert_table_priv_string(text *priv_type_text); +static AttrNumber convert_column_name(Oid tableoid, text *column); +static AclMode convert_column_priv_string(text *priv_type_text); static Oid convert_database_name(text *databasename); static AclMode convert_database_priv_string(text *priv_type_text); +static Oid convert_foreign_data_wrapper_name(text *fdwname); +static AclMode convert_foreign_data_wrapper_priv_string(text *priv_type_text); static Oid convert_function_name(text *functionname); static AclMode convert_function_priv_string(text *priv_type_text); static Oid convert_language_name(text *languagename); static AclMode convert_language_priv_string(text *priv_type_text); static Oid convert_schema_name(text *schemaname); static AclMode convert_schema_priv_string(text *priv_type_text); +static Oid convert_server_name(text *servername); +static AclMode convert_server_priv_string(text *priv_type_text); static Oid convert_tablespace_name(text *tablespacename); static AclMode convert_tablespace_priv_string(text *priv_type_text); static AclMode convert_role_priv_string(text *priv_type_text); @@ -1420,6 +1434,63 @@ convert_priv_string(text *priv_type_text) /* + * convert_any_priv_string: recognize privilege strings for has_foo_privilege + * + * We accept a comma-separated list of case-insensitive privilege names, + * producing a bitmask of the OR'd privilege bits. We are liberal about + * whitespace between items, not so much about whitespace within items. + * The allowed privilege names are given as an array of priv_map structs, + * terminated by one with a NULL name pointer. + */ +static AclMode +convert_any_priv_string(text *priv_type_text, + const priv_map *privileges) +{ + AclMode result = 0; + char *priv_type = text_to_cstring(priv_type_text); + char *chunk; + char *next_chunk; + + /* We rely on priv_type being a private, modifiable string */ + for (chunk = priv_type; chunk; chunk = next_chunk) + { + int chunk_len; + const priv_map *this_priv; + + /* Split string at commas */ + next_chunk = strchr(chunk, ','); + if (next_chunk) + *next_chunk++ = '\0'; + + /* Drop leading/trailing whitespace in this chunk */ + while (*chunk && isspace((unsigned char) *chunk)) + chunk++; + chunk_len = strlen(chunk); + while (chunk_len > 0 && isspace((unsigned char) chunk[chunk_len - 1])) + chunk_len--; + chunk[chunk_len] = '\0'; + + /* Match to the privileges list */ + for (this_priv = privileges; this_priv->name; this_priv++) + { + if (pg_strcasecmp(this_priv->name, chunk) == 0) + { + result |= this_priv->value; + break; + } + } + if (!this_priv->name) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("unrecognized privilege type: \"%s\"", chunk))); + } + + pfree(priv_type); + return result; +} + + +/* * has_table_privilege variants * These are all named "has_table_privilege" at the SQL level. * They take various combinations of relation name, relation OID, @@ -1610,55 +1681,651 @@ convert_table_name(text *tablename) static AclMode convert_table_priv_string(text *priv_type_text) { - char *priv_type = text_to_cstring(priv_type_text); + static const priv_map table_priv_map[] = { + { "SELECT", ACL_SELECT }, + { "SELECT WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_SELECT) }, + { "INSERT", ACL_INSERT }, + { "INSERT WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_INSERT) }, + { "UPDATE", ACL_UPDATE }, + { "UPDATE WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_UPDATE) }, + { "DELETE", ACL_DELETE }, + { "DELETE WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_DELETE) }, + { "TRUNCATE", ACL_TRUNCATE }, + { "TRUNCATE WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_TRUNCATE) }, + { "REFERENCES", ACL_REFERENCES }, + { "REFERENCES WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_REFERENCES) }, + { "TRIGGER", ACL_TRIGGER }, + { "TRIGGER WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_TRIGGER) }, + { "RULE", 0 }, /* ignore old RULE privileges */ + { "RULE WITH GRANT OPTION", 0 }, + { NULL, 0 } + }; + + return convert_any_priv_string(priv_type_text, table_priv_map); +} + + +/* + * has_any_column_privilege variants + * These are all named "has_any_column_privilege" at the SQL level. + * They take various combinations of relation name, relation OID, + * user name, user OID, or implicit user = current_user. + * + * The result is a boolean value: true if user has the indicated + * privilege for any column of the table, false if not. The variants + * that take a relation OID return NULL if the OID doesn't exist. + */ + +/* + * has_any_column_privilege_name_name + * Check user privileges on any column of a table given + * name username, text tablename, and text priv name. + */ +Datum +has_any_column_privilege_name_name(PG_FUNCTION_ARGS) +{ + Name rolename = PG_GETARG_NAME(0); + text *tablename = PG_GETARG_TEXT_P(1); + text *priv_type_text = PG_GETARG_TEXT_P(2); + Oid roleid; + Oid tableoid; + AclMode mode; + AclResult aclresult; + + roleid = get_roleid_checked(NameStr(*rolename)); + tableoid = convert_table_name(tablename); + mode = convert_column_priv_string(priv_type_text); + + /* First check at table level, then examine each column if needed */ + aclresult = pg_class_aclcheck(tableoid, roleid, mode); + if (aclresult != ACLCHECK_OK) + aclresult = pg_attribute_aclcheck_all(tableoid, roleid, mode, + ACLMASK_ANY); + + PG_RETURN_BOOL(aclresult == ACLCHECK_OK); +} + +/* + * has_any_column_privilege_name + * Check user privileges on any column of a table given + * text tablename and text priv name. + * current_user is assumed + */ +Datum +has_any_column_privilege_name(PG_FUNCTION_ARGS) +{ + text *tablename = PG_GETARG_TEXT_P(0); + text *priv_type_text = PG_GETARG_TEXT_P(1); + Oid roleid; + Oid tableoid; + AclMode mode; + AclResult aclresult; + + roleid = GetUserId(); + tableoid = convert_table_name(tablename); + mode = convert_column_priv_string(priv_type_text); + + /* First check at table level, then examine each column if needed */ + aclresult = pg_class_aclcheck(tableoid, roleid, mode); + if (aclresult != ACLCHECK_OK) + aclresult = pg_attribute_aclcheck_all(tableoid, roleid, mode, + ACLMASK_ANY); + + PG_RETURN_BOOL(aclresult == ACLCHECK_OK); +} + +/* + * has_any_column_privilege_name_id + * Check user privileges on any column of a table given + * name usename, table oid, and text priv name. + */ +Datum +has_any_column_privilege_name_id(PG_FUNCTION_ARGS) +{ + Name username = PG_GETARG_NAME(0); + Oid tableoid = PG_GETARG_OID(1); + text *priv_type_text = PG_GETARG_TEXT_P(2); + Oid roleid; + AclMode mode; + AclResult aclresult; + + roleid = get_roleid_checked(NameStr(*username)); + mode = convert_column_priv_string(priv_type_text); + + if (!SearchSysCacheExists(RELOID, + ObjectIdGetDatum(tableoid), + 0, 0, 0)) + PG_RETURN_NULL(); + + /* First check at table level, then examine each column if needed */ + aclresult = pg_class_aclcheck(tableoid, roleid, mode); + if (aclresult != ACLCHECK_OK) + aclresult = pg_attribute_aclcheck_all(tableoid, roleid, mode, + ACLMASK_ANY); + + PG_RETURN_BOOL(aclresult == ACLCHECK_OK); +} + +/* + * has_any_column_privilege_id + * Check user privileges on any column of a table given + * table oid, and text priv name. + * current_user is assumed + */ +Datum +has_any_column_privilege_id(PG_FUNCTION_ARGS) +{ + Oid tableoid = PG_GETARG_OID(0); + text *priv_type_text = PG_GETARG_TEXT_P(1); + Oid roleid; + AclMode mode; + AclResult aclresult; + + roleid = GetUserId(); + mode = convert_column_priv_string(priv_type_text); + + if (!SearchSysCacheExists(RELOID, + ObjectIdGetDatum(tableoid), + 0, 0, 0)) + PG_RETURN_NULL(); + + /* First check at table level, then examine each column if needed */ + aclresult = pg_class_aclcheck(tableoid, roleid, mode); + if (aclresult != ACLCHECK_OK) + aclresult = pg_attribute_aclcheck_all(tableoid, roleid, mode, + ACLMASK_ANY); + + PG_RETURN_BOOL(aclresult == ACLCHECK_OK); +} + +/* + * has_any_column_privilege_id_name + * Check user privileges on any column of a table given + * roleid, text tablename, and text priv name. + */ +Datum +has_any_column_privilege_id_name(PG_FUNCTION_ARGS) +{ + Oid roleid = PG_GETARG_OID(0); + text *tablename = PG_GETARG_TEXT_P(1); + text *priv_type_text = PG_GETARG_TEXT_P(2); + Oid tableoid; + AclMode mode; + AclResult aclresult; + + tableoid = convert_table_name(tablename); + mode = convert_column_priv_string(priv_type_text); + + /* First check at table level, then examine each column if needed */ + aclresult = pg_class_aclcheck(tableoid, roleid, mode); + if (aclresult != ACLCHECK_OK) + aclresult = pg_attribute_aclcheck_all(tableoid, roleid, mode, + ACLMASK_ANY); + + PG_RETURN_BOOL(aclresult == ACLCHECK_OK); +} + +/* + * has_any_column_privilege_id_id + * Check user privileges on any column of a table given + * roleid, table oid, and text priv name. + */ +Datum +has_any_column_privilege_id_id(PG_FUNCTION_ARGS) +{ + Oid roleid = PG_GETARG_OID(0); + Oid tableoid = PG_GETARG_OID(1); + text *priv_type_text = PG_GETARG_TEXT_P(2); + AclMode mode; + AclResult aclresult; + + mode = convert_column_priv_string(priv_type_text); + + if (!SearchSysCacheExists(RELOID, + ObjectIdGetDatum(tableoid), + 0, 0, 0)) + PG_RETURN_NULL(); + + /* First check at table level, then examine each column if needed */ + aclresult = pg_class_aclcheck(tableoid, roleid, mode); + if (aclresult != ACLCHECK_OK) + aclresult = pg_attribute_aclcheck_all(tableoid, roleid, mode, + ACLMASK_ANY); + + PG_RETURN_BOOL(aclresult == ACLCHECK_OK); +} + + +/* + * has_column_privilege variants + * These are all named "has_column_privilege" at the SQL level. + * They take various combinations of relation name, relation OID, + * column name, column attnum, user name, user OID, or + * implicit user = current_user. + * + * The result is a boolean value: true if user has the indicated + * privilege, false if not. The variants that take a relation OID + * and an integer attnum return NULL (rather than throwing an error) + * if the column doesn't exist or is dropped. + */ + +/* + * column_privilege_check: check column privileges, but don't throw an error + * for dropped column or table + * + * Returns 1 if have the privilege, 0 if not, -1 if dropped column/table. + */ +static int +column_privilege_check(Oid tableoid, AttrNumber attnum, + Oid roleid, AclMode mode) +{ + AclResult aclresult; + HeapTuple attTuple; + Form_pg_attribute attributeForm; /* - * Return mode from priv_type string + * First check if we have the privilege at the table level. We check + * existence of the pg_class row before risking calling pg_class_aclcheck. + * Note: it might seem there's a race condition against concurrent DROP, + * but really it's safe because there will be no syscache flush between + * here and there. So if we see the row in the syscache, so will + * pg_class_aclcheck. */ - if (pg_strcasecmp(priv_type, "SELECT") == 0) - return ACL_SELECT; - if (pg_strcasecmp(priv_type, "SELECT WITH GRANT OPTION") == 0) - return ACL_GRANT_OPTION_FOR(ACL_SELECT); + if (!SearchSysCacheExists(RELOID, + ObjectIdGetDatum(tableoid), + 0, 0, 0)) + return -1; - if (pg_strcasecmp(priv_type, "INSERT") == 0) - return ACL_INSERT; - if (pg_strcasecmp(priv_type, "INSERT WITH GRANT OPTION") == 0) - return ACL_GRANT_OPTION_FOR(ACL_INSERT); + aclresult = pg_class_aclcheck(tableoid, roleid, mode); - if (pg_strcasecmp(priv_type, "UPDATE") == 0) - return ACL_UPDATE; - if (pg_strcasecmp(priv_type, "UPDATE WITH GRANT OPTION") == 0) - return ACL_GRANT_OPTION_FOR(ACL_UPDATE); + if (aclresult == ACLCHECK_OK) + return true; - if (pg_strcasecmp(priv_type, "DELETE") == 0) - return ACL_DELETE; - if (pg_strcasecmp(priv_type, "DELETE WITH GRANT OPTION") == 0) - return ACL_GRANT_OPTION_FOR(ACL_DELETE); + /* + * No table privilege, so try per-column privileges. Again, we have to + * check for dropped attribute first, and we rely on the syscache not to + * notice a concurrent drop before pg_attribute_aclcheck fetches the row. + */ + attTuple = SearchSysCache(ATTNUM, + ObjectIdGetDatum(tableoid), + Int16GetDatum(attnum), + 0, 0); + if (!HeapTupleIsValid(attTuple)) + return -1; + attributeForm = (Form_pg_attribute) GETSTRUCT(attTuple); + if (attributeForm->attisdropped) + { + ReleaseSysCache(attTuple); + return -1; + } + ReleaseSysCache(attTuple); - if (pg_strcasecmp(priv_type, "TRUNCATE") == 0) - return ACL_TRUNCATE; - if (pg_strcasecmp(priv_type, "TRUNCATE WITH GRANT OPTION") == 0) - return ACL_GRANT_OPTION_FOR(ACL_TRUNCATE); + aclresult = pg_attribute_aclcheck(tableoid, attnum, roleid, mode); - if (pg_strcasecmp(priv_type, "REFERENCES") == 0) - return ACL_REFERENCES; - if (pg_strcasecmp(priv_type, "REFERENCES WITH GRANT OPTION") == 0) - return ACL_GRANT_OPTION_FOR(ACL_REFERENCES); + return (aclresult == ACLCHECK_OK); +} - if (pg_strcasecmp(priv_type, "TRIGGER") == 0) - return ACL_TRIGGER; - if (pg_strcasecmp(priv_type, "TRIGGER WITH GRANT OPTION") == 0) - return ACL_GRANT_OPTION_FOR(ACL_TRIGGER); +/* + * has_column_privilege_name_name_name + * Check user privileges on a column given + * name username, text tablename, text colname, and text priv name. + */ +Datum +has_column_privilege_name_name_name(PG_FUNCTION_ARGS) +{ + Name rolename = PG_GETARG_NAME(0); + text *tablename = PG_GETARG_TEXT_P(1); + text *column = PG_GETARG_TEXT_P(2); + text *priv_type_text = PG_GETARG_TEXT_P(3); + Oid roleid; + Oid tableoid; + AttrNumber colattnum; + AclMode mode; + int privresult; - if (pg_strcasecmp(priv_type, "RULE") == 0) - return 0; /* ignore old RULE privileges */ - if (pg_strcasecmp(priv_type, "RULE WITH GRANT OPTION") == 0) - return 0; + roleid = get_roleid_checked(NameStr(*rolename)); + tableoid = convert_table_name(tablename); + colattnum = convert_column_name(tableoid, column); + mode = convert_column_priv_string(priv_type_text); - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("unrecognized privilege type: \"%s\"", priv_type))); - return ACL_NO_RIGHTS; /* keep compiler quiet */ + privresult = column_privilege_check(tableoid, colattnum, roleid, mode); + if (privresult < 0) + PG_RETURN_NULL(); + PG_RETURN_BOOL(privresult); +} + +/* + * has_column_privilege_name_name_attnum + * Check user privileges on a column given + * name username, text tablename, int attnum, and text priv name. + */ +Datum +has_column_privilege_name_name_attnum(PG_FUNCTION_ARGS) +{ + Name rolename = PG_GETARG_NAME(0); + text *tablename = PG_GETARG_TEXT_P(1); + AttrNumber colattnum = PG_GETARG_INT16(2); + text *priv_type_text = PG_GETARG_TEXT_P(3); + Oid roleid; + Oid tableoid; + AclMode mode; + int privresult; + + roleid = get_roleid_checked(NameStr(*rolename)); + tableoid = convert_table_name(tablename); + mode = convert_column_priv_string(priv_type_text); + + privresult = column_privilege_check(tableoid, colattnum, roleid, mode); + if (privresult < 0) + PG_RETURN_NULL(); + PG_RETURN_BOOL(privresult); +} + +/* + * has_column_privilege_name_id_name + * Check user privileges on a column given + * name username, table oid, text colname, and text priv name. + */ +Datum +has_column_privilege_name_id_name(PG_FUNCTION_ARGS) +{ + Name username = PG_GETARG_NAME(0); + Oid tableoid = PG_GETARG_OID(1); + text *column = PG_GETARG_TEXT_P(2); + text *priv_type_text = PG_GETARG_TEXT_P(3); + Oid roleid; + AttrNumber colattnum; + AclMode mode; + int privresult; + + roleid = get_roleid_checked(NameStr(*username)); + colattnum = convert_column_name(tableoid, column); + mode = convert_column_priv_string(priv_type_text); + + privresult = column_privilege_check(tableoid, colattnum, roleid, mode); + if (privresult < 0) + PG_RETURN_NULL(); + PG_RETURN_BOOL(privresult); +} + +/* + * has_column_privilege_name_id_attnum + * Check user privileges on a column given + * name username, table oid, int attnum, and text priv name. + */ +Datum +has_column_privilege_name_id_attnum(PG_FUNCTION_ARGS) +{ + Name username = PG_GETARG_NAME(0); + Oid tableoid = PG_GETARG_OID(1); + AttrNumber colattnum = PG_GETARG_INT16(2); + text *priv_type_text = PG_GETARG_TEXT_P(3); + Oid roleid; + AclMode mode; + int privresult; + + roleid = get_roleid_checked(NameStr(*username)); + mode = convert_column_priv_string(priv_type_text); + + privresult = column_privilege_check(tableoid, colattnum, roleid, mode); + if (privresult < 0) + PG_RETURN_NULL(); + PG_RETURN_BOOL(privresult); +} + +/* + * has_column_privilege_id_name_name + * Check user privileges on a column given + * oid roleid, text tablename, text colname, and text priv name. + */ +Datum +has_column_privilege_id_name_name(PG_FUNCTION_ARGS) +{ + Oid roleid = PG_GETARG_OID(0); + text *tablename = PG_GETARG_TEXT_P(1); + text *column = PG_GETARG_TEXT_P(2); + text *priv_type_text = PG_GETARG_TEXT_P(3); + Oid tableoid; + AttrNumber colattnum; + AclMode mode; + int privresult; + + tableoid = convert_table_name(tablename); + colattnum = convert_column_name(tableoid, column); + mode = convert_column_priv_string(priv_type_text); + + privresult = column_privilege_check(tableoid, colattnum, roleid, mode); + if (privresult < 0) + PG_RETURN_NULL(); + PG_RETURN_BOOL(privresult); +} + +/* + * has_column_privilege_id_name_attnum + * Check user privileges on a column given + * oid roleid, text tablename, int attnum, and text priv name. + */ +Datum +has_column_privilege_id_name_attnum(PG_FUNCTION_ARGS) +{ + Oid roleid = PG_GETARG_OID(0); + text *tablename = PG_GETARG_TEXT_P(1); + AttrNumber colattnum = PG_GETARG_INT16(2); + text *priv_type_text = PG_GETARG_TEXT_P(3); + Oid tableoid; + AclMode mode; + int privresult; + + tableoid = convert_table_name(tablename); + mode = convert_column_priv_string(priv_type_text); + + privresult = column_privilege_check(tableoid, colattnum, roleid, mode); + if (privresult < 0) + PG_RETURN_NULL(); + PG_RETURN_BOOL(privresult); +} + +/* + * has_column_privilege_id_id_name + * Check user privileges on a column given + * oid roleid, table oid, text colname, and text priv name. + */ +Datum +has_column_privilege_id_id_name(PG_FUNCTION_ARGS) +{ + Oid roleid = PG_GETARG_OID(0); + Oid tableoid = PG_GETARG_OID(1); + text *column = PG_GETARG_TEXT_P(2); + text *priv_type_text = PG_GETARG_TEXT_P(3); + AttrNumber colattnum; + AclMode mode; + int privresult; + + colattnum = convert_column_name(tableoid, column); + mode = convert_column_priv_string(priv_type_text); + + privresult = column_privilege_check(tableoid, colattnum, roleid, mode); + if (privresult < 0) + PG_RETURN_NULL(); + PG_RETURN_BOOL(privresult); +} + +/* + * has_column_privilege_id_id_attnum + * Check user privileges on a column given + * oid roleid, table oid, int attnum, and text priv name. + */ +Datum +has_column_privilege_id_id_attnum(PG_FUNCTION_ARGS) +{ + Oid roleid = PG_GETARG_OID(0); + Oid tableoid = PG_GETARG_OID(1); + AttrNumber colattnum = PG_GETARG_INT16(2); + text *priv_type_text = PG_GETARG_TEXT_P(3); + AclMode mode; + int privresult; + + mode = convert_column_priv_string(priv_type_text); + + privresult = column_privilege_check(tableoid, colattnum, roleid, mode); + if (privresult < 0) + PG_RETURN_NULL(); + PG_RETURN_BOOL(privresult); +} + +/* + * has_column_privilege_name_name + * Check user privileges on a column given + * text tablename, text colname, and text priv name. + * current_user is assumed + */ +Datum +has_column_privilege_name_name(PG_FUNCTION_ARGS) +{ + text *tablename = PG_GETARG_TEXT_P(0); + text *column = PG_GETARG_TEXT_P(1); + text *priv_type_text = PG_GETARG_TEXT_P(2); + Oid roleid; + Oid tableoid; + AttrNumber colattnum; + AclMode mode; + int privresult; + + roleid = GetUserId(); + tableoid = convert_table_name(tablename); + colattnum = convert_column_name(tableoid, column); + mode = convert_column_priv_string(priv_type_text); + + privresult = column_privilege_check(tableoid, colattnum, roleid, mode); + if (privresult < 0) + PG_RETURN_NULL(); + PG_RETURN_BOOL(privresult); +} + +/* + * has_column_privilege_name_attnum + * Check user privileges on a column given + * text tablename, int attnum, and text priv name. + * current_user is assumed + */ +Datum +has_column_privilege_name_attnum(PG_FUNCTION_ARGS) +{ + text *tablename = PG_GETARG_TEXT_P(0); + AttrNumber colattnum = PG_GETARG_INT16(1); + text *priv_type_text = PG_GETARG_TEXT_P(2); + Oid roleid; + Oid tableoid; + AclMode mode; + int privresult; + + roleid = GetUserId(); + tableoid = convert_table_name(tablename); + mode = convert_column_priv_string(priv_type_text); + + privresult = column_privilege_check(tableoid, colattnum, roleid, mode); + if (privresult < 0) + PG_RETURN_NULL(); + PG_RETURN_BOOL(privresult); +} + +/* + * has_column_privilege_id_name + * Check user privileges on a column given + * table oid, text colname, and text priv name. + * current_user is assumed + */ +Datum +has_column_privilege_id_name(PG_FUNCTION_ARGS) +{ + Oid tableoid = PG_GETARG_OID(0); + text *column = PG_GETARG_TEXT_P(1); + text *priv_type_text = PG_GETARG_TEXT_P(2); + Oid roleid; + AttrNumber colattnum; + AclMode mode; + int privresult; + + roleid = GetUserId(); + colattnum = convert_column_name(tableoid, column); + mode = convert_column_priv_string(priv_type_text); + + privresult = column_privilege_check(tableoid, colattnum, roleid, mode); + if (privresult < 0) + PG_RETURN_NULL(); + PG_RETURN_BOOL(privresult); +} + +/* + * has_column_privilege_id_attnum + * Check user privileges on a column given + * table oid, int attnum, and text priv name. + * current_user is assumed + */ +Datum +has_column_privilege_id_attnum(PG_FUNCTION_ARGS) +{ + Oid tableoid = PG_GETARG_OID(0); + AttrNumber colattnum = PG_GETARG_INT16(1); + text *priv_type_text = PG_GETARG_TEXT_P(2); + Oid roleid; + AclMode mode; + int privresult; + + roleid = GetUserId(); + mode = convert_column_priv_string(priv_type_text); + + privresult = column_privilege_check(tableoid, colattnum, roleid, mode); + if (privresult < 0) + PG_RETURN_NULL(); + PG_RETURN_BOOL(privresult); +} + +/* + * Support routines for has_column_privilege family. + */ + +/* + * Given a table OID and a column name expressed as a string, look it up + * and return the column number + */ +static AttrNumber +convert_column_name(Oid tableoid, text *column) +{ + AttrNumber attnum; + char *colname; + + colname = text_to_cstring(column); + attnum = get_attnum(tableoid, colname); + if (attnum == InvalidAttrNumber) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("column \"%s\" of relation \"%s\" does not exist", + colname, get_rel_name(tableoid)))); + pfree(colname); + return attnum; +} + +/* + * convert_column_priv_string + * Convert text string to AclMode value. + */ +static AclMode +convert_column_priv_string(text *priv_type_text) +{ + static const priv_map column_priv_map[] = { + { "SELECT", ACL_SELECT }, + { "SELECT WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_SELECT) }, + { "INSERT", ACL_INSERT }, + { "INSERT WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_INSERT) }, + { "UPDATE", ACL_UPDATE }, + { "UPDATE WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_UPDATE) }, + { "REFERENCES", ACL_REFERENCES }, + { "REFERENCES WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_REFERENCES) }, + { NULL, 0 } + }; + + return convert_any_priv_string(priv_type_text, column_priv_map); } @@ -1856,35 +2523,20 @@ convert_database_name(text *databasename) static AclMode convert_database_priv_string(text *priv_type_text) { - char *priv_type = text_to_cstring(priv_type_text); - - /* - * Return mode from priv_type string - */ - if (pg_strcasecmp(priv_type, "CREATE") == 0) - return ACL_CREATE; - if (pg_strcasecmp(priv_type, "CREATE WITH GRANT OPTION") == 0) - return ACL_GRANT_OPTION_FOR(ACL_CREATE); - - if (pg_strcasecmp(priv_type, "TEMPORARY") == 0) - return ACL_CREATE_TEMP; - if (pg_strcasecmp(priv_type, "TEMPORARY WITH GRANT OPTION") == 0) - return ACL_GRANT_OPTION_FOR(ACL_CREATE_TEMP); + static const priv_map database_priv_map[] = { + { "CREATE", ACL_CREATE }, + { "CREATE WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_CREATE) }, + { "TEMPORARY", ACL_CREATE_TEMP }, + { "TEMPORARY WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_CREATE_TEMP) }, + { "TEMP", ACL_CREATE_TEMP }, + { "TEMP WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_CREATE_TEMP) }, + { "CONNECT", ACL_CONNECT }, + { "CONNECT WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_CONNECT) }, + { NULL, 0 } + }; + + return convert_any_priv_string(priv_type_text, database_priv_map); - if (pg_strcasecmp(priv_type, "TEMP") == 0) - return ACL_CREATE_TEMP; - if (pg_strcasecmp(priv_type, "TEMP WITH GRANT OPTION") == 0) - return ACL_GRANT_OPTION_FOR(ACL_CREATE_TEMP); - - if (pg_strcasecmp(priv_type, "CONNECT") == 0) - return ACL_CONNECT; - if (pg_strcasecmp(priv_type, "CONNECT WITH GRANT OPTION") == 0) - return ACL_GRANT_OPTION_FOR(ACL_CONNECT); - - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("unrecognized privilege type: \"%s\"", priv_type))); - return ACL_NO_RIGHTS; /* keep compiler quiet */ } @@ -1895,34 +2547,8 @@ convert_database_priv_string(text *priv_type_text) * fdw OID, user name, user OID, or implicit user = current_user. * * The result is a boolean value: true if user has the indicated - * privilege, false if not. The variants that take an OID return - * NULL if the OID doesn't exist. - */ - -/* - * has_foreign_data_wrapper_privilege - * Check user privileges on a foreign-data wrapper. + * privilege, false if not. */ -static Datum -has_foreign_data_wrapper_privilege(Oid roleid, Oid fdwid, text *priv_type_text) -{ - AclResult aclresult; - AclMode mode = ACL_NO_RIGHTS; - char *priv_type = text_to_cstring(priv_type_text); - - if (pg_strcasecmp(priv_type, "USAGE") == 0) - mode = ACL_USAGE; - else if (pg_strcasecmp(priv_type, "USAGE WITH GRANT OPTION") == 0) - mode = ACL_GRANT_OPTION_FOR(ACL_USAGE); - else - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("unrecognized privilege type: \"%s\"", priv_type))); - - aclresult = pg_foreign_data_wrapper_aclcheck(fdwid, roleid, mode); - - PG_RETURN_BOOL(aclresult == ACLCHECK_OK); -} /* * has_foreign_data_wrapper_privilege_name_name @@ -1933,12 +2559,20 @@ Datum has_foreign_data_wrapper_privilege_name_name(PG_FUNCTION_ARGS) { Name username = PG_GETARG_NAME(0); - char *fdwname = text_to_cstring(PG_GETARG_TEXT_P(1)); + text *fdwname = PG_GETARG_TEXT_P(1); text *priv_type_text = PG_GETARG_TEXT_P(2); + Oid roleid; + Oid fdwid; + AclMode mode; + AclResult aclresult; - return has_foreign_data_wrapper_privilege(get_roleid_checked(NameStr(*username)), - GetForeignDataWrapperOidByName(fdwname, false), - priv_type_text); + roleid = get_roleid_checked(NameStr(*username)); + fdwid = convert_foreign_data_wrapper_name(fdwname); + mode = convert_foreign_data_wrapper_priv_string(priv_type_text); + + aclresult = pg_foreign_data_wrapper_aclcheck(fdwid, roleid, mode); + + PG_RETURN_BOOL(aclresult == ACLCHECK_OK); } /* @@ -1950,12 +2584,20 @@ has_foreign_data_wrapper_privilege_name_name(PG_FUNCTION_ARGS) Datum has_foreign_data_wrapper_privilege_name(PG_FUNCTION_ARGS) { - char *fdwname = text_to_cstring(PG_GETARG_TEXT_P(0)); + text *fdwname = PG_GETARG_TEXT_P(0); text *priv_type_text = PG_GETARG_TEXT_P(1); + Oid roleid; + Oid fdwid; + AclMode mode; + AclResult aclresult; + + roleid = GetUserId(); + fdwid = convert_foreign_data_wrapper_name(fdwname); + mode = convert_foreign_data_wrapper_priv_string(priv_type_text); - return has_foreign_data_wrapper_privilege(GetUserId(), - GetForeignDataWrapperOidByName(fdwname, false), - priv_type_text); + aclresult = pg_foreign_data_wrapper_aclcheck(fdwid, roleid, mode); + + PG_RETURN_BOOL(aclresult == ACLCHECK_OK); } /* @@ -1969,14 +2611,16 @@ has_foreign_data_wrapper_privilege_name_id(PG_FUNCTION_ARGS) Name username = PG_GETARG_NAME(0); Oid fdwid = PG_GETARG_OID(1); text *priv_type_text = PG_GETARG_TEXT_P(2); + Oid roleid; + AclMode mode; + AclResult aclresult; - if (!SearchSysCacheExists(FOREIGNDATAWRAPPEROID, - ObjectIdGetDatum(fdwid), - 0, 0, 0)) - PG_RETURN_NULL(); + roleid = get_roleid_checked(NameStr(*username)); + mode = convert_foreign_data_wrapper_priv_string(priv_type_text); + + aclresult = pg_foreign_data_wrapper_aclcheck(fdwid, roleid, mode); - return has_foreign_data_wrapper_privilege(get_roleid_checked(NameStr(*username)), - fdwid, priv_type_text); + PG_RETURN_BOOL(aclresult == ACLCHECK_OK); } /* @@ -1990,14 +2634,16 @@ has_foreign_data_wrapper_privilege_id(PG_FUNCTION_ARGS) { Oid fdwid = PG_GETARG_OID(0); text *priv_type_text = PG_GETARG_TEXT_P(1); + Oid roleid; + AclMode mode; + AclResult aclresult; - if (!SearchSysCacheExists(FOREIGNDATAWRAPPEROID, - ObjectIdGetDatum(fdwid), - 0, 0, 0)) - PG_RETURN_NULL(); + roleid = GetUserId(); + mode = convert_foreign_data_wrapper_priv_string(priv_type_text); + + aclresult = pg_foreign_data_wrapper_aclcheck(fdwid, roleid, mode); - return has_foreign_data_wrapper_privilege(GetUserId(), fdwid, - priv_type_text); + PG_RETURN_BOOL(aclresult == ACLCHECK_OK); } /* @@ -2009,12 +2655,18 @@ Datum has_foreign_data_wrapper_privilege_id_name(PG_FUNCTION_ARGS) { Oid roleid = PG_GETARG_OID(0); - char *fdwname = text_to_cstring(PG_GETARG_TEXT_P(1)); + text *fdwname = PG_GETARG_TEXT_P(1); text *priv_type_text = PG_GETARG_TEXT_P(2); + Oid fdwid; + AclMode mode; + AclResult aclresult; + + fdwid = convert_foreign_data_wrapper_name(fdwname); + mode = convert_foreign_data_wrapper_priv_string(priv_type_text); + + aclresult = pg_foreign_data_wrapper_aclcheck(fdwid, roleid, mode); - return has_foreign_data_wrapper_privilege(roleid, - GetForeignDataWrapperOidByName(fdwname, false), - priv_type_text); + PG_RETURN_BOOL(aclresult == ACLCHECK_OK); } /* @@ -2028,13 +2680,45 @@ has_foreign_data_wrapper_privilege_id_id(PG_FUNCTION_ARGS) Oid roleid = PG_GETARG_OID(0); Oid fdwid = PG_GETARG_OID(1); text *priv_type_text = PG_GETARG_TEXT_P(2); + AclMode mode; + AclResult aclresult; - if (!SearchSysCacheExists(FOREIGNDATAWRAPPEROID, - ObjectIdGetDatum(fdwid), - 0, 0, 0)) - PG_RETURN_NULL(); + mode = convert_foreign_data_wrapper_priv_string(priv_type_text); + + aclresult = pg_foreign_data_wrapper_aclcheck(fdwid, roleid, mode); + + PG_RETURN_BOOL(aclresult == ACLCHECK_OK); +} + +/* + * Support routines for has_foreign_data_wrapper_privilege family. + */ + +/* + * Given a FDW name expressed as a string, look it up and return Oid + */ +static Oid +convert_foreign_data_wrapper_name(text *fdwname) +{ + char *fdwstr = text_to_cstring(fdwname); + + return GetForeignDataWrapperOidByName(fdwstr, false); +} + +/* + * convert_foreign_data_wrapper_priv_string + * Convert text string to AclMode value. + */ +static AclMode +convert_foreign_data_wrapper_priv_string(text *priv_type_text) +{ + static const priv_map foreign_data_wrapper_priv_map[] = { + { "USAGE", ACL_USAGE }, + { "USAGE WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_USAGE) }, + { NULL, 0 } + }; - return has_foreign_data_wrapper_privilege(roleid, fdwid, priv_type_text); + return convert_any_priv_string(priv_type_text, foreign_data_wrapper_priv_map); } @@ -2234,20 +2918,13 @@ convert_function_name(text *functionname) static AclMode convert_function_priv_string(text *priv_type_text) { - char *priv_type = text_to_cstring(priv_type_text); - - /* - * Return mode from priv_type string - */ - if (pg_strcasecmp(priv_type, "EXECUTE") == 0) - return ACL_EXECUTE; - if (pg_strcasecmp(priv_type, "EXECUTE WITH GRANT OPTION") == 0) - return ACL_GRANT_OPTION_FOR(ACL_EXECUTE); + static const priv_map function_priv_map[] = { + { "EXECUTE", ACL_EXECUTE }, + { "EXECUTE WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_EXECUTE) }, + { NULL, 0 } + }; - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("unrecognized privilege type: \"%s\"", priv_type))); - return ACL_NO_RIGHTS; /* keep compiler quiet */ + return convert_any_priv_string(priv_type_text, function_priv_map); } @@ -2447,20 +3124,13 @@ convert_language_name(text *languagename) static AclMode convert_language_priv_string(text *priv_type_text) { - char *priv_type = text_to_cstring(priv_type_text); - - /* - * Return mode from priv_type string - */ - if (pg_strcasecmp(priv_type, "USAGE") == 0) - return ACL_USAGE; - if (pg_strcasecmp(priv_type, "USAGE WITH GRANT OPTION") == 0) - return ACL_GRANT_OPTION_FOR(ACL_USAGE); + static const priv_map language_priv_map[] = { + { "USAGE", ACL_USAGE }, + { "USAGE WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_USAGE) }, + { NULL, 0 } + }; - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("unrecognized privilege type: \"%s\"", priv_type))); - return ACL_NO_RIGHTS; /* keep compiler quiet */ + return convert_any_priv_string(priv_type_text, language_priv_map); } @@ -2660,27 +3330,18 @@ convert_schema_name(text *schemaname) static AclMode convert_schema_priv_string(text *priv_type_text) { - char *priv_type = text_to_cstring(priv_type_text); - - /* - * Return mode from priv_type string - */ - if (pg_strcasecmp(priv_type, "CREATE") == 0) - return ACL_CREATE; - if (pg_strcasecmp(priv_type, "CREATE WITH GRANT OPTION") == 0) - return ACL_GRANT_OPTION_FOR(ACL_CREATE); - - if (pg_strcasecmp(priv_type, "USAGE") == 0) - return ACL_USAGE; - if (pg_strcasecmp(priv_type, "USAGE WITH GRANT OPTION") == 0) - return ACL_GRANT_OPTION_FOR(ACL_USAGE); - - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("unrecognized privilege type: \"%s\"", priv_type))); - return ACL_NO_RIGHTS; /* keep compiler quiet */ + static const priv_map schema_priv_map[] = { + { "CREATE", ACL_CREATE }, + { "CREATE WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_CREATE) }, + { "USAGE", ACL_USAGE }, + { "USAGE WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_USAGE) }, + { NULL, 0 } + }; + + return convert_any_priv_string(priv_type_text, schema_priv_map); } + /* * has_server_privilege variants * These are all named "has_server_privilege" at the SQL level. @@ -2692,31 +3353,6 @@ convert_schema_priv_string(text *priv_type_text) */ /* - * has_server_privilege - * Check user privileges on a foreign server. - */ -static Datum -has_server_privilege(Oid roleid, Oid serverid, text *priv_type_text) -{ - AclResult aclresult; - AclMode mode = ACL_NO_RIGHTS; - char *priv_type = text_to_cstring(priv_type_text); - - if (pg_strcasecmp(priv_type, "USAGE") == 0) - mode = ACL_USAGE; - else if (pg_strcasecmp(priv_type, "USAGE WITH GRANT OPTION") == 0) - mode = ACL_GRANT_OPTION_FOR(ACL_USAGE); - else - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("unrecognized privilege type: \"%s\"", priv_type))); - - aclresult = pg_foreign_server_aclcheck(serverid, roleid, mode); - - PG_RETURN_BOOL(aclresult == ACLCHECK_OK); -} - -/* * has_server_privilege_name_name * Check user privileges on a foreign server given * name username, text servername, and text priv name. @@ -2725,12 +3361,20 @@ Datum has_server_privilege_name_name(PG_FUNCTION_ARGS) { Name username = PG_GETARG_NAME(0); - char *servername = text_to_cstring(PG_GETARG_TEXT_P(1)); + text *servername = PG_GETARG_TEXT_P(1); text *priv_type_text = PG_GETARG_TEXT_P(2); + Oid roleid; + Oid serverid; + AclMode mode; + AclResult aclresult; + + roleid = get_roleid_checked(NameStr(*username)); + serverid = convert_server_name(servername); + mode = convert_server_priv_string(priv_type_text); + + aclresult = pg_foreign_server_aclcheck(serverid, roleid, mode); - return has_server_privilege(get_roleid_checked(NameStr(*username)), - GetForeignServerOidByName(servername, false), - priv_type_text); + PG_RETURN_BOOL(aclresult == ACLCHECK_OK); } /* @@ -2742,12 +3386,20 @@ has_server_privilege_name_name(PG_FUNCTION_ARGS) Datum has_server_privilege_name(PG_FUNCTION_ARGS) { - char *servername = text_to_cstring(PG_GETARG_TEXT_P(0)); + text *servername = PG_GETARG_TEXT_P(0); text *priv_type_text = PG_GETARG_TEXT_P(1); + Oid roleid; + Oid serverid; + AclMode mode; + AclResult aclresult; + + roleid = GetUserId(); + serverid = convert_server_name(servername); + mode = convert_server_priv_string(priv_type_text); + + aclresult = pg_foreign_server_aclcheck(serverid, roleid, mode); - return has_server_privilege(GetUserId(), - GetForeignServerOidByName(servername, false), - priv_type_text); + PG_RETURN_BOOL(aclresult == ACLCHECK_OK); } /* @@ -2761,14 +3413,16 @@ has_server_privilege_name_id(PG_FUNCTION_ARGS) Name username = PG_GETARG_NAME(0); Oid serverid = PG_GETARG_OID(1); text *priv_type_text = PG_GETARG_TEXT_P(2); + Oid roleid; + AclMode mode; + AclResult aclresult; - if (!SearchSysCacheExists(FOREIGNSERVEROID, - ObjectIdGetDatum(serverid), - 0, 0, 0)) - PG_RETURN_NULL(); + roleid = get_roleid_checked(NameStr(*username)); + mode = convert_server_priv_string(priv_type_text); - return has_server_privilege(get_roleid_checked(NameStr(*username)), serverid, - priv_type_text); + aclresult = pg_foreign_server_aclcheck(serverid, roleid, mode); + + PG_RETURN_BOOL(aclresult == ACLCHECK_OK); } /* @@ -2782,13 +3436,16 @@ has_server_privilege_id(PG_FUNCTION_ARGS) { Oid serverid = PG_GETARG_OID(0); text *priv_type_text = PG_GETARG_TEXT_P(1); + Oid roleid; + AclMode mode; + AclResult aclresult; - if (!SearchSysCacheExists(FOREIGNSERVEROID, - ObjectIdGetDatum(serverid), - 0, 0, 0)) - PG_RETURN_NULL(); + roleid = GetUserId(); + mode = convert_server_priv_string(priv_type_text); + + aclresult = pg_foreign_server_aclcheck(serverid, roleid, mode); - return has_server_privilege(GetUserId(), serverid, priv_type_text); + PG_RETURN_BOOL(aclresult == ACLCHECK_OK); } /* @@ -2800,12 +3457,18 @@ Datum has_server_privilege_id_name(PG_FUNCTION_ARGS) { Oid roleid = PG_GETARG_OID(0); - char *servername = text_to_cstring(PG_GETARG_TEXT_P(1)); + text *servername = PG_GETARG_TEXT_P(1); text *priv_type_text = PG_GETARG_TEXT_P(2); + Oid serverid; + AclMode mode; + AclResult aclresult; + + serverid = convert_server_name(servername); + mode = convert_server_priv_string(priv_type_text); - return has_server_privilege(roleid, - GetForeignServerOidByName(servername, false), - priv_type_text); + aclresult = pg_foreign_server_aclcheck(serverid, roleid, mode); + + PG_RETURN_BOOL(aclresult == ACLCHECK_OK); } /* @@ -2819,13 +3482,45 @@ has_server_privilege_id_id(PG_FUNCTION_ARGS) Oid roleid = PG_GETARG_OID(0); Oid serverid = PG_GETARG_OID(1); text *priv_type_text = PG_GETARG_TEXT_P(2); + AclMode mode; + AclResult aclresult; - if (!SearchSysCacheExists(FOREIGNSERVEROID, - ObjectIdGetDatum(serverid), - 0, 0, 0)) - PG_RETURN_NULL(); + mode = convert_server_priv_string(priv_type_text); + + aclresult = pg_foreign_server_aclcheck(serverid, roleid, mode); + + PG_RETURN_BOOL(aclresult == ACLCHECK_OK); +} + +/* + * Support routines for has_server_privilege family. + */ + +/* + * Given a server name expressed as a string, look it up and return Oid + */ +static Oid +convert_server_name(text *servername) +{ + char *serverstr = text_to_cstring(servername); - return has_server_privilege(roleid, serverid, priv_type_text); + return GetForeignServerOidByName(serverstr, false); +} + +/* + * convert_server_priv_string + * Convert text string to AclMode value. + */ +static AclMode +convert_server_priv_string(text *priv_type_text) +{ + static const priv_map server_priv_map[] = { + { "USAGE", ACL_USAGE }, + { "USAGE WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_USAGE) }, + { NULL, 0 } + }; + + return convert_any_priv_string(priv_type_text, server_priv_map); } @@ -3009,20 +3704,13 @@ convert_tablespace_name(text *tablespacename) static AclMode convert_tablespace_priv_string(text *priv_type_text) { - char *priv_type = text_to_cstring(priv_type_text); - - /* - * Return mode from priv_type string - */ - if (pg_strcasecmp(priv_type, "CREATE") == 0) - return ACL_CREATE; - if (pg_strcasecmp(priv_type, "CREATE WITH GRANT OPTION") == 0) - return ACL_GRANT_OPTION_FOR(ACL_CREATE); + static const priv_map tablespace_priv_map[] = { + { "CREATE", ACL_CREATE }, + { "CREATE WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_CREATE) }, + { NULL, 0 } + }; - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("unrecognized privilege type: \"%s\"", priv_type))); - return ACL_NO_RIGHTS; /* keep compiler quiet */ + return convert_any_priv_string(priv_type_text, tablespace_priv_map); } /* @@ -3192,25 +3880,17 @@ pg_has_role_id_id(PG_FUNCTION_ARGS) static AclMode convert_role_priv_string(text *priv_type_text) { - char *priv_type = text_to_cstring(priv_type_text); - - /* - * Return mode from priv_type string - */ - if (pg_strcasecmp(priv_type, "USAGE") == 0) - return ACL_USAGE; - if (pg_strcasecmp(priv_type, "MEMBER") == 0) - return ACL_CREATE; - if (pg_strcasecmp(priv_type, "USAGE WITH GRANT OPTION") == 0 || - pg_strcasecmp(priv_type, "USAGE WITH ADMIN OPTION") == 0 || - pg_strcasecmp(priv_type, "MEMBER WITH GRANT OPTION") == 0 || - pg_strcasecmp(priv_type, "MEMBER WITH ADMIN OPTION") == 0) - return ACL_GRANT_OPTION_FOR(ACL_CREATE); - - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("unrecognized privilege type: \"%s\"", priv_type))); - return ACL_NO_RIGHTS; /* keep compiler quiet */ + static const priv_map role_priv_map[] = { + { "USAGE", ACL_USAGE }, + { "MEMBER", ACL_CREATE }, + { "USAGE WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_CREATE) }, + { "USAGE WITH ADMIN OPTION", ACL_GRANT_OPTION_FOR(ACL_CREATE) }, + { "MEMBER WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_CREATE) }, + { "MEMBER WITH ADMIN OPTION", ACL_GRANT_OPTION_FOR(ACL_CREATE) }, + { NULL, 0 } + }; + + return convert_any_priv_string(priv_type_text, role_priv_map); } /* diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index b83c948964a..1db49f6a152 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -37,7 +37,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.520 2009/01/27 12:40:15 petere Exp $ + * $PostgreSQL: pgsql/src/include/catalog/catversion.h,v 1.521 2009/02/06 21:15:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 200901271 +#define CATALOG_VERSION_NO 200902061 #endif diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h index 14db9ae4808..d657e9826c3 100644 --- a/src/include/catalog/pg_proc.h +++ b/src/include/catalog/pg_proc.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.535 2009/01/01 17:23:57 momjian Exp $ + * $PostgreSQL: pgsql/src/include/catalog/pg_proc.h,v 1.536 2009/02/06 21:15:11 tgl Exp $ * * NOTES * The script catalog/genbki.sh reads this file and generates .bki @@ -2913,6 +2913,43 @@ DESCR("current user privilege on relation by rel name"); DATA(insert OID = 1927 ( has_table_privilege PGNSP PGUID 12 1 0 0 f f f t f s 2 0 16 "26 25" _null_ _null_ _null_ _null_ has_table_privilege_id _null_ _null_ _null_ )); DESCR("current user privilege on relation by rel oid"); +DATA(insert OID = 3012 ( has_column_privilege PGNSP PGUID 12 1 0 0 f f f t f s 4 0 16 "19 25 25 25" _null_ _null_ _null_ _null_ has_column_privilege_name_name_name _null_ _null_ _null_ )); +DESCR("user privilege on column by username, rel name, col name"); +DATA(insert OID = 3013 ( has_column_privilege PGNSP PGUID 12 1 0 0 f f f t f s 4 0 16 "19 25 21 25" _null_ _null_ _null_ _null_ has_column_privilege_name_name_attnum _null_ _null_ _null_ )); +DESCR("user privilege on column by username, rel name, col attnum"); +DATA(insert OID = 3014 ( has_column_privilege PGNSP PGUID 12 1 0 0 f f f t f s 4 0 16 "19 26 25 25" _null_ _null_ _null_ _null_ has_column_privilege_name_id_name _null_ _null_ _null_ )); +DESCR("user privilege on column by username, rel oid, col name"); +DATA(insert OID = 3015 ( has_column_privilege PGNSP PGUID 12 1 0 0 f f f t f s 4 0 16 "19 26 21 25" _null_ _null_ _null_ _null_ has_column_privilege_name_id_attnum _null_ _null_ _null_ )); +DESCR("user privilege on column by username, rel oid, col attnum"); +DATA(insert OID = 3016 ( has_column_privilege PGNSP PGUID 12 1 0 0 f f f t f s 4 0 16 "26 25 25 25" _null_ _null_ _null_ _null_ has_column_privilege_id_name_name _null_ _null_ _null_ )); +DESCR("user privilege on column by user oid, rel name, col name"); +DATA(insert OID = 3017 ( has_column_privilege PGNSP PGUID 12 1 0 0 f f f t f s 4 0 16 "26 25 21 25" _null_ _null_ _null_ _null_ has_column_privilege_id_name_attnum _null_ _null_ _null_ )); +DESCR("user privilege on column by user oid, rel name, col attnum"); +DATA(insert OID = 3018 ( has_column_privilege PGNSP PGUID 12 1 0 0 f f f t f s 4 0 16 "26 26 25 25" _null_ _null_ _null_ _null_ has_column_privilege_id_id_name _null_ _null_ _null_ )); +DESCR("user privilege on column by user oid, rel oid, col name"); +DATA(insert OID = 3019 ( has_column_privilege PGNSP PGUID 12 1 0 0 f f f t f s 4 0 16 "26 26 21 25" _null_ _null_ _null_ _null_ has_column_privilege_id_id_attnum _null_ _null_ _null_ )); +DESCR("user privilege on column by user oid, rel oid, col attnum"); +DATA(insert OID = 3020 ( has_column_privilege PGNSP PGUID 12 1 0 0 f f f t f s 3 0 16 "25 25 25" _null_ _null_ _null_ _null_ has_column_privilege_name_name _null_ _null_ _null_ )); +DESCR("current user privilege on column by rel name, col name"); +DATA(insert OID = 3021 ( has_column_privilege PGNSP PGUID 12 1 0 0 f f f t f s 3 0 16 "25 21 25" _null_ _null_ _null_ _null_ has_column_privilege_name_attnum _null_ _null_ _null_ )); +DESCR("current user privilege on column by rel name, col attnum"); +DATA(insert OID = 3022 ( has_column_privilege PGNSP PGUID 12 1 0 0 f f f t f s 3 0 16 "26 25 25" _null_ _null_ _null_ _null_ has_column_privilege_id_name _null_ _null_ _null_ )); +DESCR("current user privilege on column by rel oid, col name"); +DATA(insert OID = 3023 ( has_column_privilege PGNSP PGUID 12 1 0 0 f f f t f s 3 0 16 "26 21 25" _null_ _null_ _null_ _null_ has_column_privilege_id_attnum _null_ _null_ _null_ )); +DESCR("current user privilege on column by rel oid, col attnum"); + +DATA(insert OID = 3024 ( has_any_column_privilege PGNSP PGUID 12 10 0 0 f f f t f s 3 0 16 "19 25 25" _null_ _null_ _null_ _null_ has_any_column_privilege_name_name _null_ _null_ _null_ )); +DESCR("user privilege on any column by username, rel name"); +DATA(insert OID = 3025 ( has_any_column_privilege PGNSP PGUID 12 10 0 0 f f f t f s 3 0 16 "19 26 25" _null_ _null_ _null_ _null_ has_any_column_privilege_name_id _null_ _null_ _null_ )); +DESCR("user privilege on any column by username, rel oid"); +DATA(insert OID = 3026 ( has_any_column_privilege PGNSP PGUID 12 10 0 0 f f f t f s 3 0 16 "26 25 25" _null_ _null_ _null_ _null_ has_any_column_privilege_id_name _null_ _null_ _null_ )); +DESCR("user privilege on any column by user oid, rel name"); +DATA(insert OID = 3027 ( has_any_column_privilege PGNSP PGUID 12 10 0 0 f f f t f s 3 0 16 "26 26 25" _null_ _null_ _null_ _null_ has_any_column_privilege_id_id _null_ _null_ _null_ )); +DESCR("user privilege on any column by user oid, rel oid"); +DATA(insert OID = 3028 ( has_any_column_privilege PGNSP PGUID 12 10 0 0 f f f t f s 2 0 16 "25 25" _null_ _null_ _null_ _null_ has_any_column_privilege_name _null_ _null_ _null_ )); +DESCR("current user privilege on any column by rel name"); +DATA(insert OID = 3029 ( has_any_column_privilege PGNSP PGUID 12 10 0 0 f f f t f s 2 0 16 "26 25" _null_ _null_ _null_ _null_ has_any_column_privilege_id _null_ _null_ _null_ )); +DESCR("current user privilege on any column by rel oid"); DATA(insert OID = 1928 ( pg_stat_get_numscans PGNSP PGUID 12 1 0 0 f f f t f s 1 0 20 "26" _null_ _null_ _null_ _null_ pg_stat_get_numscans _null_ _null_ _null_ )); DESCR("statistics: number of scans done for table/index"); diff --git a/src/include/utils/builtins.h b/src/include/utils/builtins.h index d9e11431521..155abf456b5 100644 --- a/src/include/utils/builtins.h +++ b/src/include/utils/builtins.h @@ -7,7 +7,7 @@ * Portions Copyright (c) 1996-2009, PostgreSQL Global Development Group * Portions Copyright (c) 1994, Regents of the University of California * - * $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.330 2009/01/01 17:24:02 momjian Exp $ + * $PostgreSQL: pgsql/src/include/utils/builtins.h,v 1.331 2009/02/06 21:15:12 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -22,6 +22,24 @@ */ /* acl.c */ +extern Datum has_any_column_privilege_name_name(PG_FUNCTION_ARGS); +extern Datum has_any_column_privilege_name_id(PG_FUNCTION_ARGS); +extern Datum has_any_column_privilege_id_name(PG_FUNCTION_ARGS); +extern Datum has_any_column_privilege_id_id(PG_FUNCTION_ARGS); +extern Datum has_any_column_privilege_name(PG_FUNCTION_ARGS); +extern Datum has_any_column_privilege_id(PG_FUNCTION_ARGS); +extern Datum has_column_privilege_name_name_name(PG_FUNCTION_ARGS); +extern Datum has_column_privilege_name_name_attnum(PG_FUNCTION_ARGS); +extern Datum has_column_privilege_name_id_name(PG_FUNCTION_ARGS); +extern Datum has_column_privilege_name_id_attnum(PG_FUNCTION_ARGS); +extern Datum has_column_privilege_id_name_name(PG_FUNCTION_ARGS); +extern Datum has_column_privilege_id_name_attnum(PG_FUNCTION_ARGS); +extern Datum has_column_privilege_id_id_name(PG_FUNCTION_ARGS); +extern Datum has_column_privilege_id_id_attnum(PG_FUNCTION_ARGS); +extern Datum has_column_privilege_name_name(PG_FUNCTION_ARGS); +extern Datum has_column_privilege_name_attnum(PG_FUNCTION_ARGS); +extern Datum has_column_privilege_id_name(PG_FUNCTION_ARGS); +extern Datum has_column_privilege_id_attnum(PG_FUNCTION_ARGS); extern Datum has_table_privilege_name_name(PG_FUNCTION_ARGS); extern Datum has_table_privilege_name_id(PG_FUNCTION_ARGS); extern Datum has_table_privilege_id_name(PG_FUNCTION_ARGS); diff --git a/src/test/regress/expected/foreign_data.out b/src/test/regress/expected/foreign_data.out index 62d060b209a..351c2d07822 100644 --- a/src/test/regress/expected/foreign_data.out +++ b/src/test/regress/expected/foreign_data.out @@ -695,8 +695,8 @@ SELECT * FROM information_schema.user_mapping_options ORDER BY lower(authorizati SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5; grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable -------------------+-----------------------+----------------+---------------+-------------+----------------------+----------------+-------------- - foreign_data_user | foreign_data_user | regression | | foo | FOREIGN DATA WRAPPER | USAGE | NO - foreign_data_user | foreign_data_user | regression | | s6 | FOREIGN SERVER | USAGE | NO + foreign_data_user | foreign_data_user | regression | | foo | FOREIGN DATA WRAPPER | USAGE | YES + foreign_data_user | foreign_data_user | regression | | s6 | FOREIGN SERVER | USAGE | YES foreign_data_user | regress_test_indirect | regression | | foo | FOREIGN DATA WRAPPER | USAGE | NO foreign_data_user | regress_test_role2 | regression | | s6 | FOREIGN SERVER | USAGE | YES (4 rows) @@ -704,8 +704,8 @@ SELECT * FROM information_schema.usage_privileges WHERE object_type LIKE 'FOREIG SELECT * FROM information_schema.role_usage_grants WHERE object_type LIKE 'FOREIGN%' ORDER BY 1, 2, 3, 4, 5; grantor | grantee | object_catalog | object_schema | object_name | object_type | privilege_type | is_grantable -------------------+-----------------------+----------------+---------------+-------------+----------------------+----------------+-------------- - foreign_data_user | foreign_data_user | regression | | foo | FOREIGN DATA WRAPPER | USAGE | NO - foreign_data_user | foreign_data_user | regression | | s6 | FOREIGN SERVER | USAGE | NO + foreign_data_user | foreign_data_user | regression | | foo | FOREIGN DATA WRAPPER | USAGE | YES + foreign_data_user | foreign_data_user | regression | | s6 | FOREIGN SERVER | USAGE | YES foreign_data_user | regress_test_indirect | regression | | foo | FOREIGN DATA WRAPPER | USAGE | NO foreign_data_user | regress_test_role2 | regression | | s6 | FOREIGN SERVER | USAGE | YES (4 rows) diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out index 7d3b44b856b..8129f5b915b 100644 --- a/src/test/regress/expected/privileges.out +++ b/src/test/regress/expected/privileges.out @@ -258,10 +258,16 @@ SELECT one FROM atest5; -- ok 1 (1 row) +COPY atest5 (one) TO stdout; -- ok +1 SELECT two FROM atest5; -- fail ERROR: permission denied for relation atest5 +COPY atest5 (two) TO stdout; -- fail +ERROR: permission denied for relation atest5 SELECT atest5 FROM atest5; -- fail ERROR: permission denied for relation atest5 +COPY atest5 (one,two) TO stdout; -- fail +ERROR: permission denied for relation atest5 SELECT 1 FROM atest5; -- ok ?column? ---------- @@ -324,6 +330,9 @@ SELECT one, two FROM atest5 NATURAL JOIN atest6; -- ok now -- test column-level privileges for INSERT and UPDATE INSERT INTO atest5 (two) VALUES (3); -- ok +COPY atest5 FROM stdin; -- fail +ERROR: permission denied for relation atest5 +COPY atest5 (two) FROM stdin; -- ok INSERT INTO atest5 (three) VALUES (4); -- fail ERROR: permission denied for relation atest5 INSERT INTO atest5 VALUES (5,5,5); -- fail @@ -346,6 +355,7 @@ SELECT atest6 FROM atest6; -- ok -------- (0 rows) +COPY atest6 TO stdout; -- ok -- test column-level privileges when involved with DELETE SET SESSION AUTHORIZATION regressuser1; ALTER TABLE atest6 ADD COLUMN three integer; diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 2daa79d732d..2667a13e44e 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1276,8 +1276,8 @@ drop table cchild; -- Check that ruleutils are working -- SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schema' ORDER BY viewname; - viewname | definition ---------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- + viewname | definition +--------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- iexit | SELECT ih.name, ih.thepath, interpt_pp(ih.thepath, r.thepath) AS exit FROM ihighway ih, ramp r WHERE (ih.thepath ## r.thepath); pg_cursors | SELECT c.name, c.statement, c.is_holdable, c.is_binary, c.is_scrollable, c.creation_time FROM pg_cursor() c(name, statement, is_holdable, is_binary, is_scrollable, creation_time); pg_group | SELECT pg_authid.rolname AS groname, pg_authid.oid AS grosysid, ARRAY(SELECT pg_auth_members.member FROM pg_auth_members WHERE (pg_auth_members.roleid = pg_authid.oid)) AS grolist FROM pg_authid WHERE (NOT pg_authid.rolcanlogin); @@ -1308,7 +1308,7 @@ SELECT viewname, definition FROM pg_views WHERE schemaname <> 'information_schem pg_statio_user_indexes | SELECT pg_statio_all_indexes.relid, pg_statio_all_indexes.indexrelid, pg_statio_all_indexes.schemaname, pg_statio_all_indexes.relname, pg_statio_all_indexes.indexrelname, pg_statio_all_indexes.idx_blks_read, pg_statio_all_indexes.idx_blks_hit FROM pg_statio_all_indexes WHERE ((pg_statio_all_indexes.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_indexes.schemaname !~ '^pg_toast'::text)); pg_statio_user_sequences | SELECT pg_statio_all_sequences.relid, pg_statio_all_sequences.schemaname, pg_statio_all_sequences.relname, pg_statio_all_sequences.blks_read, pg_statio_all_sequences.blks_hit FROM pg_statio_all_sequences WHERE ((pg_statio_all_sequences.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_sequences.schemaname !~ '^pg_toast'::text)); pg_statio_user_tables | SELECT pg_statio_all_tables.relid, pg_statio_all_tables.schemaname, pg_statio_all_tables.relname, pg_statio_all_tables.heap_blks_read, pg_statio_all_tables.heap_blks_hit, pg_statio_all_tables.idx_blks_read, pg_statio_all_tables.idx_blks_hit, pg_statio_all_tables.toast_blks_read, pg_statio_all_tables.toast_blks_hit, pg_statio_all_tables.tidx_blks_read, pg_statio_all_tables.tidx_blks_hit FROM pg_statio_all_tables WHERE ((pg_statio_all_tables.schemaname <> ALL (ARRAY['pg_catalog'::name, 'information_schema'::name])) AND (pg_statio_all_tables.schemaname !~ '^pg_toast'::text)); - pg_stats | SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, s.stanullfrac AS null_frac, s.stawidth AS avg_width, s.stadistinct AS n_distinct, CASE WHEN (s.stakind1 = ANY (ARRAY[1, 4])) THEN s.stavalues1 WHEN (s.stakind2 = ANY (ARRAY[1, 4])) THEN s.stavalues2 WHEN (s.stakind3 = ANY (ARRAY[1, 4])) THEN s.stavalues3 WHEN (s.stakind4 = ANY (ARRAY[1, 4])) THEN s.stavalues4 ELSE NULL::anyarray END AS most_common_vals, CASE WHEN (s.stakind1 = ANY (ARRAY[1, 4])) THEN s.stanumbers1 WHEN (s.stakind2 = ANY (ARRAY[1, 4])) THEN s.stanumbers2 WHEN (s.stakind3 = ANY (ARRAY[1, 4])) THEN s.stanumbers3 WHEN (s.stakind4 = ANY (ARRAY[1, 4])) THEN s.stanumbers4 ELSE NULL::real[] END AS most_common_freqs, CASE WHEN (s.stakind1 = 2) THEN s.stavalues1 WHEN (s.stakind2 = 2) THEN s.stavalues2 WHEN (s.stakind3 = 2) THEN s.stavalues3 WHEN (s.stakind4 = 2) THEN s.stavalues4 ELSE NULL::anyarray END AS histogram_bounds, CASE WHEN (s.stakind1 = 3) THEN s.stanumbers1[1] WHEN (s.stakind2 = 3) THEN s.stanumbers2[1] WHEN (s.stakind3 = 3) THEN s.stanumbers3[1] WHEN (s.stakind4 = 3) THEN s.stanumbers4[1] ELSE NULL::real END AS correlation FROM (((pg_statistic s JOIN pg_class c ON ((c.oid = s.starelid))) JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE has_table_privilege(c.oid, 'select'::text); + pg_stats | SELECT n.nspname AS schemaname, c.relname AS tablename, a.attname, s.stanullfrac AS null_frac, s.stawidth AS avg_width, s.stadistinct AS n_distinct, CASE WHEN (s.stakind1 = ANY (ARRAY[1, 4])) THEN s.stavalues1 WHEN (s.stakind2 = ANY (ARRAY[1, 4])) THEN s.stavalues2 WHEN (s.stakind3 = ANY (ARRAY[1, 4])) THEN s.stavalues3 WHEN (s.stakind4 = ANY (ARRAY[1, 4])) THEN s.stavalues4 ELSE NULL::anyarray END AS most_common_vals, CASE WHEN (s.stakind1 = ANY (ARRAY[1, 4])) THEN s.stanumbers1 WHEN (s.stakind2 = ANY (ARRAY[1, 4])) THEN s.stanumbers2 WHEN (s.stakind3 = ANY (ARRAY[1, 4])) THEN s.stanumbers3 WHEN (s.stakind4 = ANY (ARRAY[1, 4])) THEN s.stanumbers4 ELSE NULL::real[] END AS most_common_freqs, CASE WHEN (s.stakind1 = 2) THEN s.stavalues1 WHEN (s.stakind2 = 2) THEN s.stavalues2 WHEN (s.stakind3 = 2) THEN s.stavalues3 WHEN (s.stakind4 = 2) THEN s.stavalues4 ELSE NULL::anyarray END AS histogram_bounds, CASE WHEN (s.stakind1 = 3) THEN s.stanumbers1[1] WHEN (s.stakind2 = 3) THEN s.stanumbers2[1] WHEN (s.stakind3 = 3) THEN s.stanumbers3[1] WHEN (s.stakind4 = 3) THEN s.stanumbers4[1] ELSE NULL::real END AS correlation FROM (((pg_statistic s JOIN pg_class c ON ((c.oid = s.starelid))) JOIN pg_attribute a ON (((c.oid = a.attrelid) AND (a.attnum = s.staattnum)))) LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) WHERE ((NOT a.attisdropped) AND has_column_privilege(c.oid, a.attnum, 'select'::text)); pg_tables | SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, t.spcname AS tablespace, c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.relhastriggers AS hastriggers FROM ((pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace))) WHERE (c.relkind = 'r'::"char"); pg_timezone_abbrevs | SELECT pg_timezone_abbrevs.abbrev, pg_timezone_abbrevs.utc_offset, pg_timezone_abbrevs.is_dst FROM pg_timezone_abbrevs() pg_timezone_abbrevs(abbrev, utc_offset, is_dst); pg_timezone_names | SELECT pg_timezone_names.name, pg_timezone_names.abbrev, pg_timezone_names.utc_offset, pg_timezone_names.is_dst FROM pg_timezone_names() pg_timezone_names(name, abbrev, utc_offset, is_dst); diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql index dda20db8556..2316d116162 100644 --- a/src/test/regress/sql/privileges.sql +++ b/src/test/regress/sql/privileges.sql @@ -184,8 +184,11 @@ INSERT INTO atest5 VALUES (1,2,3); SET SESSION AUTHORIZATION regressuser4; SELECT * FROM atest5; -- fail SELECT one FROM atest5; -- ok +COPY atest5 (one) TO stdout; -- ok SELECT two FROM atest5; -- fail +COPY atest5 (two) TO stdout; -- fail SELECT atest5 FROM atest5; -- fail +COPY atest5 (one,two) TO stdout; -- fail SELECT 1 FROM atest5; -- ok SELECT 1 FROM atest5 a JOIN atest5 b USING (one); -- ok SELECT 1 FROM atest5 a JOIN atest5 b USING (two); -- fail @@ -213,6 +216,10 @@ SELECT one, two FROM atest5 NATURAL JOIN atest6; -- ok now -- test column-level privileges for INSERT and UPDATE INSERT INTO atest5 (two) VALUES (3); -- ok +COPY atest5 FROM stdin; -- fail +COPY atest5 (two) FROM stdin; -- ok +1 +\. INSERT INTO atest5 (three) VALUES (4); -- fail INSERT INTO atest5 VALUES (5,5,5); -- fail UPDATE atest5 SET three = 10; -- ok @@ -227,6 +234,7 @@ SET SESSION AUTHORIZATION regressuser4; SELECT one FROM atest5; -- fail UPDATE atest5 SET one = 1; -- fail SELECT atest6 FROM atest6; -- ok +COPY atest6 TO stdout; -- ok -- test column-level privileges when involved with DELETE SET SESSION AUTHORIZATION regressuser1; |