Exposing a database to agents through MCP

Who this is for and what you will be able to do

This guide is for developers who already know the basics of MCP and want to give an agent access to a real database. After reading it you should be able to follow a multi-store retail schema, see how Row Level Security keeps each store's data separate, and set up a product table with semantic search.

Focus: the database

The material comes from an MCP hands-on lab built around a sample retail system called Zava Retail. The lab's MCP server connects to a PostgreSQL database, so this guide covers the database itself: tables, permissions, the search function, auditing and backups. The MCP server, which provides database query tools built with FastMCP, is covered in the next lab^[1]. Designing the database first matters because an agent that calls it through MCP should not be able to read other stores' data, and search should stay fast as the data grows.

Prerequisites: PostgreSQL and pgvector

The database is PostgreSQL with the pgvector extension, which provides semantic search so that customers can find products with natural-language queries^[1][2]. The lab enables three extensions, uuid-ossp, pgcrypto and vector, and checks that vector is installed:

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "vector";

-- Verify vector extension installation
SELECT * FROM pg_extension WHERE extname = 'vector';

Step 1: treat each store as a tenant

The schema uses a shared database, shared schema multi-tenancy model together with Row Level Security^[1]. The tenant master table is retail.stores, and the sample data has four stores: seattle, redmond, bellevue and online^[1]. The customers, products, sales_transactions and product_embeddings tables each carry a store_id column that records which store owns a row^[1].

The order item table, sales_transaction_items, has no store_id column. Its policy instead checks that the transaction_id belongs to a sale in the current store, so ownership is determined through a join^[1].

SKUs are unique within a store, enforced by unique_sku_per_store UNIQUE (store_id, sku), so two stores can use the same SKU^[1].

Step 2: restrict access with row-level policies

After enabling Row Level Security, each table gets a policy. The customers policy limits the mcp_user role to the current store^[1]:

CREATE POLICY customers_store_isolation ON retail.customers
    FOR ALL
    TO mcp_user
    USING (store_id = current_setting('app.current_store_id', true));

The policy reads a session variable, app.current_store_id, which is set by a database function rather than declared by the agent. Even if a prompt asks an agent to look at another store, the database layer blocks the read^[1]. The point to remember is that the security boundary sits in the database, not in the model's instructions.

Step 3: product vectors and semantic search

The product_embeddings table stores the text that was embedded, a vector(1536) column and the model name. A schema comment says that 1536 dimensions correspond to OpenAI's text-embedding-3-small model^[1].

A product can have only one embedding per model, which a unique constraint on (product_id, embedding_model) enforces^[1]. The vector index is an HNSW index built with vector_cosine_ops, which provides fast approximate search^[1].

The function retail.search_products_by_similarity takes a 1536-dimension search embedding, a similarity_threshold that defaults to 0.7 and a max_results that defaults to 20^[1]. It computes similarity_score as 1 - (pe.embedding <=> search_embedding), keeps rows at or above the threshold, returns only active products in the current store, and sorts by distance^[1].

The function is declared SECURITY DEFINER, and EXECUTE is granted to mcp_user^[1]. A function with this setting runs with its owner's privileges, so the store filter inside its body must be correct. The lab includes that filter.

Step 4: create the mcp_user role

An agent should connect with a dedicated role rather than a superuser. The lab creates the role and grants schema usage^[1]:

-- Create MCP application role
CREATE ROLE mcp_user LOGIN;

-- Grant schema usage
GRANT USAGE ON SCHEMA retail TO mcp_user;

The lab also grants SELECT, INSERT, UPDATE and DELETE on all tables in the retail schema, and uses ALTER DEFAULT PRIVILEGES so that tables created later receive the same grants^[1]. These are table-level privileges. Which rows the role can see is still decided by the row-level policies.

The store context is set by retail.set_store_context. It checks that the store exists and is active, raises an exception for an invalid or inactive store, sets app.current_store_id with set_config, and writes an audit record^[1].

Step 5: audit logging

The retail.audit_log table records the table name, action, user name, store ID, record ID, and old and new values as JSON^[1]. Triggers on customers, products and sales_transactions write a record after each insert, update or delete^[1]. This helps with compliance and shows exactly what an agent changed.

Step 6: generate test data

The sample data script uses asyncpg to connect and Faker to generate fake records. It creates 2000 customers, 500 products and 5000 sales transactions^[1]. Loyalty tiers are bronze, silver, gold and platinum, weighted 50, 30, 15 and 5^[1]. The generator adds an 8% tax to subtotals and gives about 20% of line items a random discount^[1].

Step 7: maintenance and backups

The maintenance function retail.perform_maintenance runs ANALYZE on the main tables, VACUUM with ANALYZE on customers and products, and REINDEX CONCURRENTLY on the full-text and vector indexes^[1]. Cron is the suggested way to schedule it, for example 0 2 * * 0 psql -d retail_db -c "SELECT retail.perform_maintenance();"^[1]. In that entry the final 0 is the day of week, Sunday, and 2 is the hour, so the job runs at 02:00 every Sunday.

The backup script uses the custom format of pg_dump, compresses the output, checks it with pg_restore --list, and deletes backups older than 30 days (RETENTION_DAYS=30)^[1]. The core command is:

pg_dump \
    --host="$DB_HOST" \
    --port="$DB_PORT" \
    --username="$DB_USER" \
    --dbname="$DB_NAME" \
    --verbose \
    --clean \
    --create \
    --if-exists \
    --format=custom \
    --file="$BACKUP_FILE"

The restore script creates a target database with createdb, restores it with pg_restore --clean --if-exists, and counts the tables in the retail schema to confirm the restore^[1].

Common questions and limits

• Row-level security depends on the session variable. If the application does not call set_store_context, current_setting will not return a valid store and queries will come back empty, so connection pooling must carry the store context with it.
• A SECURITY DEFINER function must keep its own store filter. Do not remove that filter because the policies seem to cover it.
• The vector dimension must match the embedding model. The column is vector(1536), so switching models means changing the dimension and regenerating the vectors.
• The numbers in the sample (2000 customers, an 8% tax rate, a 30-day retention period) are demonstration settings. Production values should follow your business and compliance rules.

Next step

With the database in place, the next step is a FastMCP server that exposes product queries, store-scoped lookups and semantic search as MCP tools. The next lab covers that work, along with connection pooling and error handling^[1]. For pgvector itself, see its project page^[2].

Adapted from the database lab of Microsoft's open-source course *MCP for Beginners*^[1], released under the MIT License.