Dan Langille

I've been playing with Open Source software, starting with FreeBSD, since New Zealand Post installed DSL on my street in 1998. From there, I started writing at The FreeBSD Diary, moving my work here after I discovered WordPress. Along the way, I started the BSDCan and PGCon conferences. I slowly moved from software development into full time systems administration and now work for very-well known company who has been a big force in the security industry.

Feb 062021
 

EDIT: update, I think I have solved this via help on IRC. See solution.

I need help configuring Postfix. I am overwhelmed by the options available and I need to stay on track by working on the rest of the git changes.

I have changed references to the FreshPorts domain. I’m using example.org instead.

Background

FreshPorts processes incoming subversion commit emails to create XML which is then loaded into the database.

I need help configuring a new host: mx-ingress04

Mail goes from the FreeBSD mailing list to mx-ingress02 which then distributes it to other nodes.

mx-ingress04 is on AWS and this is what I’m having trouble with. This host is restricted by AWS on outgoing port 25 connections. To solve that, I can use relayhost to another mail server which is configured to relay.

The problem

When I enable relay on mx-ingress04, it breaks the delivery which works now. All incoming mail goes out via relay instead of going to the ingress host for processing.

Logs with relayhost=10.0.17.21:

Feb  6 20:39:43 mx-ingress04 postfix/smtpd[77346]: connect from unknown[198.51.100.203]
Feb  6 20:39:43 mx-ingress04 postfix/smtpd[77346]: CA1BABCFB: client=unknown[198.51.100.203]
Feb  6 20:39:43 mx-ingress04 postfix/cleanup[77352]: CA1BABCFB: message-id=<20210206203943.99D71EC81@mx-ingress02.example.org>
Feb  6 20:39:43 mx-ingress04 postfix/qmgr[77341]: CA1BABCFB: from=<dan@mx-ingress02.example.org>, size=572, nrcpt=1 (queue active)
Feb  6 20:39:43 mx-ingress04 postfix/smtpd[77346]: disconnect from unknown[198.51.100.203] ehlo=2 starttls=1 mail=1 rcpt=1 data=1 quit=1 commands=7
Feb  6 20:39:43 mx-ingress04 postfix/cleanup[77352]: CED1DBD8F: message-id=<20210206203943.99D71EC81@mx-ingress02.example.org>
Feb  6 20:39:43 mx-ingress04 postfix/local[77353]: CA1BABCFB: to=<production@mx-ingress04.example.org>, relay=local, delay=0.03, delays=0.02/0.01/0/0, dsn=2.0.0, status=sent (forwarded as CED1DBD8F)
Feb  6 20:39:43 mx-ingress04 postfix/qmgr[77341]: CED1DBD8F: from=<dan@mx-ingress02.example.org>, size=732, nrcpt=1 (queue active)
Feb  6 20:39:43 mx-ingress04 postfix/qmgr[77341]: CA1BABCFB: removed
Feb  6 20:39:43 mx-ingress04 postfix/smtp[77354]: CED1DBD8F: to=<ingress_svn@[127.163.0.10]>, orig_to=<production@mx-ingress04.example.org>, relay=10.0.17.21[10.0.17.21]:25, delay=0.03, delays=0/0.01/0.01/0, dsn=2.0.0, status=sent (250 2.0.0 Ok: queued as D4EDA2E2EB)
Feb  6 20:39:43 mx-ingress04 postfix/qmgr[77341]: CED1DBD8F: removed

Logs without relayhost:

Feb  6 20:39:26 mx-ingress04 postfix/smtpd[77211]: connect from unknown[198.51.100.203]
Feb  6 20:39:26 mx-ingress04 postfix/smtpd[77211]: 5A7A9BD76: client=unknown[198.51.100.203]
Feb  6 20:39:26 mx-ingress04 postfix/cleanup[77217]: 5A7A9BD76: message-id=<20210206203926.1938AEB7D@mx-ingress02.example.org>
Feb  6 20:39:26 mx-ingress04 postfix/smtpd[77211]: disconnect from unknown[198.51.100.203] ehlo=2 starttls=1 mail=1 rcpt=1 data=1 quit=1 commands=7
Feb  6 20:39:26 mx-ingress04 postfix/qmgr[77205]: 5A7A9BD76: from=<dan@mx-ingress02.example.org>, size=572, nrcpt=1 (queue active)
Feb  6 20:39:26 mx-ingress04 postfix/cleanup[77217]: 5D2F9BCF9: message-id=<20210206203926.1938AEB7D@mx-ingress02.example.org>
Feb  6 20:39:26 mx-ingress04 postfix/local[77218]: 5A7A9BD76: to=<production@mx-ingress04.example.org>, relay=local, delay=0.01, delays=0.01/0/0/0, dsn=2.0.0, status=sent (forwarded as 5D2F9BCF9)
Feb  6 20:39:26 mx-ingress04 postfix/qmgr[77205]: 5D2F9BCF9: from=<dan@mx-ingress02.example.org>, size=732, nrcpt=1 (queue active)
Feb  6 20:39:26 mx-ingress04 postfix/qmgr[77205]: 5A7A9BD76: removed
Feb  6 20:39:26 mx-ingress04 postfix/smtp[77219]: 5D2F9BCF9: to=<ingress_svn@[127.163.0.10]>, orig_to=<production@mx-ingress04.example.org>, relay=127.163.0.10[127.163.0.10]:25, delay=0.05, delays=0/0/0.04/0, dsn=2.0.0, status=sent (250 2.0.0 Ok: queued as 683A35F665)
Feb  6 20:39:26 mx-ingress04 postfix/qmgr[77205]: 5D2F9BCF9: removed

The goal

At mx-ingress04, Incoming mail for production@mx-ingress04.example.org goes to ingress_svn@[127.163.0.10].

Any mail originating on mx-ingress04 is relayed out to 10.0.17.21.

Existing configuration

This is the existing configuration on mx-ingress04:

# postconf -n
alias_maps = hash:/etc/mail/aliases
command_directory = /usr/local/sbin
compatibility_level = 2
daemon_directory = /usr/local/libexec/postfix
data_directory = /var/db/postfix
debug_peer_level = 2
debugger_command = PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin ddd $daemon_directory/$process_name $process_id & sleep 5
html_directory = no
inet_interfaces = 127.163.0.25, [redacted]:1a17:f712:6854:4175:9eaa
inet_protocols = ipv4, ipv6
mail_owner = postfix
mailbox_command = /usr/local/bin/maildrop -d ${USER}
mailbox_size_limit = 102400000
mailq_path = /usr/local/bin/mailq
manpage_directory = /usr/local/man
message_size_limit = 102400000
mynetworks_style = host
newaliases_path = /usr/local/bin/newaliases
queue_directory = /var/spool/postfix
readme_directory = no
recipient_delimiter = +
sample_directory = /usr/local/etc/postfix
sendmail_path = /usr/local/sbin/sendmail
setgid_group = maildrop
smtp_tls_CAfile = /usr/local/etc/ssl/ca.cer
smtp_tls_cert_file = /usr/local/etc/ssl/mx-ingress04.example.org.fullchain.cer
smtp_tls_fingerprint_digest = sha1
smtp_tls_key_file = /usr/local/etc/ssl/mx-ingress04.example.org.key
smtp_tls_policy_maps = hash:/usr/local/etc/postfix/tls_policy
smtp_tls_security_level = may
smtp_tls_session_cache_database = btree:/var/db/postfix/smtp_scache
smtpd_tls_CAfile = /usr/local/etc/ssl/ca.cer
smtpd_tls_cert_file = /usr/local/etc/ssl/mx-ingress04.example.org.fullchain.cer
smtpd_tls_fingerprint_digest = sha1
smtpd_tls_key_file = /usr/local/etc/ssl/mx-ingress04.example.org.key
smtpd_tls_security_level = encrypt
smtpd_tls_session_cache_database = btree:/var/db/postfix/smtpd_scache
soft_bounce = yes
unknown_local_recipient_reject_code = 550
virtual_maps = hash:/usr/local/etc/postfix/mx-ingress04.example.org.virtual-virtual_alias_maps
# cat mx-ingress04.example.org.virtual-virtual_alias_maps
postmaster@mx-ingress04.example.org   dan@langille.org
production@mx-ingress04.example.org   ingress_svn@[127.163.0.10]

Since writing this, I’ve found some help via IRC

Solution

This is what I am using now. In short I have:

  1. Removed virtual_maps
  2. Removed relayhost
  3. Added a new alias_maps
  4. Added a new transport_maps

The new alias_maps is:

[root@mx-ingress04 /usr/local/etc/postfix]# cat mx-ingress04-alias-maps
production:	ingress_svn@[127.163.0.10]

This says

  • any mail for local user production gets sent to ingress_svn@[127.163.0.10]

The new transport_maps is:

[root@mx-ingress04 /usr/local/etc/postfix]# cat mx-ingress04-transport
[127.163.0.10] :
* :[10.0.17.21]

This says:

  • [127.163.0.10] will be delivered locally
  • all other mail will be relayed via 10.0.17.21

The current configuration is:

# postconf -n
alias_maps = hash:/etc/mail/aliases, hash:/usr/local/etc/postfix/mx-ingress04-alias-maps
command_directory = /usr/local/sbin
compatibility_level = 2
daemon_directory = /usr/local/libexec/postfix
data_directory = /var/db/postfix
debug_peer_level = 2
debugger_command = PATH=/bin:/usr/bin:/usr/local/bin:/usr/X11R6/bin ddd $daemon_directory/$process_name $process_id & sleep 5
html_directory = no
inet_interfaces = 127.163.0.25, [redacted]:1a17:f712:6854:4175:9eaa
inet_protocols = ipv4, ipv6
mail_owner = postfix
mailbox_command = /usr/local/bin/maildrop -d ${USER}
mailbox_size_limit = 102400000
mailq_path = /usr/local/bin/mailq
manpage_directory = /usr/local/man
message_size_limit = 102400000
mynetworks_style = host
newaliases_path = /usr/local/bin/newaliases
queue_directory = /var/spool/postfix
readme_directory = no
recipient_delimiter = +
sample_directory = /usr/local/etc/postfix
sendmail_path = /usr/local/sbin/sendmail
setgid_group = maildrop
smtp_tls_CAfile = /usr/local/etc/ssl/ca.cer
smtp_tls_cert_file = /usr/local/etc/ssl/mx-ingress04.example.org.fullchain.cer
smtp_tls_fingerprint_digest = sha1
smtp_tls_key_file = /usr/local/etc/ssl/mx-ingress04.example.org.key
smtp_tls_policy_maps = hash:/usr/local/etc/postfix/tls_policy
smtp_tls_security_level = may
smtp_tls_session_cache_database = btree:/var/db/postfix/smtp_scache
smtpd_tls_CAfile = /usr/local/etc/ssl/ca.cer
smtpd_tls_cert_file = /usr/local/etc/ssl/mx-ingress04.example.org.fullchain.cer
smtpd_tls_fingerprint_digest = sha1
smtpd_tls_key_file = /usr/local/etc/ssl/mx-ingress04.example.org.key
smtpd_tls_security_level = encrypt
smtpd_tls_session_cache_database = btree:/var/db/postfix/smtpd_scache
soft_bounce = yes
transport_maps = hash:/usr/local/etc/postfix/mx-ingress04-transport
unknown_local_recipient_reject_code = 550
[root@mx-ingress04 /usr/local/etc/postfix]# 
Feb 052021
 

This is what runs in an ingress jail:

[root@ingress01 ~]# ps auwwx
USER          PID %CPU %MEM   VSZ  RSS TT  STAT STARTED    TIME COMMAND
root        43108  0.0  0.2 11428 2016  -  SsJ  03:12   0:00.00 /usr/sbin/syslogd -s
root        43226  0.0  0.6 51332 6200  -  SsJ  03:12   0:00.01 /usr/local/libexec/postfix/master -w
postfix     43227  0.0  0.6 51384 6292  -  SJ   03:12   0:00.01 pickup -l -t unix -u
postfix     43228  0.0  0.6 51384 6252  -  IJ   03:12   0:00.01 qmgr -l -t unix -u
nagios      43232  0.0  0.4 15956 3792  -  IsJ  03:12   0:00.00 /usr/local/sbin/nrpe3 -c /usr/local/etc/nrpe.cfg -d
ingress_svn 43240  0.0  0.2 11004 1652  -  IsJ  03:12   0:00.00 daemon: ingress_svn[43241] (daemon)
ingress_svn 43241  0.0  0.2 11612 1856  -  SJ   03:12   0:00.01 /bin/sh /usr/local/libexec/freshports-service/ingress_svn.sh
ingress     43250  0.0  0.2 11004 1652  -  IsJ  03:12   0:00.00 daemon: ingress[43251] (daemon)
ingress     43251  0.0  0.2 11576 1844  -  SJ   03:12   0:00.01 /bin/sh /usr/local/libexec/freshports-service/ingress.sh
freshports  43263  0.0  0.2 11004 1652  -  IsJ  03:12   0:00.00 daemon: freshports[43264] (daemon)
freshports  43264  0.0  0.2 11612 1852  -  SJ   03:12   0:00.02 /bin/sh /usr/local/libexec/freshports-service/freshports.sh
root        43283  0.0  0.4 19912 4328  -  IsJ  03:12   0:00.00 /usr/sbin/sshd
root        43291  0.0  0.2 11308 1828  -  SsJ  03:12   0:00.00 /usr/sbin/cron -s
postfix     44431  0.0  0.8 51948 7392  -  IJ   03:12   0:00.01 tlsmgr -l -t unix -u
ingress     46156  0.0  0.2 10676 1600  -  SCJ  03:14   0:00.00 sleep 3
ingress_svn 46157  0.0  0.2 10676 1600  -  SCJ  03:14   0:00.00 sleep 3
freshports  46158  0.0  0.2 10676 1600  -  SCJ  03:14   0:00.00 sleep 3
root        46153  0.0  0.3 13140 3232  6  SJ   03:14   0:00.01 /bin/csh -i
root        46155  0.0  0.4 13264 3480  6  SJ   03:14   0:00.00 bash
root        46159  0.0  0.2 11600 2156  6  R+J  03:14   0:00.00 ps auwwx
[root@ingress01 ~]# 

This runs in a nginx jail:

root@nginx01:/ # ps auwwx
USER         PID %CPU %MEM    VSZ   RSS TT  STAT STARTED    TIME COMMAND
root       44800  0.0  0.2  11376  2060  -  SsJ  03:12   0:00.00 /usr/sbin/syslogd -s
root       44828  0.0  0.9  28232  9280  -  SJ   03:12   0:00.01 /usr/local/sbin/snmpd -p /var/run/net_snmpd.pid -c /usr/local/etc/snmpd.conf -a -r
root       44923  0.0  0.7  51356  6588  -  IsJ  03:12   0:00.01 /usr/local/libexec/postfix/master -w
postfix    44924  0.0  0.7  51304  6540  -  IJ   03:12   0:00.01 pickup -l -t unix -u
postfix    44925  0.0  0.7  51360  6600  -  IJ   03:12   0:00.01 qmgr -l -t unix -u
root       44929  0.0  1.3 173480 12820  -  SsJ  03:12   0:00.02 php-fpm: master process (/usr/local/etc/php-fpm.conf) (php-fpm)
www        44930  0.0  1.3 173560 12816  -  IJ   03:12   0:00.00 php-fpm: pool www (php-fpm)
www        44931  0.0  2.0 174252 19912  -  IJ   03:12   0:00.03 php-fpm: pool www (php-fpm)
www        44933  0.0  1.3 173560 12820  -  IJ   03:12   0:00.00 php-fpm: pool www (php-fpm)
www        44934  0.0  1.3 173560 12820  -  IJ   03:12   0:00.00 php-fpm: pool www (php-fpm)
www        44935  0.0  1.3 173560 12820  -  IJ   03:12   0:00.00 php-fpm: pool www (php-fpm)
www        44936  0.0  1.3 173560 12820  -  IJ   03:12   0:00.00 php-fpm: pool www (php-fpm)
www        44937  0.0  1.3 173560 12824  -  IJ   03:12   0:00.00 php-fpm: pool www (php-fpm)
www        44938  0.0  1.3 173560 12824  -  IJ   03:12   0:00.00 php-fpm: pool www (php-fpm)
www        44939  0.0  1.3 173560 12824  -  IJ   03:12   0:00.00 php-fpm: pool www (php-fpm)
www        44940  0.0  1.3 173564 12828  -  IJ   03:12   0:00.00 php-fpm: pool www (php-fpm)
www        44941  0.0  1.3 173564 12828  -  IJ   03:12   0:00.00 php-fpm: pool www (php-fpm)
www        44942  0.0  1.3 173564 12828  -  IJ   03:12   0:00.00 php-fpm: pool www (php-fpm)
www        44943  0.0  1.3 173564 12828  -  IJ   03:12   0:00.00 php-fpm: pool www (php-fpm)
www        44944  0.0  1.3 173564 12836  -  IJ   03:12   0:00.00 php-fpm: pool www (php-fpm)
www        44945  0.0  1.3 173564 12836  -  IJ   03:12   0:00.00 php-fpm: pool www (php-fpm)
www        44946  0.0  1.3 173564 12836  -  IJ   03:12   0:00.00 php-fpm: pool www (php-fpm)
www        44947  0.0  1.3 173564 12836  -  IJ   03:12   0:00.00 php-fpm: pool www (php-fpm)
www        44948  0.0  1.3 173564 12840  -  IJ   03:12   0:00.00 php-fpm: pool www (php-fpm)
www        44949  0.0  1.3 173564 12840  -  IJ   03:12   0:00.00 php-fpm: pool www (php-fpm)
www        44950  0.0  1.3 173564 12840  -  IJ   03:12   0:00.00 php-fpm: pool www (php-fpm)
nagios     44953  0.0  0.4  15956  3884  -  IsJ  03:12   0:00.00 /usr/local/sbin/nrpe3 -c /usr/local/etc/nrpe.cfg -d
root       44957  0.0  0.7  21160  6400  -  IsJ  03:12   0:00.00 nginx: master process /usr/local/sbin/nginx
www        44958  0.0  0.7  21232  6876  -  IJ   03:12   0:00.00 nginx: worker process (nginx)
www        44959  0.0  0.7  21232  6876  -  IJ   03:12   0:00.00 nginx: worker process (nginx)
www        44960  0.0  0.7  21232  6876  -  IJ   03:12   0:00.00 nginx: worker process (nginx)
www        44961  0.0  0.7  21232  6876  -  IJ   03:12   0:00.00 nginx: worker process (nginx)
www        44962  0.0  0.9  21400  8488  -  IJ   03:12   0:00.01 nginx: worker process (nginx)
www        44963  0.0  0.7  21232  6876  -  IJ   03:12   0:00.00 nginx: worker process (nginx)
www        44964  0.0  0.7  21232  6876  -  IJ   03:12   0:00.00 nginx: worker process (nginx)
www        44967  0.0  0.7  21232  6876  -  IJ   03:12   0:00.00 nginx: worker process (nginx)
freshports 44975  0.0  0.2  10844  1700  -  IsJ  03:12   0:00.00 daemon: fp_listen[44976] (daemon)
freshports 44976  0.0  1.8  29080 17704  -  SJ   03:12   0:00.14 /usr/local/bin/python /usr/local/lib/python3.7/site-packages/fp-listen/fp-listen.py (python3.7)
root       44994  0.0  0.6  19912  5424  -  IsJ  03:12   0:00.00 /usr/sbin/sshd
root       44998  0.0  0.2  11308  2032  -  IsJ  03:12   0:00.00 /usr/sbin/cron -s
root       46118  0.0  0.4  13128  3492  6  SJ   03:13   0:00.01 /bin/csh -i
root       46122  0.0  0.2  11624  2432  6  R+J  03:13   0:00.00 ps auwwx
Jan 312021
 

In this post I’ll talk about how the code intially took the easy way out and how recent changes have tightened that up. In short, the code would raise N messages when just a single notification is sufficient.

Today the full vuxml database was reloaded into FreshPorts. This affects approximately 3454 ports and the marking of 60,436 commits (or revisions) as vulnerable. When you look at a port page, you can see the skulls which indicate a known vuln beside the commits in question (look under Commit History). That makes up for 565,576 unique combinations of commits, ports, and vuxml entries.

After all that is processed (over about 21.5 hours on the current server), we need to flush the cache of for each item that changed (ports, daily summaries, etc).

How cache clearing works

When a port is updated, a trigger on the ports table adds an entry to the cache_clearing_ports. A long running daemon monitors the table waiting for a LISTEN notification. When it arrives, the daemon reads the table and starts processing the entries, clearing the cache.

This is simplified though. Updates occur in many places and each invokes its own trigger. However, for this post, we will deal with only one.

current cache_clearing_ports table

This is the current cache_clearing_ports table:

freshports.org=# \d cache_clearing_ports
                                        Table "public.cache_clearing_ports"
   Column   |            Type             | Collation | Nullable |                     Default                      
------------+-----------------------------+-----------+----------+--------------------------------------------------
 id         | integer                     |           | not null | nextval('cache_clearing_ports_id_seq'::regclass)
 port_id    | integer                     |           | not null | 
 category   | text                        |           | not null | 
 port       | text                        |           | not null | 
 date_added | timestamp without time zone |           | not null | now()
Indexes:
    "cache_clearing_ports_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "cache_clearing_ports_port_id_fkey" FOREIGN KEY (port_id) REFERENCES ports(id) ON UPDATE CASCADE ON DELETE CASCADE

freshports.org=# 

As you can see, duplicate entries are OK. However, over a single commit, which is how the vuxml data is processed, this can result in a large number of duplicates.

How many duplicates?

Checking here, we have just over 1 million entries.

freshports.org=# select count(*) from cache_clearing_ports;
  count  
---------
 1005407
(1 row)

How unique are they?

Only 3500 ports:

freshports.org=# select count(distinct port_id) from commit_log_ports_vuxml;
 count 
-------
  3454
(1 row)

I recall when I was writing this: duplicates are no big deal. The cache gets cleared twice. So what?

It takes time to read through the cache_clearing_ports. A long time, when you have about 1 million entries.

The trigger

This is the existing trigger:

CREATE OR REPLACE FUNCTION ports_clear_cache() RETURNS TRIGGER AS $$
   DECLARE
      l_port      text;
      l_category  text;
   BEGIN
      IF TG_OP = 'UPDATE' THEN
            SELECT category, name
              INTO l_category, l_port
              FROM ports_all
             WHERE id = NEW.id;

            INSERT INTO cache_clearing_ports (port_id, category, port)
                 VALUES (NEW.id, l_category, l_port);

            NOTIFY port_updated;
      END IF;

      -- when a port changes, add an entry to the cache clearing table
      RETURN NEW;
   END
$$ LANGUAGE 'plpgsql';

  DROP TRIGGER IF EXISTS ports_clear_cache ON ports;
CREATE TRIGGER ports_clear_cache
    AFTER UPDATE ON ports
    FOR EACH ROW
    EXECUTE PROCEDURE ports_clear_cache();

This is how I propose to change the table. First, I’m creating a new identical table to play with:

freshports.dev=# create table cache_clearing_ports_test (like cache_clearing_ports INCLUDING ALL);
CREATE TABLE

Just what I need. This creates a new table, including all the constraints etc, so I could play with it without affecting the ongoing database operation in my dev server.

This is the DDL I would run:

ALTER TABLE public.cache_clearing_ports_test
    ADD CONSTRAINT cache_clearing_ports_test_port_id_idx UNIQUE (port_id);

COMMENT ON CONSTRAINT cache_clearing_ports_port_id_idx ON public.cache_clearing_ports_test
    IS 'Let''s store just one instance per port_id';

The DDL for the real table would be:

ALTER TABLE public.cache_clearing_ports
    ADD CONSTRAINT cache_clearing_ports_port_id_idx UNIQUE (port_id);

COMMENT ON CONSTRAINT cache_clearing_ports_port_id_idx ON public.cache_clearing_ports
    IS 'Let''s store just one instance per port_id';

This is the amended test table:

freshports.dev=# \d cache_clearing_ports_test
                                      Table "public.cache_clearing_ports_test"
   Column   |            Type             | Collation | Nullable |                     Default                      
------------+-----------------------------+-----------+----------+--------------------------------------------------
 id         | integer                     |           | not null | nextval('cache_clearing_ports_id_seq'::regclass)
 port_id    | integer                     |           | not null | 
 category   | text                        |           | not null | 
 port       | text                        |           | not null | 
 date_added | timestamp without time zone |           | not null | now()
Indexes:
    "cache_clearing_ports_test_pkey" PRIMARY KEY, btree (id)
    "cache_clearing_ports_port_id_idx" UNIQUE CONSTRAINT, btree (port_id)

freshports.dev=# 

Next step, fix the trigger, which will produce duplicate errors if left as it is.

The new trigger

The INSERT command has a conflict_target clause, which is very useful for this situation.

CREATE OR REPLACE FUNCTION ports_clear_cache() RETURNS TRIGGER AS $$
   DECLARE
      l_port      text;
      l_category  text;
   BEGIN
      IF TG_OP = 'UPDATE' THEN
            SELECT category, name
              INTO l_category, l_port
              FROM ports_all
             WHERE id = NEW.id;

            INSERT INTO cache_clearing_ports (port_id, category, port)
                 VALUES (NEW.id, l_category, l_port)
            ON CONFLICT ON CONSTRAINT cache_clearing_ports_port_id_idx DO NOTHING;

            NOTIFY port_updated;
      END IF;

      -- when a port changes, add an entry to the cache clearing table
      RETURN NEW;
   END
$$ LANGUAGE 'plpgsql';

What this says:

  • On the INSERT
  • IF there is a conflict on the unique port_id index
  • THEN do nothing

There are many other things you can do ON CONFLICT, but in my case, doing nothing is what I want.

But wait! There’s more!

I went one step farther. Why NOTIFY if we don’t have to.

CREATE OR REPLACE FUNCTION ports_clear_cache() RETURNS TRIGGER AS $$
   DECLARE
      l_port      text;
      l_category  text;
      l_row_count bigint;
   BEGIN
      IF TG_OP = 'UPDATE' THEN
            SELECT category, name
              INTO l_category, l_port
              FROM ports_all
             WHERE id = NEW.id;

            INSERT INTO cache_clearing_ports (port_id, category, port)
                 VALUES (NEW.id, l_category, l_port)
            ON CONFLICT ON CONSTRAINT cache_clearing_ports_port_id_idx DO NOTHING;

            GET DIAGNOSTICS l_row_count = ROW_COUNT;
            IF l_row_count > 0 THEN
                  NOTIFY port_updated;
            END IF;
      END IF;

      -- when a port changes, add an entry to the cache clearing table
      RETURN NEW;
   END
$$ LANGUAGE 'plpgsql';

In this solution, the code checks to see if anything was inserted. If so, it issues the NOTIFY.

Jan 312021
 

FreshPorts can display a single commit based upon commit id, which is based upon the Message-Id of the commit email (for subversion and cvs) or the commit hash (for git). Commit ids are specified in the URL. Cached entries are stored on disk and are named after the commit. It is because are are using user-supplied data to check a file on disk that we need to sanitize the input. Otherwise, for example, they could ask for /etc/passwd.

That attack vector is sanitized by changing all all characters outsize [a-z,A-Z,0-9] to a dash.

The following shows the new and then old cache solution.

We could avoid this sanitization by first querying the database with the incoming query, after escaping the incoming value. If found, then check the cache. If not found, pull from database.

Checking the cache first seems to make the most sense.

[dan@test-nginx01:/var/db/freshports/cache/commits] $ ls -l
total 1
drwxrwxr--  2 www  freshports  3 Jan 26 14:28 202101261404-10qe4gaf086359-repo-freebsd-org
drwxrwxr--  2 www  freshports  2 Jan 26 14:28 202101261404.10QE4Gaf086359@repo.freebsd.org

These changed were committed recently.

Jan 072021
 

This documents the git specific code which I need to package and deploy.

/usr/local/libexec/freshports

This is installed by freshports-scripts-git, and I have the directory symlinked to ~/scripts on devgit.freshports.org for convenience.

In this directory, we have the following symlinks to other directories:

[dan@devgit-ingress01:~/scripts] $ find . -type l | xargs ls -l
lrwxr-xr-x  1 dan   dan    14 Dec  6 23:21 ./.#freebsd-cvs.sh -> dan@here.92757
lrwxr-xr-x  1 root  dan    64 Jul  4  2020 ./check_git.sh -> /usr/home/dan/src/git_proc_commit/git-to-freshports/check_git.sh
lrwxr-xr-x  1 root  wheel  35 Nov 26 04:20 ./config.sh -> /usr/local/etc/freshports/config.sh
lrwxr-xr-x  1 dan   dan    64 Jul  3  2020 ./git-delta.sh -> /usr/home/dan/src/git_proc_commit/git-to-freshports/git-delta.sh
lrwxr-xr-x  1 dan   dan    75 Aug  6 21:16 ./git-range-of-commits.sh -> /usr/home/dan/src/git_proc_commit/git-to-freshports/git-range-of-commits.sh
lrwxr-xr-x  1 root  dan    72 Jul  5  2020 ./git-single-commit.sh -> /usr/home/dan/src/git_proc_commit/git-to-freshports/git-single-commit.sh
lrwxr-xr-x  1 root  dan    76 Jul  5  2020 ./git-to-freshports-xml.py -> /usr/home/dan/src/git_proc_commit/git-to-freshports/git-to-freshports-xml.py
[dan@devgit-ingress01:~/scripts] $ 

Ignoring lines 2 and 4 which are not relevant, we have this list of files, all of which reside in /usr/home/dan/src/git_proc_commit/git-to-freshports/:

  1. check_git.sh
  2. git-delta.sh
  3. git-range-of-commits.sh
  4. git-single-commit.sh
  5. git-to-freshports-xml.py

What repo is this?

[dan@devgit-ingress01:~/src/git_proc_commit/git-to-freshports] $ git remote -v
origin	git@github.com:FreshPorts/git_proc_commit.git (fetch)
origin	git@git.langille.org:FreshPorts/git_proc_commit.git (push)
origin	git@github.com:FreshPorts/git_proc_commit.git (push)

OK, we do that.

/usr/local/lib/perl5/site_perl/FreshPorts

This directory is populated by p5-freshports-modules-git, and I have the directory symlinked to ~/modules on devgit.freshports.org for convenience.

[dan@devgit-ingress01:/usr/local/lib/perl5/site_perl/FreshPorts] $ find . -type l | xargs ls -l 
lrwxr-xr-x  1 root  freshports  35 Jul 18 17:12 ./config.pm -> /usr/local/etc/freshports/config.pm

We have nothing installed here.

Which makes sense, it’s all on the ingress side – collecting of commits and creating XML.

This makes it easier. Just one package, and I’ll just install it into that single directory.

EDIT: 2021-01-08 Package has been created and this is what it installs:

[dan@empty:/usr/local/libexec/freshports] $ ls -l
total 57
-r-xr-xr-x  1 root  wheel   505 Jan  9 02:03 check_git.sh
-r-xr-xr-x  1 root  wheel  2947 Jan  9 02:03 git-delta.sh
-r-xr-xr-x  1 root  wheel  1663 Jan  9 02:03 git-range-of-commits.sh
-r-xr-xr-x  1 root  wheel  1589 Jan  9 02:03 git-single-commit.sh
-r-xr-xr-x  1 root  wheel  9359 Jan  9 02:03 git-to-freshports-xml.py
[dan@empty:/usr/local/libexec/freshports] $ pkg info -l freshports-git-proc-commit
freshports-git-proc-commit-0.0.1:
	/usr/local/libexec/freshports/check_git.sh
	/usr/local/libexec/freshports/git-delta.sh
	/usr/local/libexec/freshports/git-range-of-commits.sh
	/usr/local/libexec/freshports/git-single-commit.sh
	/usr/local/libexec/freshports/git-to-freshports-xml.py
[dan@empty:/usr/local/libexec/freshports] $ 
[dan@empty:~] $ pkg info -d freshports-git-proc-commit
freshports-git-proc-commit-0.0.1_1:
	python37-3.7.9_1
	py37-pygit2-1.3.0
	py37-lxml-4.6.2
	git-2.30.0
[dan@empty:~] $ 
Jan 012021
 

This post documents the database upgrade process.

Issues encountered during initial attempts

There are permission issues on the database. Not everything is owned by the postgres super-user. In fact, many of them are owned by dan – this database is 20+ years old.

To easily resolve the ownership issue, I take the database dump (named freshports.org.dump) from production (the hostname is supernews) and load it into a development database using the –no-owner option on the pg_restore command. More on that later. The –no-owner option ensures that the restore process does not “set ownership of objects to match the original database“.

The permission issue also extends to functions. If I look at the production database, I see this:

freshports.org=# \df+ armor
                                                                          List of functions
 Schema | Name  | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description 
--------+-------+------------------+---------------------+------+------------+----------+-------+----------+-------------------+----------+-------------+-------------
 public | armor | text             | bytea               | func | immutable  | unsafe   | dan   | invoker  |                   | c        | pg_armor    | 
(1 row)

freshports.org=# 

That permission issue is also fixed by the –no-owner option. After importing in the dev database, that function looks like this:

freshports.prod.test.owners=# \df+ armor
                                                                            List of functions
 Schema | Name  | Result data type | Argument data types | Type | Volatility | Parallel |  Owner   | Security | Access privileges | Language | Source code | Description 
--------+-------+------------------+---------------------+------+------------+----------+----------+----------+-------------------+----------+-------------+-------------
 public | armor | text             | bytea               | func | immutable  | unsafe   | postgres | invoker  |                   | c        | pg_armor    | 
(1 row)

freshports.prod.test.owners=# 

The current procedure

This section outlines the steps to convert the existing production database and copy it over to Amazon’s RDS.

Dump production

On the production server:

pg_dump -Fc freshports.org -f freshports.org.dump

Load into dev

Load that into the development server:

sudo su -l postgres
createdb -O postgres -T template0 -E SQL_ASCII freshports.prod
pg_restore -j 20 --no-owner -d freshports.prod freshports.org.dump 

Doing this as the postgres use ensures optimal permissions.

Estimated time: 25 minutes.

Adjust the database schema

Once we had the database loaded into the dev environment, I ran this script to update the schema to be git-ready.

This is taken from the SQL for git issue on the git_proc_commit project.

-- repo table needs to know which repository tool we are using

ALTER TABLE public.repo
    ADD COLUMN repository text NOT NULL DEFAULT 'subversion';

COMMENT ON COLUMN public.repo.repository
    IS 'subversion? git? cvs?';


-- Let's rename the svn_hostname column:
ALTER TABLE public.repo
    RENAME svn_hostname TO repo_hostname;


-- We don't need these unique indexes. We might do something with constraints later.

drop index repo_description;
drop index repo_name;
drop index repo_path_to_repo;

-- Add in the git data:

insert into repo (name, description, repo_hostname, path_to_repo, repository) values ('ports', 'The FreeBSD Ports tree', 'github.com', '/freebsd/freebsd-ports', 'git');
insert into repo (name, description, repo_hostname, path_to_repo, repository) values ('doc', 'The FreeBSD doc tree', 'github.com', '/freebsd/doc', 'git');
insert into repo (name, description, repo_hostname, path_to_repo, repository) values ('src', 'The FreeBSD src tree', 'github.com', '/freebsd/freebsd', 'git');


-- Also, the short hash.
ALTER TABLE public.commit_log
    ADD COLUMN commit_hash_short text;

COMMENT ON COLUMN public.commit_log. commit_hash_short
    IS 'This is the short version of the git hash stored in
 svn_revision.

If null/empty, it is not a git hash.';

-- we need an index on the short stuff

-- Index: commit_log_commit_hash_short

-- DROP INDEX public.commit_log_commit_hash_short;

CREATE INDEX commit_log_commit_hash_short
    ON public.commit_log USING btree
    (commit_hash_short COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

Permissions

This fixes up a lingering permissions issue – the permissions in question were too generous.

Verify this:

select grantee, privilege_type from information_schema.role_table_grants where table_name='pg_am';
select grantee, privilege_type from information_schema.role_table_grants where table_name='pg_authid';

If rsyncer is listed, then this:

revoke all PRIVILEGES ON all tables in schema pg_catalog from rsyncer;

Extensions made location

These steps are mentioned because it came up in response to these errors:

pg_restore: from TOC entry 351; 1255 81083 FUNCTION armor(bytea) dan
pg_restore: error: could not execute query: ERROR: permission denied for language c

Why is that? This is an old database, and the permissions issues I mentioned above.

This was the recommended fix. The –no-owner option is insufficient to fix this problem.

begin;
create extension pgcrypto      from unpackaged;
create extension fuzzystrmatch from unpackaged;
create extension plperl        from unpackaged;

If all good, also do a commit.

datatypes, relational integrity, and functions

With the table changes, we have updates to datatypes, triggers, and functions (stored procedures). These steps update those:

psql freshports.prod
begin;
\i datatype.txt
\i ri.txt
\i sp.txt
commit;

NOTICES during the datatype.txt command are expected. There should be no errors.

That is the last step.

Dump the fixed schema

Now we dump this new schema and all the data:

pg_dump -Fc freshports.prod > freshports.prod.new-schema.dump

Estimated time: 8 minutes

Copy to AWS

The dump was copied to AWS and loaded into the RDS instance.

scp freshports.prod.new-schema.dump ec2-user@aws:

Load fixed schema

On AWS, create and load the database:

createdb -h pg01.[redacted].us-east-1.rds.amazonaws.com -U postgres -E SQL_ASCII -T template0 --locale=C freshports.fixed
time pg_restore -h pg01.[redacted].us-east-1.rds.amazonaws.com -U postgres -d freshports.fixed freshports.prod.new-schema.dump

Estimate time: 35 minutes

These errors on loading is expected and does not affect the outcome.

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 465; 1255 847666 FUNCTION plpgsql_call_handler() postgres
pg_restore: error: could not execute query: ERROR:  permission denied for language c
Command was: CREATE FUNCTION public.plpgsql_call_handler() RETURNS language_handler
    LANGUAGE c
    AS '$libdir/plpgsql', 'plpgsql_call_handler';


pg_restore: error: could not execute query: ERROR:  function public.plpgsql_call_handler() does not exist
Command was: ALTER FUNCTION public.plpgsql_call_handler() OWNER TO postgres;

pg_restore: warning: errors ignored on restore: 2

Using the new database

Now that the new database is over on AWS, you can modifying the configuration files to start using it.

The list of configuration files which need to be updated are covered by Moving devgit.freshports.org from GitHub to git.FreeBSD.org

Dec 212020
 

Just like I moved devgit.freshports.org from github to git.freebsd.org for the doc repo on Thursday, today (Monday), I’m doing the same thing for the src repo.

The jail uses storage on an nvd-based zpool. First, create a new filesystem:

[dan@slocum:~] $ sudo zfs create nvd/freshports/devgit/ingress/repos/src

You can see it appear here in the jail, but with the wrong permissions:

[dan@devgit-ingress01:/var/db/ingress/repos] $ ls -l
total 22
drwxr-xr-x  23 ingress  ingress  27 Dec 17 23:55 doc
drwxr-xr-x  26 ingress  ingress  45 Dec 20 03:03 freebsd
drwxr-xr-x  23 ingress  ingress  26 Dec  8 07:12 freebsd-doc
drwxr-xr-x  69 ingress  ingress  84 Dec 19 09:18 freebsd-ports
drwxr-xr-x  69 ingress  ingress  84 Nov 22 19:28 freebsd-ports-quarterly
-rw-r--r--   1 ingress  ingress  41 Dec 21 18:54 latest.doc
-rw-r--r--   1 ingress  ingress  41 Dec 21 18:54 latest.freebsd
-rw-r--r--   1 ingress  ingress  41 Dec 18 00:12 latest.freebsd-doc
-rw-r--r--   1 ingress  ingress  41 Dec 21 18:54 latest.freebsd-ports
-rw-r--r--   1 ingress  ingress  41 Dec 21 18:54 latest.freebsd-ports-quarterly
drwxr-xr-x   2 root     wheel     2 Dec 21 18:55 src
[dan@devgit-ingress01:/var/db/ingress/repos] $ 

So I fix that:

[dan@devgit-ingress01:/var/db/ingress/repos] $ sudo chown ingress:ingress src

Then clone the repo:

[dan@devgit-ingress01:/var/db/ingress/repos] $ sudo su -l ingress
$ bash
[ingress@devgit-ingress01 ~]$ cd repos
[ingress@devgit-ingress01 ~/repos]$ git clone https://git.FreeBSD.org/src.git
Cloning into 'src'...
remote: Enumerating objects: 3798472, done.
remote: Counting objects: 100% (3798472/3798472), done.
remote: Compressing objects: 100% (744779/744779), done.
remote: Total 3798472 (delta 3016406), reused 3757055 (delta 2987426), pack-reused 0
Receiving objects: 100% (3798472/3798472), 1.10 GiB | 11.63 MiB/s, done.
Resolving deltas: 100% (3016406/3016406), done.
Updating files: 100% (81314/81314), done.
[ingress@devgit-ingress01 ~/repos]$ ls -l
total 24
drwxr-xr-x  23 ingress  ingress  27 Dec 17 23:55 doc
drwxr-xr-x  26 ingress  ingress  45 Dec 20 03:03 freebsd
drwxr-xr-x  23 ingress  ingress  26 Dec  8 07:12 freebsd-doc
drwxr-xr-x  69 ingress  ingress  84 Dec 19 09:18 freebsd-ports
drwxr-xr-x  69 ingress  ingress  84 Nov 22 19:28 freebsd-ports-quarterly
-rw-r--r--   1 ingress  ingress  41 Dec 21 19:03 latest.doc
-rw-r--r--   1 ingress  ingress  41 Dec 21 19:03 latest.freebsd
-rw-r--r--   1 ingress  ingress  41 Dec 18 00:12 latest.freebsd-doc
-rw-r--r--   1 ingress  ingress  41 Dec 21 19:03 latest.freebsd-ports
-rw-r--r--   1 ingress  ingress  41 Dec 21 19:03 latest.freebsd-ports-quarterly
drwxr-xr-x  26 ingress  ingress  44 Dec 21 19:00 src
[ingress@devgit-ingress01 ~/repos]$ 

It takes up about 2.5G of space:

[ingress@devgit-ingress01 ~/repos]$ du -ch -d 0 src
2.4G	src
2.4G	total

The next step: set the commit marker.

That would be commit 3cc0c0d66a065554459bd2f9b4f80cc07426464a.

Creating the commit marker:

[ingress@devgit-ingress01 ~/repos]$ echo 3cc0c0d66a065554459bd2f9b4f80cc07426464a > latest.src
[ingress@devgit-ingress01 ~/repos]$ cat latest.src
3cc0c0d66a065554459bd2f9b4f80cc07426464a
[ingress@devgit-ingress01 ~/repos]$ 

A plan for the future involves setting this via a tag in the repo.

The configuration file change is within /usr/local/etc/freshports/config.pm:

convert_repo_label_to_directory(){
  repo=$1
  
  case $repo in
     src) dir='src';;
     src-stable-12) dir='freebsd-stable-12';;
     doc) dir='doc';;
     ports) dir='freebsd-ports';;
     ports-quarterly) dir='freebsd-ports-quarterly';;
     *) dir='';;
  esac

  echo $dir
}

The fix is on line 5. It was: freebsd, it is now src.

Those values correspond to the GitHub repo name (https://github.com/freebsd/freebsd/) and the FreeeBSD repo name (https://cgit.freebsd.org/src/ respectivtely.

For the truly bored amongst you, here are the logs for the first processing of the src repo:

2020.12.21 19:18:12 git-delta.sh Now processing repo: src
2020.12.21 19:18:12 git-delta.sh REPODIR='/var/db/ingress/repos/src' exists
2020.12.21 19:18:12 git-delta.sh LATEST_FILE='/var/db/ingress/repos/latest.src' exists
2020.12.21 19:18:12 git-delta.sh Repodir is /var/db/ingress/repos/src
2020.12.21 19:18:12 git-delta.sh Running: /usr/local/bin/git pull:
warning: Pulling without specifying how to reconcile divergent branches is
discouraged. You can squelch this message by running one of the following
commands sometime before your next pull:

  git config pull.rebase false  # merge (the default strategy)
  git config pull.rebase true   # rebase
  git config pull.ff only       # fast-forward only

You can replace "git config" with "git config --global" to set a default
preference for all repositories. You can also pass --rebase, --no-rebase,
or --ff-only on the command line to override the configured default per
invocation.

Already up to date.
2020.12.21 19:18:12 git-delta.sh Done.
2020.12.21 19:18:12 git-delta.sh STARTPOINT = 3cc0c0d66a065554459bd2f9b4f80cc07426464a
2020.12.21 19:18:12 git-delta.sh Running: /usr/local/bin/git rev-list 3cc0c0d66a065554459bd2f9b4f80cc07426464a..HEAD
2020.12.21 19:18:12 git-delta.sh Done.
2020.12.21 19:18:12 git-delta.sh The commits found are:
2020.12.21 19:18:12 git-delta.sh /usr/local/libexec/freshports/git-to-freshports-xml.py --repo src --path /var/db/ingress/repos/src --commit 3cc0c0d66a065554459bd2f9b4f80cc07426464a --spooling /var/db/ingress/message-queues/spooling --output /var/db/ingress/message-queues/incoming
2020.12.21 19:18:12 git-delta.sh Ending

I did run that git config pull.rebase false later.

Dec 192020
 

My plan is to update the production website and database in place. To test this process, a copy of the production database has been copied to the pg02.int.unixathome.org PostgreSQL database server and is now available as freshports.dgnew.

The first test was the database update process. I think I have that settled now.

Pointing the devgit.freshports.org website at the database found a few missing pieces in the stored procedures.

Next, I want to try the ingress side of the website; the processing of commits.

What is involved in that process?

Configuration file changes and, to ensure integrity, stopping all processing first.

Stopping the processing

[dan@devgit-ingress01:~] $ sudo service ingress stop
Stopping ingress.
[dan@devgit-ingress01:~] $ sudo service ingress_svn stop
Stopping ingress_svn.
[dan@devgit-ingress01:~] $ sudo service freshports stop
Stopping freshports.
[dan@devgit-ingress01:~] $ 

That stops, respectively:

  1. the processing of XML files
  2. the processing of incoming svn commit emails and generation of XML files
  3. the detection of new git commits and creation of XML files

Configuration files

There are two sets of configuration files:

  • ingress – the jail used for ingesting commits into the database
  • website – the jail used for hosting the website

Usually, these run in separate jails, but they don’t have to. At present, production is running on single CPU non-ZFS system with hardware RAID and 8GB RAM. That host does not run jails.

website

The website website files are:

[dan@devgit-nginx01:/usr/local/etc/freshports] $ sudo grep freshports.devgit *
config.pm:$FreshPorts::Config::dbname			= 'freshports.devgit';
config.sh:DB=freshports.devgit
database.php:	$db = pg_connect("dbname=freshports.devgit host=pg02.int.unixathome.org user=www_dev_git password=[redacted] sslmode=require");
fp-listen.ini:DBNAME		= 'freshports.devgit'
[dan@devgit-nginx01:/usr/local/etc/freshports] $ 

Changing databases for the website also necessitates clearing the cache.

Yes, the database is specified in four different locations. We have shell scripts, python, PHP, and Perl all needing the configuration.

After making those changes, this clears the cache:

$ sudo zfs rollback system/data/freshports-cache/devgit-nginx01/ports@empty
$ sudo zfs rollback system/data/freshports-cache/devgit-nginx01/packages@empty

The website is now on the conversion test database (freshports.dgnew, as is dev git new).

ingress

The ingress configuration files are:

[dan@devgit-ingress01:/usr/local/etc/freshports] $ sudo grep freshports.devgit *
config.ini:DBNAME             = 'freshports.devgit'
config.pm:$FreshPorts::Config::dbname			= 'freshports.devgit';

The .ini file is used for processing new package building information.

The .pm file is used by the commit processing.

I will update both, let the commit processing resume, and see what happens.

*some time later*

I had some database permission issue because of missing pg_hba.conf settings, but once that was fixed, the commits started coming in:

Commits coming into the converted database

Commits coming into the converted database

Now I wait and monitor the incoming commits to be sure the ongoing processing is smooth.

Dec 172020
 

The doc repo has moved from svn to git. This changeover occurred on 2020-12-09.

The last svn commit was: 54737

The first git commit was: 3be01a475855e7511ad755b2defd2e0da5d58bbe

To date, devgit.freshports.org has been using https://github.com/freebsd/freebsd-doc/ for processing commits.

Today’s work will convert from that GitHub repo to https://cgit.freebsd.org/doc/ (actually, https://git.freebsd.org/).

What changes are required

The following changes are required:

  1. A new working copy of the git.FreeBSD.org/doc repo
  2. A marker pointing the last commit processed
  3. Configuration file changes to point to that repo

EDIT: 2021-01-01 – NOTE: all the configuration files are also maintained via Ansible – the manual steps are not necessarily required. I think I should next document the changes to Ansible when changing database names / servers.

The rest of the post documents those changes.

A new working copy of the git.FreeBSD.org/doc repo

Let’s clone that repo:

$ cd /var/db/ingress/repos
[ingress@devgit-ingress01 ~]$ cd repos
[ingress@devgit-ingress01 ~/repos]$ ls -l
total 18
drwxr-xr-x  26 ingress  ingress  45 Dec 17 03:03 freebsd
drwxr-xr-x  23 ingress  ingress  26 Dec  8 07:12 freebsd-doc´
drwxr-xr-x  69 ingress  ingress  84 Dec 17 02:18 freebsd-ports
drwxr-xr-x  69 ingress  ingress  84 Nov 22 19:28 freebsd-ports-quarterly
-rw-r--r--   1 ingress  ingress  41 Dec 17 23:51 latest.freebsd
-rw-r--r--   1 ingress  ingress  41 Dec 17 23:51 latest.freebsd-doc
-rw-r--r--   1 ingress  ingress  41 Dec 17 23:51 latest.freebsd-ports
-rw-r--r--   1 ingress  ingress  41 Dec 17 23:51 latest.freebsd-ports-quarterly
[ingress@devgit-ingress01 ~/repos]$ git clone https://git.FreeBSD.org/doc.git
Cloning into 'doc'...
remote: Enumerating objects: 449334, done.
remote: Counting objects: 100% (449334/449334), done.
remote: Compressing objects: 100% (120607/120607), done.
remote: Total 449334 (delta 314272), reused 448476 (delta 313566), pack-reused 0
Receiving objects: 100% (449334/449334), 245.50 MiB | 11.60 MiB/s, done.
Resolving deltas: 100% (314272/314272), done.
Updating files: 100% (11346/11346), done.
[ingress@devgit-ingress01 ~/repos]$ 

Done.

A marker pointing the last commit processed

In the directory listing above, you can see files starting with latest. which mirror the list of repos, which are directories. We need to create a new file, named latest.doc, which contains the hash of the last commit processed by FreshPorts.

To find that value, I did this:

  1. browse to https://cgit.freebsd.org/doc/
  2. click on main
  3. scroll down to “Mark the repository as being converted to Git.”
  4. Take that hash value

You won’t be able to reproduce that once there are enough commits and that particular commit scrolls off the page. You will always be able to find the first commit (3be01a475855e7511ad755b2defd2e0da5d58bbe) and see parent listed as 89d0233560e4ba181d73143fc25248b407120e09

Let’s put that into a file:

[ingress@devgit-ingress01 ~/repos]$ echo 89d0233560e4ba181d73143fc25248b407120e09 > latest.doc

What do we have now:

[ingress@devgit-ingress01 ~/repos]$ ls -l
total 21
drwxr-xr-x  23 ingress  ingress  27 Dec 17 23:55 doc
drwxr-xr-x  26 ingress  ingress  45 Dec 17 03:03 freebsd
drwxr-xr-x  23 ingress  ingress  26 Dec  8 07:12 freebsd-doc
drwxr-xr-x  69 ingress  ingress  84 Dec 17 02:18 freebsd-ports
drwxr-xr-x  69 ingress  ingress  84 Nov 22 19:28 freebsd-ports-quarterly
-rw-r--r--   1 ingress  ingress  41 Dec 18 00:06 latest.doc
-rw-r--r--   1 ingress  ingress  41 Dec 18 00:06 latest.freebsd
-rw-r--r--   1 ingress  ingress  41 Dec 18 00:06 latest.freebsd-doc
-rw-r--r--   1 ingress  ingress  41 Dec 18 00:06 latest.freebsd-ports
-rw-r--r--   1 ingress  ingress  41 Dec 18 00:06 latest.freebsd-ports-quarterly
[ingress@devgit-ingress01 ~/repos]$ 

Next, configuration file changes.

Configuration file changes to point to that repo

What files need changing? Just one I think.

[dan@devgit-ingress01:~] $ cd /usr/local/etc/freshports
[dan@devgit-ingress01:/usr/local/etc/freshports] $ sudo grep freebsd-doc *
config.sh:     doc) dir='freebsd-doc';;

When I make this change, I want to disable the git processing so as not to change a configuration setting in the middle of processing.

$ sudo sysrc -f /etc/periodic.conf fp_check_for_git_commits_enable="NO"
fp_check_for_git_commits_enable: YES -> NO

Let’s verify nothing is running git processing:

 ps auwwx | grep ingress
ingress     60714  0.0  0.0 10676  2188  -  SCJ  00:14   0:00.00 sleep 3
ingress_svn 60727  0.0  0.0 10676  2188  -  SCJ  00:14   0:00.00 sleep 3
ingress_svn 98912  0.0  0.0 11004  2424  -  IsJ  Sat23   0:00.15 daemon: ingress_svn[98913] (daemon)
ingress_svn 98913  0.0  0.0 11868  2968  -  SJ   Sat23   0:32.78 /bin/sh /usr/local/libexec/freshports-service/ingress_svn.sh
ingress     98921  0.0  0.0 11004  2424  -  IsJ  Sat23   0:00.44 daemon: ingress[98923] (daemon)
ingress     98923  0.0  0.0 11844  2952  -  SJ   Sat23   0:21.70 /bin/sh /usr/local/libexec/freshports-service/ingress.sh
dan         60729  0.0  0.0 11384  2764  2  S+J  00:14   0:00.00 grep ingress

That’s normal. ingress_svn is the daemon checking for incoming svn commits.

ingress is the daemon which looks for incoming git commits, but that’s the daemon which processes the XML files. The periodic.conf setting we adjusted is for the creation of those XML files. That is what we are pausing now.

Next, I updated config.sh. This is what we have now:

[dan@devgit-ingress01:/usr/local/etc/freshports] $ sudo grep doc *
config.pm:# Values as found at https://www.postgresql.org/docs/current/static/libpq-ssl.html
config.pm:$FreshPorts::Config::Repo_DOC             = 'doc';
config.pm:$FreshPorts::Config::DB_Root_Prefix_DOC             = '/doc';
config.sh:# see https://www.postgresql.org/docs/12/libpq-envars.html
config.sh:     doc) dir='doc';;

That last line is the updated valud. The others refer to repo names and internal pathnames within the FreshPorts database.

What’s next?

Turn on git commit processing and watch the logs.

$ sudo sysrc -f /etc/periodic.conf fp_check_for_git_commits_enable="YES"
fp_check_for_git_commits_enable: NO -> YES
$ 

Now I wait until 7:24, because the script runs every 3 minutes.

BOOM! The latest commit is now in: 482d8311b8a1e25a66ee49af4bc7efadd8be22aa

Nov 292020
 

Some blog posts serve to help me think through to a solution. This blog post is just for that.

Today I realized the code needs to handle both git and svn. I thought I would have one cut-over date after which all commits would go through git. I see now that this isn’t the way to go. The code has to be ready to import both git and svn commits. But not from the same tree. We don’t want duplicates.

We have three repos:

  1. doc
  2. ports
  3. src

So what next?

Today, I’m going to take an XML file from dev and see if devgit can import it. I fully expect errors.

Use of uninitialized value $Updates{"commit_hash"} in concatenation (.) or string at /usr/local/lib/perl5/site_perl/FreshPorts/xml_munge_git.pm line 622.
Use of uninitialized value $Updates{"FileRevision"} in concatenation (.) or string at /usr/local/lib/perl5/site_perl/FreshPorts/xml_munge_git.pm line 623.
Use of uninitialized value $FileRevision in concatenation (.) or string at /usr/local/lib/perl5/site_perl/FreshPorts/xml_munge_git.pm line 624.
no value set for incoming RepoName at /usr/local/lib/perl5/site_perl/FreshPorts/xml_munge_git.pm line 555.

And we have them. Yes, there is no commit_hash in the subversion XML.

The ingress code is different, to handle a commit_hash, both long ( 1cabbda44f7f82543402b6a988976020afda2c46) and short (1cabbda).

There is no code in the git branch to handle importing svn commits.

What about keeping the two websites separate?

Let’s consider this scenario.

doc starts using git first. Then src, then ports.

Let git.freshports.org process the git commits. Let www.freshports.org process the svn commits.

When everything is transitioned to git, promote git.freshports.org to www.freshports.org

No, that won’t work. The databases are separate. The git website won’t have all the commits which were processed by the svn website….

It has to be one database

We know the git database can handle svn commits, because they are already present. We know the website can already display svn commits, because it is.

Yes, it’s just the ingress side which has to be updated now. Or rather, svn-specific code merged into the git branch.

This might be feasible if we keep svn and git processing completely separate and don’t try to make one do both.

Avoiding concurrency issues

FreshPorts has always process one commit at a time, in the order they are received. For cvs and svn, ‘order’ was defined by when the commit mailing list email was received. Commits received out of order will produce interesting results, such as a port version decreasing. There is no simple solution to that issue as far as I know.

For git, commits are again processed in order, according to however they appear in the tree, not commit date order.

But with two input streams, I’d rather avoid having two commits being processed at the same time. It is unlikely that any concurrency issues would arise, but I’d rather just avoid that.

That means two separate message queues and processing, but only one consumer of those two queues.

The svn outline

For svn, commits are processed like this:

  1. email arrives
  2. raw email is dumped into ~ingress/message-queues/incoming/2020.11.29.17.43.12.53448.txt
  3. the above is all handled by ~ingress/.mailfilter and these configuration settings in /usr/local/etc/postfix/main.cf
    mailbox_command = /usr/local/bin/maildrop -d ${USER}
    setgid_group = maildrop
    
  4. fp-daemon.sh sees 2020.11.29.17.43.12.53448.txt
  5. XML is created and dumped into 2020.11.29.17.43.12.53448.txt.xml but in ~freshports/message-queues/recent/
  6. XML is processed and loaded into the database.

The git outline

For git processing, there is no incoming email. Instead, we poll the local working copy of the git repo after a git fetch.

  1. The FreeBSD periodic system invokes /usr/local/etc/periodic/everythreeminutes/215.fp_check_git_for_commits
  2. If a new commit is found, it is extracted from the repo and a new file is created: ~ingress/message-queues/incoming/2020.10.01.19.50.02.000000.4796a64ade4267608e861f717e443c0290b73b70.xml – yes, that is a timestamp and a commit hash in that filename.
  3. The freshports daemon notices a new file in the incoming directory.
  4. XML is processed and loaded into the database.

Joining the two outlines

The solution I see is the modify both outlines so they stop at creating the XML file in different directories.

The freshports daemon then scans both directories and processes them accordingly.

The fp-daemon code (or more specifically, the code it invokes) will be modified so it only creates the XML and does not process it.