The packages_raw gets reloaded. A lot. Every time a new set of packages is available from the FreeBSD packages table, data gets deleted and reloaded. So I manually vacuum the table. Well, it’s automated, as part of the code, but it doesn’t wait for the daily scripts.
I was seeing these errors on server r720-02:
2023-01-07 08:00:32.846 UTC [73161] WARNING: skipping "packages_raw" --- only table or database owner can analyze it 2023-01-07 08:00:32.846 UTC [73161] CONTEXT: SQL statement "analyze packages_raw"
I compared the permissions of the various database instances.
freshports.devgit=# \dp packages_raw
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+--------------+-------+--------------------------------+-------------------+----------
public | packages_raw | table | packaging=arwdDxt/packaging +| |
| | | packager_dev=arwdDxt/packaging+| |
| | | rsyncer=r/packaging | |
(1 row)
freshports.testgit=# \dp packages_raw
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+--------------+-------+--------------------------------+-------------------+----------
public | packages_raw | table | packaging=arwdDxt/packaging +| |
| | | packager_dev=arwdDxt/packaging+| |
| | | rsyncer=r/packaging | |
(1 row)
freshports.stagegit=# \dp packages_raw
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+--------------+-------+--------------------------------+-------------------+----------
public | packages_raw | table | packaging=arwdDxt/packaging +| |
| | | packager_dev=arwdDxt/packaging+| |
| | | rsyncer=r/packaging | |
(1 row)
All the same on dev, test, and stage.
I should remove the explicit permissions for packager_dev on each of those databases. That user is a member of packaging.
Revoking extraneous permissions
As mentioned above, it’s time to remove those permissions:
freshports.testgit=# \dp packages_raw
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+--------------+-------+--------------------------------+-------------------+----------
public | packages_raw | table | packaging=arwdDxt/packaging +| |
| | | packager_dev=arwdDxt/packaging+| |
| | | rsyncer=r/packaging | |
(1 row)
freshports.testgit=# revoke all ON packages_raw FROM packager_dev;
REVOKE
freshports.testgit=# \dp packages_raw
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+--------------+-------+-----------------------------+-------------------+----------
public | packages_raw | table | packaging=arwdDxt/packaging+| |
| | | rsyncer=r/packaging | |
(1 row)
freshports.testgit=# commit;
COMMIT
That was repeated on stage and dev.
Compare with the nodes not in my basement
prod is not the same as above:
[aws-1 dan ~] % psql freshports.org
Password for user postgres:
psql (12.13, server 12.11)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
freshports.org=> \dp packages_raw
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+--------------+-------+---------------------------+-------------------+----------
public | packages_raw | table | postgres=arwdDxt/postgres+| |
| | | rsyncer=r/postgres +| |
| | | packaging=arwd/postgres | |
(1 row)
freshports.org=>
x8dtu matched prod.
root@x8dtu-pg01:/ # psql -U postgres freshports.org
psql (13.9)
Type "help" for help.
freshports.org=# \dp packages_raw
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+--------------+-------+---------------------------+-------------------+----------
public | packages_raw | table | postgres=arwdDxt/postgres+| |
| | | rsyncer=r/postgres +| |
| | | packaging=arwd/postgres | |
(1 row)
So did r720-02 (the host generating the messages in question).
[r720-02-pg01 dan ~] % psql -U postgres freshports.org
psql (13.9)
Type "help" for help.
freshports.org=# \dp packages_raw
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+--------------+-------+---------------------------+-------------------+----------
public | packages_raw | table | postgres=arwdDxt/postgres+| |
| | | rsyncer=r/postgres +| |
| | | packaging=arwd/postgres | |
(1 row)
freshports.org=#
I noticed one difference: ownership.
This is dev, test, and stage:
freshports.stagegit=# \dt packages_raw
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+-----------
public | packages_raw | table | packaging
(1 row)
This is aws-1 (i.e. prod), x8dtu, and r720-02
freshports.org=> \dt packages_raw
List of relations
Schema | Name | Type | Owner
--------+--------------+-------+----------
public | packages_raw | table | postgres
(1 row)
I did this on aws-1, x8dtu, and r720-02:
ALTER TABLE public.packages_raw OWNER TO packaging; GRANT ALL ON TABLE public.packages_raw TO packaging;
The permissions on those hosts are now:
freshports.org=# \dp packages_raw
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+--------------+-------+-----------------------------+-------------------+----------
public | packages_raw | table | packaging=arwdDxt/packaging+| |
| | | rsyncer=r/packaging | |
(1 row)
Now it matches dev, test, and stage.
