Mail::...qlList(3pm)

💖 Mail::...qlList — the mtpolicyd SQL list plugin 💖

okay bestie, sit down, hydrate, because we're talking about Mail::MtPolicyd::Plugin::SqlList — the mtpolicyd plugin for accessing a SQL white/black/access list. it's giving gatekeeper. it's giving bouncer-at-the-club-but-in-SQL. no cap. 🎀

it's a User Contributed Perl Documentation page, and honestly? iconic of her.

---

✨ VERSION ✨

version 2.05 — she's been through some things and she's thriving 💅

---

🌸 SYNOPSIS 🌸

here's the vibe, verbatim, don't touch a single character:

<Plugin whitelist>
  module="SqlList"
  sql_query="SELECT client_ip FROM whitelist WHERE client_ip=?"
  match_action=dunno
</Plugin>

<Plugin blacklist>
  module="SqlList"
  sql_query="SELECT client_ip FROM blacklist WHERE client_ip=?"
  match_action="reject you are blacklisted!"
</Plugin>

see how one of them is nice (dunno) and one of them is mean (reject you are blacklisted!)? that's range. that's main character energy. 💀

---

🦋 DESCRIPTION 🦋

so what does this plugin actually DO? it checks a field against a SQL table/query. that's it. that's the whole job. 🔥

and depending on whether a supplied SQL query matched, actions can be taken. so like — matched? something happens. didn't match? something else happens. the drama is entirely configurable. 🫶

🎀 PARAMETERS 🎀

the module takes the following parameters, and yes we're listing ALL of them, bestie, every single one, no skipping:

• (uc_)enabled (default: "on") — could be set to 'off' to deactivate the check. could be used to activate/deactivate the check per user. it's giving ✨ selective attention ✨.
• sql_query (default: "SELECT client_ip FROM whitelist WHERE client_ip=INET_ATON(?)") — the prepared SQL statement to use for checking an IP address.
  • the ? will be replaced by the IP address. literally, that's the whole substitution situation.
  • the module will match if the statement returns one or more rows. one row? matched. fifty rows? matched. zero rows? not matched, sorry babes. 😭

lowkey important: by default the plugin will do nothing. NOTHING. she's just standing there. one of the following actions should be specified, otherwise what are we even doing here 💀

so, pick your fighter:

• match_action (default: empty) — if given, this action will be returned to the MTA if the SQL query matched. ✨
• not_match_action (default: empty) — if given, this action will be returned to the MTA if the SQL query DID NOT match. yes, the capitalization of DID NOT is the original's, and honestly? valid. it's giving emphasis.
• score (default: empty) — if given, this score will be applied to the session. points. on the board. 🌈
• field (default: client_address) — the field the query parameter will be taken from. that's where we're pulling the value from, bestie. 👑

---

🍓 EXAMPLE WITH A MYSQL TABLE 🍓

you may use the following table for storing IPv4 addresses in MySQL. here it is, exact, do not improvise:

CREATE TABLE `whitelist` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_ip` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `client_ip` (`client_ip`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1

INSERT INTO whitelist VALUES(NULL, INET_ATON('127.0.0.1'));

look at that UNIQUE KEY on client_ip. she said no duplicates in THIS house. 💅

and then you use it as a whitelist in mtpolicyd like so:

<VirtualHost 12345>
  name="reputation"
  <Plugin whitelist>
    module="SqlList"
    sql_query="SELECT client_ip FROM whitelist WHERE client_ip=INET_ATON(?)"
    match_action="dunno"
  </Plugin>
  <Plugin trigger-greylisting>
  ...
</VirtualHost>

see the ... at the end? that's not me being lazy, that's the original being mysterious. the manpage is keeping secrets. respect it. 🙈

---

💜 AUTHOR 💜

Markus Benning <ich@markusbenning.de> — the author. the originator. the moment. 👑

---

👑 COPYRIGHT AND LICENSE 👑

This software is Copyright (c) 2014 by Markus Benning <ich@markusbenning.de>.

This is free software, licensed under:

The GNU General Public License, Version 2, June 1991

free software, GPL v2, June 1991 — a classic, a legend, an elder stateswoman of licensing. 🌸

---

and that's the whole page, bestie!! 🥰 this was Mail::...qlList(3pm), page rendered with perl v5.38.2 on 2024-05-26 — mwah, go configure your whitelist, you absolute icon 💖✨🦋