Build an EDB Postgres AI Semantic Layer for AgentCore

To connect an AI agent to PostgreSQL with EDB Postgres AI and Amazon Bedrock AgentCore, put AIDB’s Semantic Knowledge Base (SemKB) beside the database schema, expose AIDB’s MCP endpoint to AgentCore, and let PostgreSQL roles enforce data access. This walkthrough follows the C360 example in AWS’s EDB Postgres AI and Amazon Bedrock AgentCore tutorial. Its sample uses four tables and one view, so treat its query results as examples for that dataset.

What you need

• An EDB Postgres AI database with AIDB available.
• The four CSV files from the Stardog C360 Knowledge Kit, loaded into a c360 schema with the relevant foreign-key relationships.
• Access to Amazon Cognito, Amazon Bedrock AgentCore, and an HTTPS Application Load Balancer (ALB) for the illustrated connection path.
• A PostgreSQL role for each intended access level, with grants and row-level security configured for the data that role may read.

Build the semantic layer and connect the agent

1. Load the C360 data into PostgreSQL

Load the sample CSVs into four tables: customers, locations, products, and purchases. Add the foreign keys that describe how those tables relate. The tutorial also creates a derived view in the c360 schema.

The SemKB indexes schema information, including tables, views, columns, comments, and foreign-key relationships. It does not index the underlying rows. The agent uses that metadata to find relevant schema and join paths, then queries the database through the MCP endpoint.

2. Write business definitions into the schema

Use PostgreSQL COMMENT ON statements to describe what columns and fields mean, including sensitive fields. The comments are useful context for the agent, but the definition still needs to match the organization’s intended meaning.

In the C360 example, purchases.price means the total for an order, not the price of each unit. The tutorial reports that treating it as a unit price produced an erroneous top-customer result of about $502,529; with the order-total meaning documented, its demonstrated result was $27,991.66. Both figures come from the sample query, not a product benchmark.

3. Create views and reusable semantic aliases

Represent business concepts with views and named queries. The C360 big_spender view defines a big spender as a customer with at least one order over $250. A semantic alias can then expose a parameterized, read-only SELECT with a natural-language description as an agent tool.

Foreign keys help the SemKB discover join paths. A view or alias can express a reusable business definition and query, while comments explain the meaning of the schema elements the agent encounters.

4. Configure the Semantic Knowledge Base

Create the sample knowledge base as c360_kb, using the c360 schema and the bge-small-en-v1.5-f16 model. The tutorial sets auto_processing to Live. When an installation has multiple SemKBs, specify kb_name in searches so the intended knowledge base is used.

Choose processing behavior based on how schema changes should be handled:

5. Enable AIDB’s MCP endpoint

Set edb.endpoints_mcp_enabled, add aidb to shared_preload_libraries, and restart PostgreSQL. The tutorial’s endpoint example uses port 8765. A non-loopback bind requires TLS certificate and key settings.

6. Put the endpoint behind an HTTPS ALB

In the demonstrated network path, an HTTPS ALB fronts the AIDB edb-endpoints MCP server on an EC2 instance. The ALB target group connects over HTTPS on port 8765. In that setup, the target group does not validate the backend certificate chain; AWS recommends private VPC connectivity for production.

7. Register the MCP target with AgentCore Gateway

Register the AIDB endpoint as an MCP target in AgentCore Gateway, routing requests through the ALB. The tutorial uses streamable HTTP MCP, so its path does not require an AWS Lambda translation layer.

8. Configure user identity and PostgreSQL roles

Amazon Cognito issues the user’s JWT. AgentCore Gateway validates it and evaluates the Cedar policy for tool access. Separately, AgentCore Identity supplies the PostgreSQL username and password to the MCP target through HTTP Basic authentication. The agent code does not receive the PostgreSQL credential.

Set up PostgreSQL roles, grants, and row-level security to control what each database session can read. Gateway authorization determines whether the agent can use a tool; PostgreSQL enforces the database permissions for the role used by that request.

Test access, query meaning, and traces

Test the same agent tools with credentials for each intended role. In AWS’s C360 demonstration, a restricted role receives a PostgreSQL permission error when it tries to read customers.ssn. Row-level security can also filter which rows a role sees. These checks show that PostgreSQL permissions apply to the request; they do not determine whether a business definition or query result is correct.

Use sample questions with known results to check query behavior. Under the tutorial’s definition of a big spender, its C360 query returns 46 customers each in Florida and Texas, and 42 in California. Those counts depend on the sample data and the threshold of at least one order over $250.

AIDB can store OpenTelemetry SQL execution traces in the aidb_otel schema when telemetry is enabled. AgentCore separately sends traces for tool calls, model calls, and token usage to CloudWatch Transaction Search. Together, these traces help teams inspect both SQL execution and the agent’s calls.

Check definitions, schema changes, and data freshness

A semantic layer can apply a definition consistently; it cannot decide which of several valid business definitions an organization has approved. In a separate practitioner-described Snowflake demonstration using 34 vendors, the term “active” produced counts of 25 under a trailing-12-month purchase or invoice definition, 24 under a trailing-90-day definition, and 21 under an ERP status definition. The example concerns a different system and dataset. The practical step is to assign ownership to business terms and make the chosen definition explicit; ask for clarification when a question could refer to more than one measure. (Practitioner-described Snowflake example)

Practitioners discussing an agent over a database with roughly 700 tables recommended testing real questions, reviewing generated query plans, and recording the business definition used for each query. One commenter described hashing and comparing schema snapshots, then flagging definitions that reference changed tables; the same commenter reported a stale balance caused by replica lag. These are practitioner accounts from another system, not EDB features. They point to separate checks for query behavior, schema changes, and data freshness alongside permission tests. (Large-database practitioner discussion)