OpenDKIM: Use MySQL socket in KeyTable/SigningTable

Question

How can i connect to a MySQL socket (not TCP) with e.g. KeyTable/SigningTable in OpenDKIM?

The dataset which needs to be used is "dsn:" and the manual says:

If the string begins with "dsn:" and the OpenDKIM library was compiled to support that database type, then the remainder of the string is a Data Store Name describing the type, location parameters and access credentials for an ODBC or SQL database. The DSN is of the form: backend://[user[:pwd]@][port+]host/dbase[/key=value[?...]]

where backend is the name of a supported backend database mechanism (e.g. "mysql"), user and password are optional login credentials for the database, port and host describe the destination of a TCP connection to connect to that database, dbase is the name of the database to be accessed, and the key=value pairs must specify at least "table", "keycol" and "datacol" values specifying the name of the table, the name of the column to consider as the key, and the name(s) of the column(s) to be considered as the values (separated by commas). For example (all in one line):

mysql:://dbuser:dbpass@3306+dbhost/odkim/table=macros ?keycol=host?datacol=v1,v2

defines a MySQL database listening at port 3306 on host "dbhost"; the userid "dbuser" and password "dbpass" should be used to access the database; the database name is "odkim", and the data are in columns "host" (the keys) and "v1" and "v2" (the values) inside table "macros". This example would thus return two values when a match is found.

No value within the DSN may contain any of the six punctuation characters (":", "/", "@", "+", "?" and "=") used to separate portions of the DSN from each other.

It seems it's not possible to connect to MySQL socket, but only via TCP?

Answer 1

OpenDKIM uses OpenDBX library to connect to backend databases, including MySQL; and currently only OpenDBX 1.2+ supports UNIX sockets for MySQL. OpenDBX 1.1 only support UNIX sockets for PostgreSQL.

Furthermore, OpenDKIM limits its configurations to use DSN strings, requiring it to parse DSN string into its components (host, port, database, ...).

Even though it's possible for OpenDBX to use UNIX sockets to connect to MySQL, even in the latest master (20220402) branch, OpenDKIM expects / character (after DSN backend) as a separator between host+port and database name, not an UNIX socket path component. Using DSN value like this:

mysql://user:pass@3306+/path/to/mysql.socket/odkim/table=macros?keycol=host?datacol=v1,v2

should result an empty hostname (dsn->dsn_host) and OpenDKIM return an error.

Probably open an issue on their Github page to raise this concern to the developers.

Answer 2

I've just setup OpenDKIM to connect to MySQL on its socket.

First, create the user opendkim in MySQL Server :

CREATE USER 'opendkim'@'localhost' IDENTIFIED VIA unix_socket;
GRANT SELECT ON opendkim.* TO 'opendkim'@'localhost';

And add these lines in opendkim config file (/etc/opendkim.conf on Debian) :

SigningTable dsn:mysql://localhost/opendkim/table=dkim?keycol=domain_name?datacol=id
KeyTable     dsn:mysql://localhost/opendkim/table=dkim?keycol=id?datacol=domain_name,selector,private_key

As you can see, you only have to define the hostname of the MySQL server (no user, no password) : localhost.

Configuration :

• Debian 11
• OpenDKIM 2.11.0 (installed from apt)
• libopendbx1-mysql 1.4.6
• MariaDB Server 10.5.15