Fixing permissions on the packages_raw table

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.

Website Pin Facebook Twitter Myspace Friendfeed Technorati del.icio.us Digg Google StumbleUpon Premium Responsive

Leave a Comment

Scroll to Top