Обсуждение: Question about psql meta-command with schema option doesn't use visibilityrule

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

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.

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

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!

Hi David,

>     I have a question that is a specification of permission check
>     (visibilityrule) for psql meta-command with schema option.
> 
> Visibility means search_path, not permission.  If s_a is not in the search_paths it objects are not visible
unqualifiedbut can be seen (catalog) when schema qualified.
 

Thanks for your comments! I understood them:
  - all users can show System catalog (pg_catalog. *) is a
    specification, so it is not a bug
  - visibility and permission are not the same (I confused it before, oops)

Regards,
Tatsuro Yamada