From 7449427a1e6a099bc7e76164cb99a01d5e87237b Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Fri, 6 Feb 2009 21:15:12 +0000 Subject: [PATCH] Clean up some loose ends from the column privileges patch: add has_column_privilege and has_any_column_privilege SQL functions; fix the information_schema views that are supposed to pay attention to column privileges; adjust pg_stats to show stats for any column you have select privilege on; and fix COPY to allow copying a subset of columns if the user has suitable per-column privileges for all the columns. To improve efficiency of some of the information_schema views, extend the has_xxx_privilege functions to allow inquiring about the OR of a set of privileges in just one call. This is just exposing capability that already existed in the underlying aclcheck routines. In passing, make the information_schema views report the owner's own privileges as being grantable, since Postgres assumes this even when the grant option bit is not set in the ACL. This is a longstanding oversight. Also, make the new has_xxx_privilege functions for foreign data objects follow the same coding conventions used by the older ones. Stephen Frost and Tom Lane --- doc/src/sgml/func.sgml | 135 +- doc/src/sgml/information_schema.sgml | 29 +- src/backend/catalog/aclchk.c | 99 +- src/backend/catalog/information_schema.sql | 212 +-- src/backend/catalog/system_views.sql | 4 +- src/backend/commands/copy.c | 39 +- src/backend/utils/adt/acl.c | 1376 +++++++++++++++----- src/include/catalog/catversion.h | 4 +- src/include/catalog/pg_proc.h | 39 +- src/include/utils/builtins.h | 20 +- src/test/regress/expected/foreign_data.out | 8 +- src/test/regress/expected/privileges.out | 10 + src/test/regress/expected/rules.out | 6 +- src/test/regress/sql/privileges.sql | 8 + 14 files changed, 1428 insertions(+), 561 deletions(-) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 341a37fb71..0f8635dc5e 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -1,4 +1,4 @@ - + Functions and Operators @@ -11599,6 +11599,38 @@ SET search_path TO schema , schema, .. + + has_any_column_privilege(user, + table, + privilege) + + boolean + does user have privilege for any column of table + + + has_any_column_privilege(table, + privilege) + + boolean + does current user have privilege for any column of table + + + has_column_privilege(user, + table, + column, + privilege) + + boolean + does user have privilege for column + + + has_column_privilege(table, + column, + privilege) + + boolean + does current user have privilege for column + has_database_privilege(user, database, @@ -11738,6 +11770,12 @@ SET search_path TO schema , schema, .. + + has_any_column_privilege + + + has_column_privilege + has_database_privilege @@ -11766,11 +11804,71 @@ SET search_path TO schema , schema, .. pg_has_role + + has_table_privilege checks whether a user + can access a table in a particular way. The user can be + specified by name or by OID + (pg_authid.oid), or if the argument is + omitted + current_user is assumed. The table can be specified + by name or by OID. (Thus, there are actually six variants of + has_table_privilege, which can be distinguished by + the number and types of their arguments.) When specifying by name, + the name can be schema-qualified if necessary. + The desired access privilege type + is specified by a text string, which must evaluate to one of the + values SELECT, INSERT, + UPDATE, DELETE, TRUNCATE, + REFERENCES, or TRIGGER. Optionally, + WITH GRANT OPTION can be added to a privilege type to test + whether the privilege is held with grant option. Also, multiple privilege + types can be listed separated by commas, in which case the result will + be true if any of the listed privileges is held. + (Case of the privilege string is not significant, and extra whitespace + is allowed between but not within privilege names.) + Some examples: + +SELECT has_table_privilege('myschema.mytable', 'select'); +SELECT has_table_privilege('joe', 'mytable', 'INSERT, SELECT WITH GRANT OPTION'); + + + + + has_any_column_privilege checks whether a user can + access any column of a table in a particular way. The possibilities for + its arguments are the same as for has_table_privilege, + except that the desired access privilege type must evaluate to some + combination of + SELECT, + INSERT, + UPDATE, or + REFERENCES. Note that having any of these privileges + at the table level implicitly grants it for each column of the table, + so has_any_column_privilege will always return + true if has_table_privilege does for the same + arguments. But has_any_column_privilege also succeeds if + there is a column-level grant of the privilege for at least one column. + + + + has_column_privilege checks whether a user + can access a column in a particular way. The possibilities for its + arguments are analogous to has_table_privilege, + with the addition that the column can be specified either by name + or attribute number. + The desired access privilege type must evaluate to some combination of + SELECT, + INSERT, + UPDATE, or + REFERENCES. Note that having any of these privileges + at the table level implicitly grants it for each column of the table. + + has_database_privilege checks whether a user can access a database in a particular way. The possibilities for its arguments are analogous to has_table_privilege. - The desired access privilege type must evaluate to + The desired access privilege type must evaluate to some combination of CREATE, CONNECT, TEMPORARY, or @@ -11813,7 +11911,7 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute'); has_schema_privilege checks whether a user can access a schema in a particular way. The possibilities for its arguments are analogous to has_table_privilege. - The desired access privilege type must evaluate to + The desired access privilege type must evaluate to some combination of CREATE or USAGE. @@ -11826,29 +11924,6 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute'); USAGE. - - has_table_privilege checks whether a user - can access a table in a particular way. The user can be - specified by name or by OID - (pg_authid.oid), or if the argument is - omitted - current_user is assumed. The table can be specified - by name or by OID. (Thus, there are actually six variants of - has_table_privilege, which can be distinguished by - the number and types of their arguments.) When specifying by name, - the name can be schema-qualified if necessary. - The desired access privilege type - is specified by a text string, which must evaluate to one of the - values SELECT, INSERT, - UPDATE, DELETE, TRUNCATE, - REFERENCES, or TRIGGER. - (Case of the string is not significant, however.) - An example is: - -SELECT has_table_privilege('myschema.mytable', 'select'); - - - has_tablespace_privilege checks whether a user can access a tablespace in a particular way. The possibilities for its @@ -11861,7 +11936,7 @@ SELECT has_table_privilege('myschema.mytable', 'select'); pg_has_role checks whether a user can access a role in a particular way. The possibilities for its arguments are analogous to has_table_privilege. - The desired access privilege type must evaluate to + The desired access privilege type must evaluate to some combination of MEMBER or USAGE. MEMBER denotes direct or indirect membership in @@ -11870,12 +11945,6 @@ SELECT has_table_privilege('myschema.mytable', 'select'); are immediately available without doing SET ROLE. - - To test whether a user holds a grant option on the privilege, - append WITH GRANT OPTION to the privilege key - word; for example 'UPDATE WITH GRANT OPTION'. - - shows functions that determine whether a certain object is visible in the diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index 96cbf1f12a..06ccb6d2de 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -1,4 +1,4 @@ - + The Information Schema @@ -686,18 +686,11 @@ - In PostgreSQL, you can only grant - privileges on entire tables, not individual columns. Therefore, - this view contains the same information as - table_privileges, just represented through one - row for each column in each appropriate table, but it only covers + If a privilege has been granted on an entire table, it will show up in + this view as a grant for each column, but only for the privilege types where column granularity is possible: SELECT, INSERT, - UPDATE, REFERENCES. If you - want to make your applications fit for possible future - developments, it is generally the right choice to use this view - instead of table_privileges if one of those - privilege types is concerned. + UPDATE, REFERENCES. @@ -2727,8 +2720,10 @@ ORDER BY c.ordinal_position; The view referential_constraints contains all - referential (foreign key) constraints in the current database that - belong to a table owned by a currently enabled role. + referential (foreign key) constraints in the current database. + Only those constraints are shown for which the current user has + write access to the referencing table (by way of being the + owner or having some privilege other than SELECT).
@@ -3157,8 +3152,8 @@ ORDER BY c.ordinal_position; The view routine_privileges identifies all - privileges granted to a currently enabled role or by a currently - enabled role. There is one row for each combination of function, + privileges granted on functions to a currently enabled role or by a + currently enabled role. There is one row for each combination of function, grantor, and grantee. @@ -4500,7 +4495,7 @@ ORDER BY c.ordinal_position; The view table_constraints contains all constraints belonging to tables that the current user owns or has - some privilege on. + some non-SELECT privilege on.
@@ -4777,7 +4772,7 @@ ORDER BY c.ordinal_position; The view triggers contains all triggers defined in the current database on tables that the current user owns or has - some privilege on. + some non-SELECT privilege on.
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c index b49c80e485..2924dddf62 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 470a454f69..d03083aac3 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 33c3210819..897d930bfe 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 c2e2c82205..550e7e661f 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 cb0ebf4694..64d55b6b1c 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); @@ -1419,6 +1433,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. @@ -1610,352 +1681,923 @@ convert_table_name(text *tablename) static AclMode convert_table_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, "SELECT") == 0) - return ACL_SELECT; - if (pg_strcasecmp(priv_type, "SELECT WITH GRANT OPTION") == 0) - return ACL_GRANT_OPTION_FOR(ACL_SELECT); - - 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); - - 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 (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); - - 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); - - 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); - - 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); - - 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; - - 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 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_database_privilege variants - * These are all named "has_database_privilege" at the SQL level. - * They take various combinations of database name, database OID, + * 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, false if not, or NULL if object doesn't exist. + * 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_database_privilege_name_name - * Check user privileges on a database given - * name username, text databasename, and text priv name. + * 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_database_privilege_name_name(PG_FUNCTION_ARGS) +has_any_column_privilege_name_name(PG_FUNCTION_ARGS) { - Name username = PG_GETARG_NAME(0); - text *databasename = PG_GETARG_TEXT_P(1); + 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 databaseoid; + Oid tableoid; AclMode mode; AclResult aclresult; - roleid = get_roleid_checked(NameStr(*username)); - databaseoid = convert_database_name(databasename); - mode = convert_database_priv_string(priv_type_text); + roleid = get_roleid_checked(NameStr(*rolename)); + tableoid = convert_table_name(tablename); + mode = convert_column_priv_string(priv_type_text); - aclresult = pg_database_aclcheck(databaseoid, roleid, mode); + /* 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_database_privilege_name - * Check user privileges on a database given - * text databasename and text priv name. + * 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_database_privilege_name(PG_FUNCTION_ARGS) +has_any_column_privilege_name(PG_FUNCTION_ARGS) { - text *databasename = PG_GETARG_TEXT_P(0); + text *tablename = PG_GETARG_TEXT_P(0); text *priv_type_text = PG_GETARG_TEXT_P(1); Oid roleid; - Oid databaseoid; + Oid tableoid; AclMode mode; AclResult aclresult; roleid = GetUserId(); - databaseoid = convert_database_name(databasename); - mode = convert_database_priv_string(priv_type_text); + tableoid = convert_table_name(tablename); + mode = convert_column_priv_string(priv_type_text); - aclresult = pg_database_aclcheck(databaseoid, roleid, mode); + /* 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_database_privilege_name_id - * Check user privileges on a database given - * name usename, database oid, and text priv name. + * 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_database_privilege_name_id(PG_FUNCTION_ARGS) +has_any_column_privilege_name_id(PG_FUNCTION_ARGS) { Name username = PG_GETARG_NAME(0); - Oid databaseoid = PG_GETARG_OID(1); + 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_database_priv_string(priv_type_text); + mode = convert_column_priv_string(priv_type_text); - if (!SearchSysCacheExists(DATABASEOID, - ObjectIdGetDatum(databaseoid), + if (!SearchSysCacheExists(RELOID, + ObjectIdGetDatum(tableoid), 0, 0, 0)) PG_RETURN_NULL(); - aclresult = pg_database_aclcheck(databaseoid, roleid, mode); + /* 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_database_privilege_id - * Check user privileges on a database given - * database oid, and text priv name. + * 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_database_privilege_id(PG_FUNCTION_ARGS) +has_any_column_privilege_id(PG_FUNCTION_ARGS) { - Oid databaseoid = PG_GETARG_OID(0); + 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_database_priv_string(priv_type_text); + mode = convert_column_priv_string(priv_type_text); - if (!SearchSysCacheExists(DATABASEOID, - ObjectIdGetDatum(databaseoid), + if (!SearchSysCacheExists(RELOID, + ObjectIdGetDatum(tableoid), 0, 0, 0)) PG_RETURN_NULL(); - aclresult = pg_database_aclcheck(databaseoid, roleid, mode); + /* 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_database_privilege_id_name - * Check user privileges on a database given - * roleid, text databasename, and text priv name. + * 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_database_privilege_id_name(PG_FUNCTION_ARGS) +has_any_column_privilege_id_name(PG_FUNCTION_ARGS) { Oid roleid = PG_GETARG_OID(0); - text *databasename = PG_GETARG_TEXT_P(1); + text *tablename = PG_GETARG_TEXT_P(1); text *priv_type_text = PG_GETARG_TEXT_P(2); - Oid databaseoid; + Oid tableoid; AclMode mode; AclResult aclresult; - databaseoid = convert_database_name(databasename); - mode = convert_database_priv_string(priv_type_text); + tableoid = convert_table_name(tablename); + mode = convert_column_priv_string(priv_type_text); - aclresult = pg_database_aclcheck(databaseoid, roleid, mode); + /* 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_database_privilege_id_id - * Check user privileges on a database given - * roleid, database oid, and text priv name. + * 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_database_privilege_id_id(PG_FUNCTION_ARGS) +has_any_column_privilege_id_id(PG_FUNCTION_ARGS) { Oid roleid = PG_GETARG_OID(0); - Oid databaseoid = PG_GETARG_OID(1); + Oid tableoid = PG_GETARG_OID(1); text *priv_type_text = PG_GETARG_TEXT_P(2); AclMode mode; AclResult aclresult; - mode = convert_database_priv_string(priv_type_text); + mode = convert_column_priv_string(priv_type_text); - if (!SearchSysCacheExists(DATABASEOID, - ObjectIdGetDatum(databaseoid), + if (!SearchSysCacheExists(RELOID, + ObjectIdGetDatum(tableoid), 0, 0, 0)) PG_RETURN_NULL(); - aclresult = pg_database_aclcheck(databaseoid, roleid, mode); + /* 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); } -/* - * Support routines for has_database_privilege family. - */ /* - * Given a database name expressed as a string, look it up and return Oid + * 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. */ -static Oid -convert_database_name(text *databasename) -{ - char *dbname = text_to_cstring(databasename); - Oid oid; - - oid = get_database_oid(dbname); - if (!OidIsValid(oid)) - ereport(ERROR, - (errcode(ERRCODE_UNDEFINED_DATABASE), - errmsg("database \"%s\" does not exist", dbname))); - - return oid; -} /* - * convert_database_priv_string - * Convert text string to AclMode value. + * 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 AclMode -convert_database_priv_string(text *priv_type_text) +static int +column_privilege_check(Oid tableoid, AttrNumber attnum, + Oid roleid, AclMode mode) { - char *priv_type = text_to_cstring(priv_type_text); + 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, "CREATE") == 0) - return ACL_CREATE; - if (pg_strcasecmp(priv_type, "CREATE WITH GRANT OPTION") == 0) - return ACL_GRANT_OPTION_FOR(ACL_CREATE); + if (!SearchSysCacheExists(RELOID, + ObjectIdGetDatum(tableoid), + 0, 0, 0)) + return -1; - 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); + aclresult = pg_class_aclcheck(tableoid, roleid, mode); - 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 (aclresult == ACLCHECK_OK) + return true; - 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); + /* + * 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); - ereport(ERROR, - (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("unrecognized privilege type: \"%s\"", priv_type))); - return ACL_NO_RIGHTS; /* keep compiler quiet */ -} + aclresult = pg_attribute_aclcheck(tableoid, attnum, roleid, mode); + return (aclresult == ACLCHECK_OK); +} /* - * has_foreign_data_wrapper_privilege variants - * These are all named "has_foreign_data_wrapper_privilege" at the SQL level. - * They take various combinations of foreign-data wrapper name, - * 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_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; + + 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); + + privresult = column_privilege_check(tableoid, colattnum, roleid, mode); + if (privresult < 0) + PG_RETURN_NULL(); + PG_RETURN_BOOL(privresult); +} /* - * has_foreign_data_wrapper_privilege - * Check user privileges on a foreign-data wrapper. + * has_column_privilege_name_name_attnum + * Check user privileges on a column given + * name username, text tablename, int attnum, and text priv name. */ -static Datum -has_foreign_data_wrapper_privilege(Oid roleid, Oid fdwid, text *priv_type_text) +Datum +has_column_privilege_name_name_attnum(PG_FUNCTION_ARGS) { - 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))); + 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; - aclresult = pg_foreign_data_wrapper_aclcheck(fdwid, roleid, mode); + roleid = get_roleid_checked(NameStr(*rolename)); + tableoid = convert_table_name(tablename); + mode = convert_column_priv_string(priv_type_text); - PG_RETURN_BOOL(aclresult == ACLCHECK_OK); + privresult = column_privilege_check(tableoid, colattnum, roleid, mode); + if (privresult < 0) + PG_RETURN_NULL(); + PG_RETURN_BOOL(privresult); } /* - * has_foreign_data_wrapper_privilege_name_name - * Check user privileges on a foreign-data wrapper given - * name username, text fdwname, and text priv name. + * 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_foreign_data_wrapper_privilege_name_name(PG_FUNCTION_ARGS) +has_column_privilege_name_id_name(PG_FUNCTION_ARGS) { Name username = PG_GETARG_NAME(0); - char *fdwname = text_to_cstring(PG_GETARG_TEXT_P(1)); - text *priv_type_text = PG_GETARG_TEXT_P(2); + 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); - return has_foreign_data_wrapper_privilege(get_roleid_checked(NameStr(*username)), - GetForeignDataWrapperOidByName(fdwname, false), - priv_type_text); + privresult = column_privilege_check(tableoid, colattnum, roleid, mode); + if (privresult < 0) + PG_RETURN_NULL(); + PG_RETURN_BOOL(privresult); } /* - * has_foreign_data_wrapper_privilege_name - * Check user privileges on a foreign-data wrapper given - * text fdwname and text priv name. + * 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); +} + + +/* + * has_database_privilege variants + * These are all named "has_database_privilege" at the SQL level. + * They take various combinations of database name, database 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, or NULL if object doesn't exist. + */ + +/* + * has_database_privilege_name_name + * Check user privileges on a database given + * name username, text databasename, and text priv name. + */ +Datum +has_database_privilege_name_name(PG_FUNCTION_ARGS) +{ + Name username = PG_GETARG_NAME(0); + text *databasename = PG_GETARG_TEXT_P(1); + text *priv_type_text = PG_GETARG_TEXT_P(2); + Oid roleid; + Oid databaseoid; + AclMode mode; + AclResult aclresult; + + roleid = get_roleid_checked(NameStr(*username)); + databaseoid = convert_database_name(databasename); + mode = convert_database_priv_string(priv_type_text); + + aclresult = pg_database_aclcheck(databaseoid, roleid, mode); + + PG_RETURN_BOOL(aclresult == ACLCHECK_OK); +} + +/* + * has_database_privilege_name + * Check user privileges on a database given + * text databasename and text priv name. + * current_user is assumed + */ +Datum +has_database_privilege_name(PG_FUNCTION_ARGS) +{ + text *databasename = PG_GETARG_TEXT_P(0); + text *priv_type_text = PG_GETARG_TEXT_P(1); + Oid roleid; + Oid databaseoid; + AclMode mode; + AclResult aclresult; + + roleid = GetUserId(); + databaseoid = convert_database_name(databasename); + mode = convert_database_priv_string(priv_type_text); + + aclresult = pg_database_aclcheck(databaseoid, roleid, mode); + + PG_RETURN_BOOL(aclresult == ACLCHECK_OK); +} + +/* + * has_database_privilege_name_id + * Check user privileges on a database given + * name usename, database oid, and text priv name. + */ +Datum +has_database_privilege_name_id(PG_FUNCTION_ARGS) +{ + Name username = PG_GETARG_NAME(0); + Oid databaseoid = 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_database_priv_string(priv_type_text); + + if (!SearchSysCacheExists(DATABASEOID, + ObjectIdGetDatum(databaseoid), + 0, 0, 0)) + PG_RETURN_NULL(); + + aclresult = pg_database_aclcheck(databaseoid, roleid, mode); + + PG_RETURN_BOOL(aclresult == ACLCHECK_OK); +} + +/* + * has_database_privilege_id + * Check user privileges on a database given + * database oid, and text priv name. + * current_user is assumed + */ +Datum +has_database_privilege_id(PG_FUNCTION_ARGS) +{ + Oid databaseoid = PG_GETARG_OID(0); + text *priv_type_text = PG_GETARG_TEXT_P(1); + Oid roleid; + AclMode mode; + AclResult aclresult; + + roleid = GetUserId(); + mode = convert_database_priv_string(priv_type_text); + + if (!SearchSysCacheExists(DATABASEOID, + ObjectIdGetDatum(databaseoid), + 0, 0, 0)) + PG_RETURN_NULL(); + + aclresult = pg_database_aclcheck(databaseoid, roleid, mode); + + PG_RETURN_BOOL(aclresult == ACLCHECK_OK); +} + +/* + * has_database_privilege_id_name + * Check user privileges on a database given + * roleid, text databasename, and text priv name. + */ +Datum +has_database_privilege_id_name(PG_FUNCTION_ARGS) +{ + Oid roleid = PG_GETARG_OID(0); + text *databasename = PG_GETARG_TEXT_P(1); + text *priv_type_text = PG_GETARG_TEXT_P(2); + Oid databaseoid; + AclMode mode; + AclResult aclresult; + + databaseoid = convert_database_name(databasename); + mode = convert_database_priv_string(priv_type_text); + + aclresult = pg_database_aclcheck(databaseoid, roleid, mode); + + PG_RETURN_BOOL(aclresult == ACLCHECK_OK); +} + +/* + * has_database_privilege_id_id + * Check user privileges on a database given + * roleid, database oid, and text priv name. + */ +Datum +has_database_privilege_id_id(PG_FUNCTION_ARGS) +{ + Oid roleid = PG_GETARG_OID(0); + Oid databaseoid = PG_GETARG_OID(1); + text *priv_type_text = PG_GETARG_TEXT_P(2); + AclMode mode; + AclResult aclresult; + + mode = convert_database_priv_string(priv_type_text); + + if (!SearchSysCacheExists(DATABASEOID, + ObjectIdGetDatum(databaseoid), + 0, 0, 0)) + PG_RETURN_NULL(); + + aclresult = pg_database_aclcheck(databaseoid, roleid, mode); + + PG_RETURN_BOOL(aclresult == ACLCHECK_OK); +} + +/* + * Support routines for has_database_privilege family. + */ + +/* + * Given a database name expressed as a string, look it up and return Oid + */ +static Oid +convert_database_name(text *databasename) +{ + char *dbname = text_to_cstring(databasename); + Oid oid; + + oid = get_database_oid(dbname); + if (!OidIsValid(oid)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_DATABASE), + errmsg("database \"%s\" does not exist", dbname))); + + return oid; +} + +/* + * convert_database_priv_string + * Convert text string to AclMode value. + */ +static AclMode +convert_database_priv_string(text *priv_type_text) +{ + 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); + +} + + +/* + * has_foreign_data_wrapper_privilege variants + * These are all named "has_foreign_data_wrapper_privilege" at the SQL level. + * They take various combinations of foreign-data wrapper name, + * 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. + */ + +/* + * has_foreign_data_wrapper_privilege_name_name + * Check user privileges on a foreign-data wrapper given + * name username, text fdwname, and text priv name. + */ +Datum +has_foreign_data_wrapper_privilege_name_name(PG_FUNCTION_ARGS) +{ + Name username = PG_GETARG_NAME(0); + text *fdwname = PG_GETARG_TEXT_P(1); + text *priv_type_text = PG_GETARG_TEXT_P(2); + Oid roleid; + Oid fdwid; + AclMode mode; + AclResult aclresult; + + 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); +} + +/* + * has_foreign_data_wrapper_privilege_name + * Check user privileges on a foreign-data wrapper given + * text fdwname and text priv name. * current_user is assumed */ 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; - return has_foreign_data_wrapper_privilege(GetUserId(), - GetForeignDataWrapperOidByName(fdwname, false), - priv_type_text); + roleid = GetUserId(); + 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); } /* @@ -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); - return has_foreign_data_wrapper_privilege(GetUserId(), fdwid, - priv_type_text); + aclresult = pg_foreign_data_wrapper_aclcheck(fdwid, roleid, mode); + + 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); +} - return has_foreign_data_wrapper_privilege(roleid, fdwid, priv_type_text); +/* + * 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 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. @@ -2691,31 +3352,6 @@ convert_schema_priv_string(text *priv_type_text) * privilege, false if not. */ -/* - * 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 @@ -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); + + aclresult = pg_foreign_server_aclcheck(serverid, roleid, mode); - return has_server_privilege(get_roleid_checked(NameStr(*username)), serverid, - priv_type_text); + 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); - return has_server_privilege(GetUserId(), serverid, priv_type_text); + aclresult = pg_foreign_server_aclcheck(serverid, roleid, mode); + + 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); + + aclresult = pg_foreign_server_aclcheck(serverid, roleid, mode); - return has_server_privilege(roleid, - GetForeignServerOidByName(servername, false), - priv_type_text); + 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 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 has_server_privilege(roleid, serverid, priv_type_text); + 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 b83c948964..1db49f6a15 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 14db9ae480..d657e9826c 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 d9e1143152..155abf456b 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 62d060b209..351c2d0782 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 7d3b44b856..8129f5b915 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 2daa79d732..2667a13e44 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 dda20db855..2316d11616 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; -- 2.40.0