MCP on ERP data: column privileges keep personal data out

01 — The setup

An ERP system exports some 200 tables every night into a PostgreSQL database of their own. The import creates a new schema, fills it and swaps it for the previous day's in one transaction. A language model is meant to answer questions about these figures, through MCP. Who calls the model, a dashboard on Amazon Bedrock or a desktop client, does not matter for this note.

The MCP server is Postgres MCP Pro, a finished open-source product. The export has a little over 4,600 columns. Around 230 of them hold personal data, and none of those fields should reach the model.

02 — Column privileges instead of a prompt rule

An instruction in the prompt such as “do not output names” does not reliably prevent anything. A suitably worded question gets around it, and by then the model has already received the data anyway. The restriction therefore has to sit in the database.

PostgreSQL grants SELECT on individual columns too. A query that reads a column it was not granted fails with permission denied. That includes SELECT *, because the star expands to every column.

03 — Creating the roles

The setup relies on the fact that a PostgreSQL view checks privileges on the underlying tables as its owner, not as the role querying it. It needs three roles.

• Import: owns schema erp and reloads it every night.
• mcp_owner: owns schema mcp with the views and may read only released columns in erp.
• mcp_reader: the MCP server's login. It reads mcp and nothing else.

The roles are created once; the import sets their passwords from the secret store on every run:

CREATE ROLE mcp_owner  LOGIN;
CREATE ROLE mcp_reader LOGIN;
SELECT format('ALTER ROLE mcp_owner  PASSWORD %L', :'owner_pw')  \gexec
SELECT format('ALTER ROLE mcp_reader PASSWORD %L', :'reader_pw') \gexec

CREATE SCHEMA IF NOT EXISTS mcp AUTHORIZATION mcp_owner;
GRANT USAGE ON SCHEMA erp TO mcp_owner;     -- columns follow in section 04
GRANT USAGE ON SCHEMA mcp TO mcp_reader;    -- no USAGE on erp

ALTER ROLE mcp_reader SET default_transaction_read_only = on;
ALTER ROLE mcp_reader SET statement_timeout = '30s';
ALTER ROLE mcp_reader SET log_statement = 'all';

mcp_reader has no USAGE on erp and so cannot reach any table there. It reads only views, and the views read with the privileges of mcp_owner. If someone creates a view with security_invoker, PostgreSQL checks the privileges of mcp_reader instead, and the query fails.

04 — Column privileges from the manifest

The column privileges are rebuilt every night, in the same transaction as the import. Because schema erp is created afresh, there are no old privileges to revoke. The basis is the export's manifest, a JSON document with every table and all its columns. Two lists are added: the reviewed tables and, per table, the columns that hold personal data.

CREATE OR REPLACE FUNCTION erp_admin.grant_mcp_owner(
  manifest jsonb, reviewed jsonb, pii jsonb) RETURNS integer
LANGUAGE plpgsql AS $$
DECLARE tbl jsonb; key text; cols text[]; n int := 0;
BEGIN
  FOR tbl IN SELECT * FROM jsonb_array_elements(manifest->'tables') LOOP
    key := tbl->>'name';
    CONTINUE WHEN NOT (coalesce(reviewed, '[]') ? key);
    SELECT array_agg(quote_ident(c->>'name')) INTO cols
      FROM jsonb_array_elements(tbl->'columns') c
     WHERE NOT (coalesce(pii->key, '[]') ? (c->>'name'));
    CONTINUE WHEN cols IS NULL;
    EXECUTE format('GRANT SELECT (%s) ON erp.%I TO mcp_owner',
                   array_to_string(cols, ', '), key);
    n := n + array_length(cols, 1);
  END LOOP;
  RETURN n;
END $$;

It is called at the end of the import, after the schema swap and before the COMMIT:

SELECT erp_admin.grant_mcp_owner(:'manifest'::jsonb,
                                 :'reviewed'::jsonb,
                                 :'pii'::jsonb) AS columns_granted;

quote_ident and %I make sure that column names with capitals or special characters end up correctly in the GRANT. A table that appears in the export after an ERP update is on neither list and therefore gets no privilege. It is released only once someone has reviewed it and added it.

05 — The two lists

The lists live in the deployment, versioned and changed through merge requests. An excerpt:

mcp:
  reviewedTables:
    - buchungen
    - konten
    - adressen
    - belege
  piiColumns:
    adressen: [name, vorname, strasse, telefon, email, geburtstag, notiz]
    buchungen: [buchungstext, bemerkung, erfasst_von]
    konten: [kontobezeichnung]
    belege: [kunde_name, lieferanschrift, bearbeiter]

Before a list goes into the deployment, a small script checks every name against the manifest. Otherwise a typo in a column name would silently release a personal-data column, because it is not on the list.

The list includes name, address, phone, email, IBAN, birthday, contact person, and user and clerk names. Free-text fields are harder. A booking text often reads “invoice Müller 4711”. A column privilege always covers the whole column, so the only option is to lock it completely. Account names are the same: a customer account carries the customer's name, a general ledger account carries “revenue 19 %”.

As a result, the model lacks information that would be useful for some analyses. That is worth agreeing with the users beforehand. In the actual case the list ended up at 227 columns in 33 tables, including till PINs and password fields.

06 — Writing and applying the views

The model does not query the raw data but views in schema mcp. The views should aggregate and leave out small groups:

CREATE VIEW mcp.umsatz_monat AS
SELECT date_trunc('month', belegdatum)::date AS monat,
       konto_haben                            AS erloeskonto,
       sum(betrag)                            AS umsatz,
       count(*)                               AS buchungen
  FROM erp.buchungen
 WHERE konto_haben BETWEEN '8000' AND '8999'
 GROUP BY 1, 2
HAVING count(*) >= 5;

COMMENT ON VIEW mcp.umsatz_monat IS
  'Revenue per month and revenue account, net, from 5 bookings';

Postgres MCP Pro does not pass the comment to the model automatically. The model can query it with obj_description('mcp.umsatz_monat'::regclass), though. A note about this in the system prompt helps it find the right view.

All views sit in one file, views.sql. A job applies it as mcp_owner, in a single transaction:

psql -v ON_ERROR_STOP=1 --single-transaction <<'SQL'
SELECT format('DROP VIEW IF EXISTS mcp.%I CASCADE', viewname)
  FROM pg_views WHERE schemaname = 'mcp' \gexec
\i /views/views.sql
DO $$ BEGIN
  IF EXISTS (SELECT 1 FROM pg_class WHERE relnamespace = 'mcp'::regnamespace
                                      AND relkind <> 'v') THEN
    RAISE EXCEPTION 'schema mcp may hold views only';
  END IF;
  IF EXISTS (SELECT 1 FROM pg_proc WHERE pronamespace = 'mcp'::regnamespace) THEN
    RAISE EXCEPTION 'schema mcp may hold no functions';
  END IF;
END $$;
SELECT format('SELECT * FROM mcp.%I LIMIT 0', viewname)
  FROM pg_views WHERE schemaname = 'mcp' \gexec
GRANT SELECT ON ALL TABLES IN SCHEMA mcp TO mcp_reader;
SQL

The step that matters is LIMIT 0. CREATE VIEW checks no privileges on the tables beneath; only the query does. If a view reads a locked column, it ends with permission denied. The transaction rolls back and the previous views stay active.

The job runs twice: as an init container before the MCP server starts, and as a CronJob after the nightly import. The import recreates erp, and CASCADE takes the views with it.

07 — Running the MCP server

Postgres MCP Pro runs as a container next to the database. Three things matter: restricted mode, a connection as mcp_reader, and TLS with certificate verification.

containers:
  - name: postgres-mcp
    image: crystaldba/postgres-mcp   # pinned by digest in practice
    args: ["--access-mode=restricted", "--transport=sse",
           "--sse-host=0.0.0.0", "--sse-port=8000"]
    env:
      - name: MCP_READER_PASSWORD
        valueFrom: { secretKeyRef: { name: mcp-db, key: reader_password } }
      - name: DATABASE_URI
        value: "postgresql://mcp_reader:$(MCP_READER_PASSWORD)@db:5432/workspace?sslmode=verify-full&sslrootcert=/run/tls/ca.crt"
    securityContext:
      runAsNonRoot: true
      readOnlyRootFilesystem: true
      allowPrivilegeEscalation: false

restricted runs every query in a read-only transaction and stops it after 30 seconds. The role mcp_reader has the same settings, so the restrictions still apply if one of the two layers is misconfigured.

The server has no public endpoint. A NetworkPolicy admits only the pods meant to use it:

kind: NetworkPolicy
spec:
  podSelector: { matchLabels: { app.kubernetes.io/name: postgres-mcp } }
  policyTypes: [Ingress]
  ingress:
    - from: [{ podSelector: { matchLabels: { app.kubernetes.io/name: dashboard } } }]
      ports: [{ port: 8000 }]

Note the protection against DNS rebinding: the server answers only requests with a Host header on localhost, all others get a 421. Clients inside the cluster therefore send Host: localhost:8000.

With log_statement = 'all', every query by mcp_reader lands in the database log, which is collected centrally in Loki. That makes it possible to trace at any time which queries the model sent.

08 — Using the server

The server offers its tools over MCP. Four matter for this setup: list_schemas, list_objects, get_object_details and execute_sql. The other tools, index recommendations for instance, need privileges mcp_reader does not have and are not relevant here.

To try it out, a port-forward and Claude Code are enough. With the forward, the Host header is right as well:

kubectl -n app port-forward svc/postgres-mcp 8000:8000 &
claude mcp add --transport sse erp localhost/sse

You can then ask the assistant questions like “How has revenue developed since January?”. It lists the views in mcp, reads their columns and sends a query:

SELECT monat, sum(umsatz) AS umsatz
  FROM mcp.umsatz_monat
 WHERE monat >= date '2026-01-01'
 GROUP BY monat ORDER BY monat;

From your own code, the MCP SDK for Python does it:

from mcp import ClientSession
from mcp.client.sse import sse_client

URL = "http://postgres-mcp.app.svc:8000/sse"

async def ask(sql: str):
    async with sse_client(URL, headers={"Host": "localhost:8000"}) as (rd, wr):
        async with ClientSession(rd, wr) as s:
            await s.initialize()
            return await s.call_tool("execute_sql", {"sql": sql})

In an application on Bedrock this becomes a loop: list_tools supplies the tool descriptions for the Converse API's toolConfig, and every toolUse from the model goes to the server as a call_tool. The model receives only the columns the view returns.

09 — Checking that the line holds

Three queries check whether the privileges are right. They should run again after every change to the lists:

-- 1. The reader cannot reach raw data.
SET ROLE mcp_reader;
SELECT count(*) FROM erp.adressen;
-- ERROR:  permission denied for schema erp
RESET ROLE;

-- 2. The owner reads released columns, but not locked ones.
SET ROLE mcp_owner;
SELECT ort FROM erp.adressen LIMIT 1;    -- works
SELECT name FROM erp.adressen LIMIT 1;
-- ERROR:  permission denied for table adressen
RESET ROLE;

-- 3. No column on the list holds a privilege.
SELECT table_name, column_name
  FROM information_schema.column_privileges
 WHERE grantee = 'mcp_owner' AND table_schema = 'erp'
   AND column_name IN ('name', 'vorname', 'strasse', 'telefon', 'email');
-- (0 rows)

The third query can be generated from the list and run after the import as a check. If it returns a row, the job should fail.

The database log shows which queries the model actually sent, for example in Loki:

{namespace="db"} |= "mcp_reader" |= "statement:"

10 — What column privileges do not do

Column privileges stop the model from reading certain fields. They do not stop it from drawing conclusions from permitted data. Querying revenue per customer number can point to a person when the customer is a sole trader. Only aggregation in the views and a HAVING for small groups help against that. It has to be checked in the review of views.sql.

Access from public assistants such as claude.ai is not implemented yet. It needs a login in front of the server, OAuth 2.1 with Dynamic Client Registration. Until then the server is reachable only inside the cluster. Whatever the path: customer data goes to a model only if a contract covers exactly this use case.

Conclusion

Personal data can be kept away from an MCP server reliably if the database enforces the privileges, not the prompt. Three roles are enough, plus a function that grants column privileges from the manifest, two lists and a job that checks the views. New tables stay locked until they are reviewed, and every query is in the log. The drawback: free-text fields drop out entirely. That should be discussed with the users beforehand.