Give AI access to your database while masking customer PII with GateRook

An AI assistant can help investigate a subscription problem, explain an unfamiliar schema, or calculate revenue by plan. Those tasks rarely require a customer's name, email address, phone number, date of birth, or home address.

Yet a perfectly ordinary SQL query can return all of them. Once the tool response reaches the AI client, those values may enter the model's context, the conversation history, or another downstream system. Asking the assistant to leave personal details out of its final answer happens too late: it has already received them.

This walkthrough puts GateRook between the AI app and PostgreSQL. We will let the assistant query a small customer database while GateRook replaces selected personal data with ‹masked› before returning the result. Then we will run useful analysis over the remaining data and inspect the actual AI call in Activity.

Disclosure: I build GateRook, an open-source desktop MCP Firewall. Its application source is on GitHub. The example below uses GateRook 0.9.2 and PostgreSQL 16.13. Every customer record is fictional.

Here is the result we are aiming for:

Five protected values per customer, six customers: GateRook reports 30 masked fields. This is an actual query result in Playground, which uses the same policy and masking pipeline as an agent call.

Where the privacy boundary belongs

A read-only database account is a useful starting point. It limits changes to the database, but it can still read every sensitive column its grants permit.

For this workflow, the request and response take this path:

AI app
  -> MCP endpoint in GateRook
  -> endpoint scope, tool mode, policy and execution limits
  -> PostgreSQL using credentials held by GateRook
  -> result masking in GateRook
  -> masked MCP response to the AI app

The model asks for a tool such as sql_query. It supplies a connection name and SQL, rather than a database password. GateRook checks whether that endpoint exposes the tool and connection, executes an allowed request, and processes its result before the AI receives it.

There are three separate decisions to make:

Decision	Control
Which database objects can this connection read?	PostgreSQL grants and GateRook resource restrictions
Which tools may the AI invoke, and when is review required?	Endpoint tool modes and policy
Which returned values should be hidden?	Pattern-based PII masking and explicit field rules

Keeping these decisions separate is useful. An allowed query can still have sensitive output. Masking a result does not grant permission to execute a query that was otherwise forbidden.

The gateway must be on the access path. A second MCP server with unrestricted database access, a database password in the agent's environment, or a direct SQL connection creates a separate path outside these controls.

1. Prepare a database containing only fictional customers

Use a disposable database for your first test. The commands below create a PostgreSQL container reachable through the local loopback address. If port 15432 is already occupied, choose another port and use the same value in GateRook.

export POSTGRES_PASSWORD="$(openssl rand -hex 24)"

docker run -d \
  --name gaterook-postgres-pii \
  -p 127.0.0.1:15432:5432 \
  -e POSTGRES_PASSWORD \
  -e POSTGRES_DB=gaterook_demo \
  --health-cmd 'pg_isready -U postgres -d gaterook_demo' \
  --health-interval 2s \
  --health-timeout 3s \
  --health-retries 30 \
  postgres:16.13

unset POSTGRES_PASSWORD

docker inspect --format '{{.State.Health.Status}}' \
  gaterook-postgres-pii

Wait for healthy. Save the following SQL as customers.sql:

CREATE ROLE demo_agent LOGIN PASSWORD 'local-demo-only';

CREATE TABLE public.customers (
    customer_id integer PRIMARY KEY,
    full_name text NOT NULL,
    email text NOT NULL,
    phone text NOT NULL,
    birth_date date NOT NULL,
    street_address text NOT NULL,
    country_code char(2) NOT NULL,
    plan text NOT NULL,
    status text NOT NULL,
    monthly_revenue numeric(10,2) NOT NULL
);

INSERT INTO public.customers VALUES
    (1, 'Avery Stone', '[e-mail]', '[phone]',
     '1988-04-12', '12 Example Lane', 'ES', 'pro', 'active', 49.00),
    (2, 'Morgan Reed', '[e-mail]', '[phone]',
     '1991-09-03', '24 Sample Street', 'DE', 'business', 'active', 199.00),
    (3, 'Riley Chen', '[e-mail]', '[phone]',
     '1985-02-21', '36 Demo Road', 'ES', 'pro', 'cancelled', 0.00),
    (4, 'Jordan Lee', '[e-mail]', '[phone]',
     '1995-11-08', '48 Fiction Avenue', 'GB', 'free', 'active', 0.00),
    (5, 'Casey Park', '[e-mail]', '[phone]',
     '1990-06-17', '60 Example Lane', 'ES', 'business', 'active', 199.00),
    (6, 'Taylor Quinn', '[e-mail]', '[phone]',
     '1993-01-25', '72 Sample Street', 'DE', 'pro', 'active', 49.00);

GRANT CONNECT ON DATABASE gaterook_demo TO demo_agent;
GRANT USAGE ON SCHEMA public TO demo_agent;
GRANT SELECT ON public.customers TO demo_agent;

Load it as the database administrator:

docker exec -i gaterook-postgres-pii \
  psql -U postgres -d gaterook_demo -v ON_ERROR_STOP=1 \
  < customers.sql

The publicly documented password local-demo-only belongs only to this disposable local fixture. Use a separately managed credential for a real database.

The connection role gets SELECT on this table. It is neither the table owner nor the PostgreSQL administrator, and the script grants it no write privileges. In an existing database, also inspect role memberships and grants to PUBLIC: privileges are cumulative. See PostgreSQL's GRANT documentation.

2. Connect PostgreSQL to a scoped GateRook endpoint

Install GateRook from gaterook.com, then open Connections → Add connection → PostgreSQL.

For this local fixture, use:

Setting	Value
Connection name	postgres-demo
Host	127.0.0.1
Port	15432
User	demo_agent
Password	The local fixture password
Secret source	Encrypted here
Environment	DEV / Development
Discovery database	gaterook_demo
Selected databases	Only gaterook_demo
Schema allowlist	public
Default schema	public

The disposable container above has no TLS listener, so its local connection uses SSL mode: Off (disable). For a remote database, configure TLS and use Verify the CA and host name (verify-full) with the appropriate CA certificate.

Test the connection before saving it. Add it to an endpoint dedicated to the workflow; mine is named showcase. An endpoint determines which connections and tools a particular AI app can use. Do not select unrelated endpoints in the connection wizard.

The three tools needed here are:

Tool	Purpose	Mode
sql_list_schemas	Discover accessible schemas and tables	Read only
sql_describe_table	Read columns, types, keys and indexes	Read only
sql_query	Execute a validated read query	Read only

For an analytics-only endpoint, set sql_execute to Off. My existing demonstration endpoint also illustrates the Approval mode for writes, as shown below; no write tool is used in this article, and the customer-table grant is still SELECT-only.

Tool permissions and output masking solve different problems. The screenshot shows the endpoint's existing modes; an analytics-only setup can disable the write tool entirely.

Connect your AI app through AI apps, selecting this endpoint. Use GateRook's client setup flow rather than putting the PostgreSQL credential into the AI app's configuration. The getting-started guide covers the connection workflow.

3. Turn on PII masking in Development

Open Policies → Development, then scroll to Masking.

This is an important step for a DEV test: in GateRook 0.9.2, the built-in PII categories are enabled by default for PROD, while DEV starts with secret masking and no enabled PII categories. STAGE inherits individual settings from DEV unless it overrides them. Always inspect the effective policy for the connection's environment.

Under Personal data, found by pattern, enable:

• Email addresses
• Phone numbers
• Card numbers
• IBANs

These detectors inspect values regardless of their field names. An email address does not have to appear in a column literally called email to match.

Under Always mask these fields, add each of these entries with the plus button:

customers.full_name
customers.birth_date
customers.street_address

Then select Save dev policy. The saved rules apply from the next call.

This is the Masking section under Policies → Development. The explanatory text identifies its scope: every DEV connection.

The distinction between the two mechanisms matters:

Example	How this configuration protects it
[e-mail]	Email pattern
[phone]	Phone pattern
Avery Stone in customers.full_name	Explicit field rule
A date in customers.birth_date	Explicit field rule
An address in customers.street_address	Explicit field rule

There is no general-purpose name or postal-address detector among those four PII checkboxes. Field rules cover sensitive columns whose values do not have one reliable universal shape. Add your own application-specific fields, such as a national identifier or a private support note, rather than assuming an email detector covers every kind of personal data.

SQL field rules can use table.column or schema.table.column; a rule such as *.birth_date covers that field name across tables. Prefer a schema-qualified rule when identically named tables need different treatment. The three customer-field rules above are specific to our fixture.

Policy scope is broader than the endpoint used for the demo. A DEV policy change affects every DEV connection, and inherited STAGE settings can change with it. Review that scope before using the same procedure in a shared setup.

4. Verify what actually reaches the AI

First, test in Playground. Choose endpoint showcase, tool sql_query, and connection postgres-demo. Set the result limit to 10 and run:

SELECT customer_id, full_name, email, phone,
       birth_date, street_address, plan, status, monthly_revenue
FROM public.customers
ORDER BY customer_id;

This intentionally includes sensitive columns so that the masking behavior is visible. For normal analysis, select only the columns the task needs.

The screenshot near the beginning shows the actual result: all six rows remain present, but five values in each row are replaced with ‹masked›. The useful business fields keep their values.

Next, run the same query through your connected AI app. The MCP tool arguments are ordinary JSON:

{
  "connection": "postgres-demo",
  "sql": "SELECT customer_id, full_name, email, phone, birth_date, street_address, plan, status, monthly_revenue FROM public.customers ORDER BY customer_id;",
  "limit": 10
}

Here is one row from the actual structured response, with column order preserved:

{
  "columns": [
    "customer_id", "full_name", "email", "phone",
    "birth_date", "street_address", "plan", "status", "monthly_revenue"
  ],
  "rows": [
    [1, "‹masked›", "‹masked›", "‹masked›", "‹masked›",
     "‹masked›", "pro", "active", "49.00"]
  ]
}

The full response contained six rows and reported truncated: false. Its text representation also contained the masking marker instead of the protected values. This matters because an MCP client can consume structured content or text; the readable table alone is not the whole response contract.

Treat ‹masked› as a withheld value. It is not SQL NULL, an empty string, a stable customer pseudonym, or a value the model should infer. A masked date becomes a string marker, so downstream code must also handle the change in representation.

The source table is unchanged. This is output masking at the gateway; PostgreSQL retains the original values for authorized application workflows.

5. Ask a useful question without selecting PII

Now ask the assistant:

Using postgres-demo, calculate the number of active customers and total monthly revenue for each plan. Aggregate in SQL and return only the plan, count, and revenue. Do not select customer contact details.

The query can be:

SELECT plan,
       COUNT(*) AS active_customers,
       SUM(monthly_revenue) AS monthly_revenue
FROM public.customers
WHERE status = 'active'
GROUP BY plan
ORDER BY monthly_revenue DESC;

The result is:

plan	active_customers	monthly_revenue
business	2	398.00
pro	2	98.00
free	1	0.00

The AI can conclude that the fixture has five active customers, 496.00 in total monthly revenue, and approximately 80.2% of that revenue from the business plan. It does not need any customer's email or address to calculate those figures.

GateRook reports Masked: None for this result because the query did not return a matching sensitive value. The masking policy is still enabled. The aggregate executes inside PostgreSQL before the gateway processes the returned rows.

This is the workflow I want in everyday use: minimize the data in the query, with gateway masking as an additional control when a tool returns more detail than expected. A prompt helps express the analytical task; the configured controls enforce the boundary independently of whether the assistant follows that prompt perfectly.

6. Inspect the actual AI call in Activity

Open Activity → Agent calls, then enter this filter and press Enter:

endpoint:showcase connection:postgres-demo tool:sql_query

Open the customer-row query from your AI app. In this run, the client is Codex. The event identifies the endpoint, connection, protocol, SQL arguments, policy decision and timing.

Scroll to Result. The same event records 30 masked fields, together with a masked result preview, size and SHA-256 metadata.

The useful evidence is that the call originated from the connected AI client and that masking happened in the gateway's response processing. Playground provides a convenient result viewer; Activity ties the actual client request to its execution.

Do not use Activity as a guaranteed full archive of every result row. It records execution metadata and may show a masked preview. Masking changes also do not retroactively sanitize data already returned to an AI app or stored before the rules were enabled.

How the masking works technically

GateRook's implementation is available in the public repository, so the behavior can be inspected rather than inferred from the interface.

The MaskPolicy implementation defines the environment defaults, enabled PII categories, entropy setting and explicit field rules.

The Masker implementation combines several mechanisms: known credential values and their recognized encodings, sensitive key names, configured fields, secret patterns, optional entropy detection, and enabled PII patterns. The PII settings sit alongside credential protection; a database password and a customer's birth date are different things to protect.

For SQL results, the connector preserves result-column metadata where the driver provides it. The masker can match the column label and its original schema/table/column identity. That lets a normal direct-column alias retain protection when its source metadata is available. See ResultReader.

The PII detectors are pattern-based. Card recognition includes a Luhn check; IBAN recognition includes a mod-97 check. Phone recognition expects a leading + and a supported digit count. Those details help explain both why ordinary values remain readable and why your own real-world formats need testing.

There is an important limit: SQL expressions do not necessarily preserve source-column metadata. Transformations can also change a value's recognizable shape. Field masking and pattern recognition are therefore not a complete information-flow or query-inference defense.

When the requirement is stricter than output masking

The demo deliberately grants access to raw fictional customer columns so we can see the masking step. If your real requirement is “the AI database identity must never have access to those columns”, enforce that in PostgreSQL as well.

One approach is a separate analytics role that can select only a reviewed aggregate view. The following is an illustrative design for a larger dataset, run by an appropriate database owner:

CREATE SCHEMA analytics;

CREATE VIEW analytics.active_customers_by_plan AS
SELECT plan,
       COUNT(*) AS active_customers,
       SUM(monthly_revenue) AS monthly_revenue
FROM public.customers
WHERE status = 'active'
GROUP BY plan
HAVING COUNT(*) >= 10;

CREATE ROLE ai_analytics LOGIN;
GRANT CONNECT ON DATABASE gaterook_demo TO ai_analytics;
GRANT USAGE ON SCHEMA analytics TO ai_analytics;
GRANT SELECT ON analytics.active_customers_by_plan TO ai_analytics;

Provision authentication for that role separately, and configure a separate GateRook connection with those credentials. Give the role no raw-table grants, ownership or membership in a broader role. Check PUBLIC privileges too. With the ordinary PostgreSQL view privilege model, the view owner supplies the underlying-table privileges; inspect CREATE VIEW before adapting this to views with different security options or row-level policies.

The threshold of ten is just an example of a dataset-specific release rule, not a guarantee of anonymity. This view intentionally returns no rows for our tiny six-customer fixture. In a real system, rare combinations, repeated queries and differences between aggregates can still reveal information, so design the reporting surface for the disclosure risk you actually have.

Likewise, the customer_id retained in our row-level demo can link records together. Hiding direct identifiers does not make every remaining row anonymous. Exclude identifiers when the task does not need them, and review any remaining quasi-identifiers such as precise timestamps, location and unusual account attributes.

GateRook can still add endpoint scope, limits, masking and Activity history around the narrower database surface. The database and the gateway contribute different controls.

A practical verification checklist

Before connecting a real dataset, check the complete path with synthetic records:

1. Confirm the environment. DEV, STAGE and PROD can have different effective masking rules.
2. Check every protected field. Include names, dates, addresses, free-text notes and application-specific identifiers, not only email addresses.
3. Inspect actual tool output. Verify structured and text responses, rather than trusting an AI-generated summary of what it received.
4. Exercise representative query shapes. Direct columns, aliases, joins, views and expressions can expose different metadata. Validate them using fictional values and the exact database driver you deploy.
5. Check analytical usefulness. Counts, status distributions and revenue totals should remain usable when the task is allowed to disclose them.
6. Review access outside GateRook. Keep raw database credentials and alternate unrestricted tools out of the AI workflow.
7. Review Activity. Confirm the client, endpoint, connection, decision and masking count for a real client call.

Output masking reduces exposure; it does not establish regulatory compliance or guarantee that every possible PII format will be detected. Allowed, unmasked results still reach your chosen AI client and may reach its provider. GateRook's security model describes those boundaries.

Try it on a small workflow

Start with one question that needs business facts but no customer identity: revenue by plan, failed-payment counts, an order-status distribution, or a schema investigation. Use a restricted database role, choose the tools that task needs, configure the masking policy, and verify the actual response before expanding access.

In this example, the assistant received enough information to calculate active-customer counts and revenue while the configured customer PII fields were replaced before delivery. The setup is visible, the SQL is reproducible, and the AI call can be inspected afterward.

• Website and downloads: gaterook.com
• Application source: github.com/gaterook/gaterook
• Setup guide: Getting started with GateRook
• Security boundaries: GateRook security model