Thread: apparent loss of database access permissions
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.
Attachment
eponymous alias <eponymousalias@yahoo.com> writes: > 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". Actually not; or at least, your test case proves no such thing. That's because these two situations are completely equivalent: > Name | Owner | Encoding | Collate | Ctype | Access privileges > -----------------+-------------+----------+-------------+-------------+----------------------------- > report_db | report_user | UTF8 | en_US.UTF-8 | en_US.UTF-8 | > Name | Owner | Encoding | Collate | Ctype | Access privileges > -----------------+-------------+----------+-------------+-------------+----------------------------- > report_db | report_user | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/report_user + > | | | | | report_user=CTc/report_user If the access privileges are shown as empty (NULL), that implies the object-type-specific default privileges are in effect; and for a database that means the owner has all privileges (CTc) while PUBLIC has "T" and "c" (temp and connect) privileges. The reason it gets to be like that is that normal grant and revoke operations don't make any effort to reset the ACL field to null if the end result of a series of operations chances to be equivalent to the default. However, for reasons of its own pg_dump has to compute the set of GRANT/REVOKE commands to issue to get from the default state to the object's current state --- and in this situation that list is empty. So no commands are issued, the ACL stays null, and after the restore you again see > report_db | report_user | UTF8 | en_US.UTF-8 | en_US.UTF-8 | That's not a bug, just an implementation artifact. We like the fact that the underlying behavior is like this, because leaving default ACLs as null saves a whole lot of storage in some catalogs, notably pg_proc and pg_attribute. There's been occasional discussions of having psql's display commands print the actual default ACL instead of null, but so far the consensus has been that that'd bulk up the listings without really adding much. Admittedly this consensus comes more from seasoned users than confused newbies, but nonetheless there's little appetite to change it. regards, tom lane
> The reason it gets to be like that is that normal grant and revoke
> operations don't make any effort to reset the ACL field to null if
> the end result of a series of operations chances to be equivalent
> to the default. However, for reasons of its own pg_dump has to
> compute the set of GRANT/REVOKE commands to issue to get from the
> default state to the object's current state --- and in this situation
> that list is empty. So no commands are issued, the ACL stays null, ...
>
> That's not a bug, just an implementation artifact.
Okay, thanks for the quick and detailed response.
> operations don't make any effort to reset the ACL field to null if
> the end result of a series of operations chances to be equivalent
> to the default. However, for reasons of its own pg_dump has to
> compute the set of GRANT/REVOKE commands to issue to get from the
> default state to the object's current state --- and in this situation
> that list is empty. So no commands are issued, the ACL stays null, ...
>
> That's not a bug, just an implementation artifact.
Okay, thanks for the quick and detailed response.