Implement pg_catalog.has_table_privilege

[hlcianfagna]

Problem Statement

Some tools (see labels) rely on this function.

Possible Solutions

Implement it as defined in https://www.postgresql.org/docs/7.3/functions-misc.html
Please note this function has 2 signatures.
Theobald Software (Xtract Universal) uses (table, access)
Metabase uses (user, table, access)

Considered Alternatives

Mock them with:

CREATE FUNCTION pg_catalog.has_table_privilege("user" TEXT,"table" TEXT,"access" TEXT)
RETURNS BOOLEAN LANGUAGE JAVASCRIPT AS 'function has_table_privilege(user,table,access){return true;}';

CREATE FUNCTION pg_catalog.has_table_privilege("table" TEXT,"access" TEXT)
RETURNS BOOLEAN LANGUAGE JAVASCRIPT AS 'function has_table_privilege(table,access){return true;}';

[matriv]

I've tried out to implement this, and the issue I'm facing is the overload of the function where the user passes tableOid instead of a table, view, foreign table name.
The way we handle oids for schemas is that we use the following code:

Policy matchSchema(Permission permission, int oid) {
        Policy result = Policy.REVOKE;
        for (var privilege : privileges) {
            Subject ident = privilege.subject();
            if (ident.permission() != permission) {
                continue;
            }
            if (ident.securable() == SCHEMA && OidHash.schemaOid(ident.ident()) == oid) {
                return privilege.policy();
            }
            if (ident.securable() == CLUSTER) {
                result = privilege.policy();
            }
        }
        return result;
    }

So we create the oid for every schema in the granted (or denied) privileges of the user and we try to match it against the oid passed as argument to the has_schema_privilege function.
This doesn't work for table oid, because to create a table oid you need the RelationInfo, and not a simple ident string, because it needs to know the type of the table (table, view, foreign table).
To retrieve this we need to use Schemas.findRelation(), which mean we need to pass Schemas to the HasTablePrivilegeFunction and also to the whole hierarchy of those functions.
Additionally, to call Schemas.findRelation() we need to construct a QualifiedName (easy, just split the privilege subject on ".") but we also need a SearchPath, which means we also need to pass down to all
all the calls from HashTablePrivilegeFunction.

• Maybe I missing something for this ^^?
• Maybe we should just not support the oid overload for now,
• Change the way we crate oids for tables? why do we need the type to generate the oid? we cannot have a clash of FQNs, right? (same view and table or foreign table under the same schema). Did a quick test to remove the type from the oid generation and the only think that needs to adjust is the expected oid in varius pg_catalog related tests.

[jeeminso]

Less performant but another option could be to regenerate oids for all existing tables in order to find out the oid -> table mapping.

[matriv]

I don't get what you mean, the hash function is one way. We need to iterate over the table privileges of the user, and for every table listed in the user privileges, produce the oid, and compare it to the oid passed to the hasTablePrivileges() function.