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) >= 12)
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
