MBS-11962: Update privileges for all database users on schema changes

[mwiencek]

Problem

MBS-11962

When new tables or schemas are added, production database users like musicbrainz_ro may not be granted access to them.

Solution

Adds a new script, admin/UpdateDatabasePrivileges.pl, which is invoked from upgrade.sh and updates privileges for the following users if they exist:

• musicbrainz_ro
• caa_redirect
• sir

The script works by revoking all write privileges from musicbrainz_ro, and then granting USAGE on all schemas, and SELECT privileges on all tables in those schemas.

Next, for the last two schemas, it revokes all privileges from them (including SELECT and USAGE), and then simply grants them membership in the musicbrainz_ro role.

Testing

I added the three roles in question to my local database and ran admin/UpdateDatabasePrivileges.pl. I observed that each of the three users could SELECT from any table in our schemas, but not modify them.

[reosarevok]

Tests seem to be failing

[mwiencek]

Tests seem to be failing

Seems to be because the WITH INHERIT clause I'm using was added in PostgreSQL 16.

[mwiencek]

@yvanzo I think I've addressed all of your comments (and it should also work on PG<16 now).

[mwiencek]

Thanks!

Last, when running this script (without --nogrant) two times in a row, two additional lines are logged:

Yeah, those are logged by PG if you try to grant a privilege that already exists. It's harmless though and may be useful information (if it wasn't expected for them to exist).