Question about psql meta-command with schema option doesn't use visibilityrule
От | Tatsuro Yamada |
---|---|
Тема | Question about psql meta-command with schema option doesn't use visibilityrule |
Дата | |
Msg-id | 79baeec1-7568-49af-787a-1156594f9025@nttcom.co.jp обсуждение исходный текст |
Ответы |
Re: Question about psql meta-command with schema option doesn't use visibilityrule
("David G. Johnston" <david.g.johnston@gmail.com>)
Re: Question about psql meta-command with schema option doesn't use visibilityrule ("David G. Johnston" <david.g.johnston@gmail.com>) |
Список | 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
В списке pgsql-hackers по дате отправления: