apparent loss of database access permissions

Поиск
Список
Период
Сортировка
От eponymous alias
Тема apparent loss of database access permissions
Дата
Msg-id 2085551791.33703.1663615115771@mail.yahoo.com
обсуждение исходный текст
Ответы Re: apparent loss of database access permissions  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-bugs
I have been testing the use of pg_upgrade, and in so doing I notice
that it loses most database access privileges which are listed under
the "Access privileges" column in output from "psql --list".

Setting that aside for the moment, I see the same kind of behavior
without pg_upgrade in play, in a simple pg_dumpall and psql context
to dump and restore my databases.  It may be simpler for others to
replicate the basic problem in that context, so I provide details here.
I am testing with the Pg 10.22 release, but based on what I was seeing
in upgrade testing, I believe the behavior probably carries through to
the latest Pg 14.5 release as well.

Steps to replicate:

(1) Create a safe space for testing.
    mkdir -p /tmp/pg
    cd /tmp/pg
    cp /tmp/test-databases.sql.gz .
    cp /tmp/grants.sql.gz .
    gunzip test-databases.sql.gz
    gunzip grants.sql.gz
    pg_bin=/usr/lib/postgresql/10/bin
(2) Initialize a fresh database.
    $pg_bin/initdb --locale=en_US.UTF-8 --auth=md5 -U postgres -D /tmp/pg/data --pwprompt
(3) Start the database.
    $pg_bin/pg_ctl start
(4) Load the database with my test databases.
    $pg_bin/psql -f test-databases.sql
(5) Check the access permissions at this point.
    $pg_bin/psql --list
(6) Apply certain access permissions to the databases.
    $pg_bin/psql -f grants.sql
(7) Check the access permissions at this point.
    $pg_bin/psql --list
(8) Optionally, stop and start the database.
    $pg_bin/pg_ctl stop
    $pg_bin/pg_ctl start
(9) Check the access permissions at this point.
    $pg_bin/psql --list
(10) Dump the databases.
    $pg_bin/pg_dumpall --clean --if-exists -f dumped-databases.sql
(11) Restore the databases.
    $pg_bin/psql -f dumped-databases.sql
(12) Check the access permissions at this point.
    $pg_bin/psql --list

I have attached my test-databases.sql and grants.sql files for detailed
inspection and unit testing.

To simplify matters for those who don't want to run the tests themselves,
here are the outputs from steps where we display the access permissions:

Output from step (5):
                                         List of databases
      Name       |    Owner    | Encoding |   Collate   |    Ctype    |      Access privileges      
-----------------+-------------+----------+-------------+-------------+-----------------------------
 has_other_owner | report_user | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/report_user            +
                 |             |          |             |             | report_user=CTc/report_user+
                 |             |          |             |             | unused_user=CTc/report_user
 postgres        | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 report_db       | report_user | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0       | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres                +
                 |             |          |             |             | postgres=CTc/postgres
 template1       | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres      +
                 |             |          |             |             | =c/postgres
(5 rows)

Output from step (7):
                                         List of databases
      Name       |    Owner    | Encoding |   Collate   |    Ctype    |      Access privileges      
-----------------+-------------+----------+-------------+-------------+-----------------------------
 has_other_owner | report_user | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/report_user            +
                 |             |          |             |             | report_user=CTc/report_user+
                 |             |          |             |             | unused_user=CTc/report_user
 postgres        | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 report_db       | report_user | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/report_user            +
                 |             |          |             |             | report_user=CTc/report_user
 template0       | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres                +
                 |             |          |             |             | postgres=CTc/postgres
 template1       | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres      +
                 |             |          |             |             | =c/postgres
(5 rows)

Output from step (9) is the same as from step (7).

Output from step (12);
                                         List of databases
      Name       |    Owner    | Encoding |   Collate   |    Ctype    |      Access privileges      
-----------------+-------------+----------+-------------+-------------+-----------------------------
 has_other_owner | report_user | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/report_user            +
                 |             |          |             |             | report_user=CTc/report_user+
                 |             |          |             |             | unused_user=CTc/report_user
 postgres        | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 report_db       | report_user | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0       | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres                +
                 |             |          |             |             | postgres=CTc/postgres
 template1       | postgres    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres      +
                 |             |          |             |             | =c/postgres
(5 rows)

Observe that between step (7) and step (12), the access permissions on the
has_other_owner database were kept intact, while the access permissions
for the report_db database were apparently lost.  From what I can tell,
the difference between those two databases is how the access privileges
were initially set up.  The has_other_owner database has some extra
privileges accorded to the unused_user (which is to say, a role other
than the role that owns the database), while the report_db does not have
such extra permissions beyond the permissions accorded to the owner of
the database.

Perhaps you will say that the access permissions shown for the report_db
in step (7) amount to nothing more than those already accorded to
the owner of the database.  And thus there is no reason to dump them
and restore them, as what I had in place after the restore is in fact
effectively exactly what I had in place before the dump.  But if that is
the case, then why are those access permissions even listed at all in the
output from step (7)?

There is some verbiage about the display of access
privilege info, in the last couple of paragraphs here:
https://www.postgresql.org/docs/current/ddl-priv.html
However, that info is pretty well buried, inasmuch as there is no direct
mention of it in the psql documentation for the --list or \l options:
https://www.postgresql.org/docs/current/app-psql.html

Whatever the case, I find this behavior highly confusing.  If I apply
permissions, I expect them to be visible and to remain visible throughout
normal backup/restore and upgrade procedures.  And if for instance,
only-default permissions are to be displayed as an empty field, that
behavior should be consistent.  Otherwise, it looks like the tooling is
broken.  In fact, I find that the explicit display of access permissions
survives a database stop/start action, so there is something persistent
about that state that is significant enough to evoke the explicit display
even though a backup/restore action obviously destroys that state.

Вложения

В списке pgsql-bugs по дате отправления:

Предыдущее
От: "David G. Johnston"
Дата:
Сообщение: Re: BUG #17618: unnecessary filter column <> text even after adding index
Следующее
От: Tom Lane
Дата:
Сообщение: Re: apparent loss of database access permissions