Question about psql meta-command with schema option doesn't use visibilityrule

[Tatsuro Yamada]

Hi,

I have a question that is a specification of permission check
(visibilityrule) for psql meta-command with schema option.

According to the source code [1], there is no check if a schema
option is added. As a result, a role that is not granted can see
other roles' object names.
We might say it's okay because it's a name, not contents (data),
but It seems not preferable, I think.

The following is a reproducer using \dX commands.
Note: It is not only \dX but also \d because it uses the same
permission check function (processSQLNamePattern).

The reproduction procedure (including some results):
================================================
-- Create role a, b as non-superuser
create role a nosuperuser;
create role b nosuperuser;
grant CREATE on database postgres to a;

-- Create schema s_a, table hoge, and its extend stats by role a
set role a;
create schema s_a;
create table s_a.hoge(a int, b int);
create statistics s_a.hoge_ext on a,b from s_a.hoge;
set search_path to public, s_a;

-- Run \dX and \dX s_a.* by role a: OK (since schema s_a was created by role a)
\dX
                       List of extended statistics
 Schema |   Name   |   Definition   | Ndistinct | Dependencies |   MCV
--------+----------+----------------+-----------+--------------+---------
 s_a    | hoge_ext | a, b FROM hoge | defined   | defined      | defined
(1 row)

\dX s_a.*
                       List of extended statistics
 Schema |   Name   |   Definition   | Ndistinct | Dependencies |   MCV
--------+----------+----------------+-----------+--------------+---------
 s_a    | hoge_ext | a, b FROM hoge | defined   | defined      | defined
(1 row)

-- Run \dX by role b: OK
--  (not displayed is fine since role b can't see info of role a)
reset role;
set role b;
\dX
                 List of extended statistics
 Schema | Name | Definition | Ndistinct | Dependencies | MCV
--------+------+------------+-----------+--------------+-----
(0 rows)

-- Run \dX with schema by role b: OK?? (It should be NG?)
-- this case is a point in my question
\dX s_a.*
                         List of extended statistics
 Schema |   Name   |     Definition     | Ndistinct | Dependencies |   MCV
--------+----------+--------------------+-----------+--------------+---------
 s_a    | hoge_ext | a, b FROM s_a.hoge | defined   | defined      | defined
(1 row)

-- clean-up
reset role;
drop schema s_a cascade;
revoke CREATE on DATABASE postgres FROM a;
drop role a;
drop role b;
================================================

From the above results, I expected "\dX s_a.*" doesn't show any info
as same as "\dX". but info is displayed. I'm wondering this behavior.

I'm maybe missing something, but if this is a problem, I'll send a
patch. Any comments are welcome!


[1]: processSQLNamePattern in src/fe_utils/string_utils.c
    if (schemabuf.len > 2)
    {
        /* We have a schema pattern, so constrain the schemavar */

        /* Optimize away a "*" pattern */
        if (strcmp(schemabuf.data, "^(.*)$") != 0 && schemavar)
        {
            WHEREAND();
            appendPQExpBuffer(buf, "%s OPERATOR(pg_catalog.~) ", schemavar);
            appendStringLiteralConn(buf, schemabuf.data, conn);
            if (PQserverVersion(conn) >= 120000)
                appendPQExpBufferStr(buf, " COLLATE pg_catalog.default");
            appendPQExpBufferChar(buf, '\n');
        }
    }
    else
    {
        /* No schema pattern given, so select only visible objects */
        if (visibilityrule)
        {
            WHEREAND();
            appendPQExpBuffer(buf, "%s\n", visibilityrule);
        }
    }



Thanks,
Tatsuro Yamada