init query failed: pq: type "longtext" does not exist

[jpatters]

Describe the bug

When starting maddy configured to use postgres, the error init query failed: pq: type "longtext" does not exist prevents it from starting.

Steps to reproduce

Try to start maddy when configured to use postgres

Log files

syslog

Aug 21 01:10:15 localhost maddy[21346]: [debug] /etc/maddy/maddy.conf:183: register config block remote_queue []
Aug 21 01:10:15 localhost maddy[21346]: [debug] /etc/maddy/maddy.conf:104: new module limits []
Aug 21 01:10:15 localhost maddy[21346]: [debug] /etc/maddy/maddy.conf:46: new module sql_table []
Aug 21 01:10:15 localhost maddy[21346]: tls.loader.acme/maintenance: started background certificate maintenance#011{"cache":"0xc0001dd260"}
Aug 21 01:10:15 localhost maddy[21346]: [debug] /etc/maddy/maddy.conf:110: reference &local_authdb
Aug 21 01:10:15 localhost maddy[21346]: [debug] systemd: STATUS="init query failed: pq: type "longtext" does not exist"
Aug 21 01:10:15 localhost maddy[21346]: init query failed: pq: type "longtext" does not exist
Aug 21 01:10:15 localhost systemd[1]: maddy.service: Main process exited, code=exited, status=2/INVALIDARGUMENT
Aug 21 01:10:15 localhost systemd[1]: maddy.service: Failed with result 'exit-code'.
Aug 21 01:10:15 localhost systemd[1]: Failed to start maddy mail server.

Configuration file

maddy.conf

$(hostname) = mx1.example.com
$(primary_domain) = example.com
$(local_domains) = $(primary_domain)

debug true

tls {
    loader acme {
        hostname mx1.example.com
        email [email protected]
        agreed true
        challenge dns-01
        dns cloudflare {
            api_token "..."
        }
    }
}

auth.pass_table local_authdb {
    table sql_table {
        driver postgres
        dsn "user=maddy dbname=maddy"
        table_name passwords
    }
}

storage.imapsql local_mailboxes {
    driver postgres
    dsn "user=maddy dbname=maddy"

    msg_store s3 {
        endpoint s3compat.com
        access_key "..."
        secret_key "..."
        bucket bucket-name
        region a-valid-region
    }
}

hostname $(hostname)

table.chain local_rewrites {
    optional_step regexp "(.+)\+(.+)@(.+)" "$1@$3"
    optional_step file /etc/maddy/aliases
}

msgpipeline local_routing {
    destination postmaster $(local_domains) {
        modify {
            replace_rcpt &local_rewrites
        }

        deliver_to &local_mailboxes
    }

    default_destination {
        reject 550 5.1.1 "User doesn't exist"
    }
}

smtp tcp://0.0.0.0:25 {
    limits {
        # Up to 20 msgs/sec across max. 10 SMTP connections.
        all rate 20 1s
        all concurrency 10
    }

    auth &local_authdb

    dmarc yes
    check {
        require_mx_record
        dkim
        spf
    }

    source $(local_domains) {
        reject 501 5.1.8 "Use Submission for outgoing SMTP"
    }
    default_source {
        destination postmaster $(local_domains) {
            deliver_to &local_routing
        }
        default_destination {
            reject 550 5.1.1 "User doesn't exist"
        }
    }
}

submission tls://0.0.0.0:465 tcp://0.0.0.0:587 {
    limits {
        # Up to 50 msgs/sec across any amount of SMTP connections.
        all rate 50 1s
    }

    auth &local_authdb

    source $(local_domains) {
        check {
            authorize_sender {
                prepare_email &local_rewrites
                user_to_email identity
            }
        }

        destination postmaster $(local_domains) {
            deliver_to &local_routing
        }
        default_destination {
            modify {
                dkim $(primary_domain) $(local_domains) default
            }
            deliver_to &remote_queue
        }
    }
    default_source {
        reject 501 5.1.8 "Non-local sender domain"
    }
}

target.remote outbound_delivery {
    limits {
        # Up to 20 msgs/sec across max. 10 SMTP connections
        # for each recipient domain.
        destination rate 20 1s
        destination concurrency 10
    }
    mx_auth {
        dane
        mtasts {
            cache fs
            fs_dir mtasts_cache/
        }
        local_policy {
            min_tls_level encrypted
            min_mx_level none
        }
    }
}

target.queue remote_queue {
    target &outbound_delivery

    autogenerated_msg_domain $(primary_domain)
    bounce {
        destination postmaster $(local_domains) {
            deliver_to &local_routing
        }
        default_destination {
            reject 550 5.0.0 "Refusing to send DSNs to non-local addresses"
        }
    }
}

imap tls://0.0.0.0:993 tcp://0.0.0.0:143 {
    auth &local_authdb
    storage &local_mailboxes
}

Environment information

• maddy version: 0.5.0

[foxcpp]

Ouch. It is quite embarrassing I have been aware of this little issue but forgot to include the fix in 0.5.0.

As a workaround you could manually create the table with correct data types:

CREATE TABLE password (key TEXT PRIMARY KEY NOT NULL, value TEXT NOT NULL);

then this error should not occur.

Sorry about that!

[jpatters]

@foxcpp thanks for the quick response. I changed it and rebuilt from source, so I was able to work around it. It was just late so I didn't get the chance to put up a PR.
If I may use this thread to ask another quick question, I've maddy running with postgres now but get the following when I run maddyctl imap-acct create [email protected].

Failed to initialize update pipe, do not remove messages from mailboxes open by clients: imapsql: driver does not have an update pipe implementation

Is this causing any issues? I can't seem to connect via IMAP and am wondering if this is the reason for that.

[foxcpp]

Is this causing any issues? I can't seem to connect via IMAP and am wondering if this is the reason for that.

Unlikely. This affects only message removal using imap-msgs remove.

[jpatters]

Thanks again. I found the issue and will open another ticket for it.

[foxcpp]

I suppose we could close this one...

[jpatters]

Yes. Thanks for the quick fix!