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

From: Tatsuro Yamada <tatsuro(dot)yamada(dot)tf(at)nttcom(dot)co(dot)jp>
To: Pg Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Question about psql meta-command with schema option doesn't use visibilityrule
Date: 2021-11-08 02:43:34
Message-ID: 79baeec1-7568-49af-787a-1156594f9025@nttcom.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message David Rowley 2021-11-08 02:47:58 Re: jsonb crash
Previous Message Tom Lane 2021-11-08 02:38:20 Re: jsonb crash