Hi,
I found that has_table_privilege returns an error when a table is specified
by schema-qualified name and the user doen't have privilege for its schema.
postgres=> select has_table_privilege('myschema.tbl','select');
ERROR: permission denied for schema myschema
I think that this function should return false because the user doesn't have
the privilege on this table eventually. It is more useful for users because
it is not needed to parse the schema-qualified table name and check the
privilege on the schema in advance.
Attached is a patch to modify the function like that. This is WIP patch, so
only has_table_previlege is modified and other familiy functions are left as
they are. Also, there is no additional test yet.
One consern on this patch is that modifying the function can break the
back-compatibility, so it might be better to add a new parameter to
control the behavior of the function.
Any comments would be appriciated.
Regards,
--
Yugo Nagata <nag...@sraoss.co.jp>
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index a45e093de7..6628385277 100644
--- a/src/backend/utils/adt/acl.c
+++ b/src/backend/utils/adt/acl.c
@@ -95,7 +95,9 @@ static AclMode convert_any_priv_string(text *priv_type_text,
const priv_map *privileges);
static Oid convert_table_name(text *tablename);
+static Oid convert_table_schema_name(text *tablename);
static AclMode convert_table_priv_string(text *priv_type_text);
+static AclMode convert_table_schema_priv_string(text *priv_type_text);
static AclMode convert_sequence_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);
@@ -1856,10 +1858,19 @@ has_table_privilege_name_name(PG_FUNCTION_ARGS)
text *priv_type_text = PG_GETARG_TEXT_PP(2);
Oid roleid;
Oid tableoid;
+ Oid schemaoid;
AclMode mode;
AclResult aclresult;
roleid = get_role_oid_or_public(NameStr(*rolename));
+
+ schemaoid = convert_table_schema_name(tablename);
+ mode = convert_table_schema_priv_string(priv_type_text);
+
+ aclresult = pg_namespace_aclcheck(schemaoid, roleid, mode);
+ if (aclresult != ACLCHECK_OK)
+ PG_RETURN_BOOL(false);
+
tableoid = convert_table_name(tablename);
mode = convert_table_priv_string(priv_type_text);
@@ -1881,10 +1892,19 @@ has_table_privilege_name(PG_FUNCTION_ARGS)
text *priv_type_text = PG_GETARG_TEXT_PP(1);
Oid roleid;
Oid tableoid;
+ Oid schemaoid;
AclMode mode;
AclResult aclresult;
roleid = GetUserId();
+
+ schemaoid = convert_table_schema_name(tablename);
+ mode = convert_table_schema_priv_string(priv_type_text);
+
+ aclresult = pg_namespace_aclcheck(schemaoid, roleid, mode);
+ if (aclresult != ACLCHECK_OK)
+ PG_RETURN_BOOL(false);
+
tableoid = convert_table_name(tablename);
mode = convert_table_priv_string(priv_type_text);
@@ -1957,9 +1977,17 @@ has_table_privilege_id_name(PG_FUNCTION_ARGS)
text *tablename = PG_GETARG_TEXT_PP(1);
text *priv_type_text = PG_GETARG_TEXT_PP(2);
Oid tableoid;
+ Oid schemaoid;
AclMode mode;
AclResult aclresult;
+ schemaoid = convert_table_schema_name(tablename);
+ mode = convert_table_schema_priv_string(priv_type_text);
+
+ aclresult = pg_namespace_aclcheck(schemaoid, roleid, mode);
+ if (aclresult != ACLCHECK_OK)
+ PG_RETURN_BOOL(false);
+
tableoid = convert_table_name(tablename);
mode = convert_table_priv_string(priv_type_text);
@@ -1996,6 +2024,20 @@ has_table_privilege_id_id(PG_FUNCTION_ARGS)
* Support routines for has_table_privilege family.
*/
+/*
+ * Given a table name expressed as a string, return its schema Oid
+ */
+static Oid
+convert_table_schema_name(text *tablename)
+{
+ RangeVar *relrv;
+
+ relrv = makeRangeVarFromNameList(textToQualifiedNameList(tablename));
+
+ return get_namespace_oid(relrv->schemaname, false);
+}
+
+
/*
* Given a table name expressed as a string, look it up and return Oid
*/
@@ -2040,6 +2082,36 @@ convert_table_priv_string(text *priv_type_text)
return convert_any_priv_string(priv_type_text, table_priv_map);
}
+/*
+ * convert_table_schema_priv_string
+ * Convert text string to AclMode value.
+ */
+static AclMode
+convert_table_schema_priv_string(text *priv_type_text)
+{
+ static const priv_map table_priv_map[] = {
+ {"SELECT", ACL_USAGE},
+ {"SELECT WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_USAGE)},
+ {"INSERT", ACL_USAGE},
+ {"INSERT WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_USAGE)},
+ {"UPDATE", ACL_USAGE},
+ {"UPDATE WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_USAGE)},
+ {"DELETE", ACL_USAGE},
+ {"DELETE WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_USAGE)},
+ {"TRUNCATE", ACL_USAGE},
+ {"TRUNCATE WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_USAGE)},
+ {"REFERENCES", ACL_USAGE},
+ {"REFERENCES WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_USAGE)},
+ {"TRIGGER", ACL_USAGE},
+ {"TRIGGER WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_USAGE)},
+ {"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_sequence_privilege variants
* These are all named "has_sequence_privilege" at the SQL level.
@@ -3832,7 +3904,6 @@ convert_schema_priv_string(text *priv_type_text)
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.
