OpenText Secure Messaging Gateway (SMG)

Remove a Blacklisted Address Manually from the Postgresql Database

Published 8/27/2025

These instructions provide a step-by-step process for locating and removing blacklisted email addresses and recipient domain rules from the SecureGateway (SMG) PostgreSQL database. By following this guide, an administrator can identify whether an address is explicitly blocked, discover any sender-to-recipient pair rules that enforce the block, and then safely delete those entries from the database. The end result is that legitimate email traffic from the specified sender (or domain) will no longer be rejected by SMG due to blacklist rules.

Why use this procedure:
This method is useful if the SMG Admin interface fails to remove a blacklist entry, or if the web interface hangs or becomes unresponsive when attempting to manage blacklisted addresses or domains.

Disclaimer:
The procedures described in this document involve making direct modifications to the SecureGateway database. Such actions may have unintended consequences, including disruption of mail flow, loss of data, or system instability if executed incorrectly. These instructions are provided “as is” without any warranties, express or implied. By using them, you acknowledge and agree that you are solely responsible for any risks, outcomes, or damages that may result. It is strongly recommended that you perform a full database backup prior to making changes and that you validate all steps in a non-production or test environment whenever possible.

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.

Preface | Note about Example Text

In the steps below, replace the sample blacklist address (someone@examplemail.com) with the actual sender address you need to search for and remove. Likewise, replace the placeholder recipient domain (@megacorp.example) with your own organization’s domain. These examples are provided as safe stand-ins so you can copy/paste the commands directly, but be sure to substitute them with the real values relevant to your environment before running any queries or deletes.

1) Connect to the right DB

sudo -u postgres psql -d "SecureGateway"

2) Look up the sender address and get its ID

\set target 'someone@examplemail.com' -- <<< set to the exact sender you’re unblocking

SELECT idblackwhitelistaddress, address
FROM public.blackwhitelistaddress
WHERE lower(address) = lower(:'target');

Note the returned idblackwhitelistaddress (call it :addr_id).

3) Check if any pair rules reference that address

\set addr_id 123456 -- <<< replace with the id from step 2

SELECT p.idblacklistaddresspair, s.address AS sender, r.address AS recipient
FROM public.blacklistaddresspair p
LEFT JOIN public.blackwhitelistaddress s ON s.idblackwhitelistaddress = p.idsenderaddress
LEFT JOIN public.blackwhitelistaddress r ON r.idblackwhitelistaddress = p.idrecipientaddress
WHERE p.idsenderaddress = :'addr_id' OR p.idrecipientaddress = :'addr_id';

4) Remove the pairs and the address (transaction-safe)

BEGIN;

DELETE FROM public.blacklistaddresspair
WHERE idsenderaddress = :'addr_id' OR idrecipientaddress = :'addr_id';

DELETE FROM public.blackwhitelistaddress
WHERE idblackwhitelistaddress = :'addr_id';

COMMIT;

5) Verify cleanup

SELECT * FROM public.blackwhitelistaddress WHERE idblackwhitelistaddress = :'addr_id';

SELECT * FROM public.blacklistaddresspair
WHERE idsenderaddress = :'addr_id' OR idrecipientaddress = :'addr_id';

(Optional) Check for domain-level rules

A) Sender wildcard/domain (e.g., any sender at a provider)

SELECT idblackwhitelistaddress, address
FROM public.blackwhitelistaddress
WHERE address IN ('*@examplemail.com','examplemail.com')
OR address ~* '@examplemail\.com$';

B) Recipient domain (e.g., your organization)

Use the placeholder domain here instead of the real one:

-- Find all recipient entries at the org
SELECT idblackwhitelistaddress, address
FROM public.blackwhitelistaddress
WHERE address ILIKE '%@megacorp.example';-- List pair rules that block senders → recipients at the org
SELECT p.idblacklistaddresspair, s.address AS sender, r.address AS recipient
FROM public.blacklistaddresspair p
JOIN public.blackwhitelistaddress s ON s.idblackwhitelistaddress = p.idsenderaddress
JOIN public.blackwhitelistaddress r ON r.idblackwhitelistaddress = p.idrecipientaddress
WHERE r.address ILIKE '%@megacorp.example';

C) Delete those recipient-domain pairs (if needed)

BEGIN;

DELETE FROM public.blacklistaddresspair
WHERE idrecipientaddress IN (
SELECT idblackwhitelistaddress
FROM public.blackwhitelistaddress
WHERE address ILIKE '%@megacorp.example'
);

COMMIT;

(If you prefer to repoint them to a generic catch-all instead of deleting, insert/find *@placeholder.example in blackwhitelistaddress and UPDATE the pairs’ idrecipientaddress to that ID.)

6) Reload SMG Services

You will need to restart the SMG Services after manual database manipulation so the changes will go into effect.

Quick notes

  • Core tables used:

    • public.blackwhitelistaddress — stores the actual address strings.

    • public.blacklistaddresspair — enforces blocks by linking sender ID ↔ recipient ID.

  • Always delete pairs first, then the address row.

  • Use the \set variables to avoid typos and keep steps repeatable.