OpenText Secure Messaging Gateway (SMG)

Export All Blacklisted Addresses from Postgresql Database

Published 8/27/2025

Introduction

Over many years of use, the SecureGateway appliance can accumulate tens of thousands of blacklisted sender/recipient email address pairs. While the administrative interface provides visibility into the total count, it does not make it easy to extract or review these entries in bulk. The following instructions allow you to export all blacklisted pairs directly from the PostgreSQL database into a CSV file. This provides a structured, searchable, and portable copy of all blacklist entries for external review, auditing, or cleanup.

Note: The system does not appear to maintain a reliable timestamp for when an address was originally blacklisted. The export therefore includes only the sender address, recipient address, and the database pair ID (which can be used to infer relative order, but not exact dates).

Why You Might Need This
Administrators may want to perform this export if:

  • The admin interface is slow, unresponsive, or hangs when loading the blacklist.
  • The interface fails to properly delete or manage entries.
  • An external review, migration, or bulk cleanup of the blacklist is required.
Disclaimer
These instructions read data from your SecureGateway database and do not modify it. Still, use at your own risk. Environment differences (schema changes, permissions, paths) can affect results. Validate in a non-production context when possible and ensure you comply with your organization’s data handling policies.

Product Version Note
These steps have been tested and verified on the Appliance version of OpenText Secure Messaging Gateway (SMG) Build 23.3.17. The commands themselves are written for PostgreSQL and should be expected to work on any SMG version that uses the same PostgreSQL database schema. Minor differences may exist depending on schema changes in future or older builds, so always confirm table and column names in your environment before executing.

Instructions

  1. Connect to your SMG server using SSH or console as an administrator.

  2. Run the following command to export the blacklist entries into a CSV file:

    sudo -u postgres psql -d SecureGateway -c "COPY (
    SELECT
    p.idblacklistaddresspair,
    s.address AS sender_address,
    r.address AS recipient_address
    FROM public.blacklistaddresspair p
    JOIN public.blackwhitelistaddress s ON s.idblackwhitelistaddress = p.idsenderaddress
    JOIN public.blackwhitelistaddress r ON r.idblackwhitelistaddress = p.idrecipientaddress
    ORDER BY p.idblacklistaddresspair
    ) TO STDOUT WITH (FORMAT CSV, HEADER);"     > /home/vaadmin/blacklist_export.csv

    • Adjust the final path (/home/vaadmin/blacklist_export.csv) as needed to a writable location on your system.

    • The output file will contain three columns:

      • idblacklistaddresspair (internal database ID, useful for relative ordering or deletion)

      • sender_address

      • recipient_address

  3. Verify the export:

    head -n 10 /home/vaadmin/blacklist_export.csv
    wc -l /home/vaadmin/blacklist_export.csv

    This confirms the first few lines and total number of records.

Using the Exported Blacklist CSV

Once you have generated blacklist_export.csv, you can open it in Excel, LibreOffice Calc, or any spreadsheet or database analysis tool. The file contains three columns:

  • idblacklistaddresspair — the unique internal identifier for the blacklist entry

  • sender_address — the blocked sender address or domain pattern

  • recipient_address — the recipient address the rule applies to

Understanding the ID Column

Each blacklist entry is assigned a unique idblacklistaddresspair value at the time it is created. These IDs are generated sequentially by the database. This means:

  • Lower ID numbers correspond to older entries

  • Higher ID numbers correspond to newer entries

  • Sorting the CSV by this column allows you to view the blacklist in relative chronological order (oldest to newest), even though exact timestamps are not available

Practical Uses of the CSV

  • Audit & Review: Search, filter, or pivot the data to see how many addresses target specific recipients, or identify large blocks from the same sender domain.

  • Cleanup Planning: Sort by ID to identify the oldest entries that may no longer be relevant, or to spot duplicate sender/recipient combinations.

  • Cross-Referencing: Compare the CSV against email system logs or security incident records to validate why certain entries exist.

  • Bulk Operations: The idblacklistaddresspair values can be used later in SQL scripts to remove selected entries if cleanup is required.