Skip to content

Row-Level and Column-Level Security

The pgEdge Postgres MCP Server executes SQL queries as the configured database user. PostgreSQL's built-in security mechanisms govern what data the server returns through natural language queries. These mechanisms include row-level security (RLS), column-level grants, and security views.

This guide explains how to combine PostgreSQL authorization features with the MCP server's per-user database connections. The result is fine-grained access control that applies transparently to all queries, including those generated by an LLM.

Overview

The MCP server connects to PostgreSQL using the credentials specified in the server configuration file. Each database entry in the configuration defines a PostgreSQL user, and that user's privileges determine the query results. When you combine per-user database connections with PostgreSQL RLS policies, each MCP user sees only the rows that the corresponding PostgreSQL role can access.

This approach provides the following capabilities:

  • Row-level security restricts which rows a user can read or modify.
  • Column-level grants control which columns a role can access on a table.
  • Security views expose a safe subset of columns and rows without granting direct table access.
  • Per-user MCP connections map authenticated MCP users to distinct PostgreSQL roles.
  • Session variables enable tenant isolation through a single shared database connection.

Row-Level Security with Per-User Connections

Row-level security (RLS) allows PostgreSQL to filter table rows based on the executing role. The MCP server supports this pattern through per-user database connections.

Creating PostgreSQL Roles

Create a separate PostgreSQL role for each MCP user who requires isolated data access.

In the following example, the CREATE ROLE statements create two login roles with passwords.

CREATE ROLE alice LOGIN PASSWORD 'secure_password_alice';
CREATE ROLE bob LOGIN PASSWORD 'secure_password_bob';

Grant each role the permissions required for querying the application tables.

In the following example, the GRANT statements give both roles read access to the public schema.

GRANT CONNECT ON DATABASE myapp TO alice;
GRANT CONNECT ON DATABASE myapp TO bob;
GRANT USAGE ON SCHEMA public TO alice;
GRANT USAGE ON SCHEMA public TO bob;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO alice;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO bob;

Configuring Per-User Database Connections

Define a separate database entry for each user in the MCP server configuration file. Each entry connects as a different PostgreSQL role and restricts access to the corresponding MCP user.

In the following example, the configuration file defines two database entries that connect as alice and bob.

databases:
    # Connection for alice
    - name: "alice-db"
      host: "localhost"
      port: 5432
      database: "myapp"
      user: "alice"
      password: ""
      available_to_users:
          - "alice"

    # Connection for bob
    - name: "bob-db"
      host: "localhost"
      port: 5432
      database: "myapp"
      user: "bob"
      password: ""
      available_to_users:
          - "bob"

The available_to_users field restricts each database entry to the named MCP user. Set the password field to an empty string to use a .pgpass file, or provide the password directly. You can also use environment variables such as PGPASSWORD or PGEDGE_DB_PASSWORD to supply credentials securely.

Creating RLS Policies

Enable RLS on the target table and create a policy that filters rows based on the current PostgreSQL role.

In the following example, the ALTER TABLE statement enables RLS, and the CREATE POLICY statement restricts SELECT access to rows owned by the current user.

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY user_isolation ON orders
    FOR SELECT
    USING (owner = current_user);

How RLS Works with the MCP Server

When Alice queries the MCP server, the server connects to PostgreSQL as the alice role. The RLS policy evaluates current_user as alice and returns only rows where the owner column matches. Bob's queries connect as the bob role and return only rows belonging to Bob.

The LLM does not need to include WHERE clauses for access control. PostgreSQL enforces RLS policies automatically on every query, regardless of how the query was generated.

Using Session Variables for RLS

Session variables provide an alternative approach that uses a single shared database connection. This pattern suits multi-tenant applications where creating separate PostgreSQL roles for each tenant is impractical.

Configuring a Shared Connection

Define a single database connection in the MCP server configuration file.

In the following example, the configuration uses one shared connection for all users.

databases:
    - name: "production"
      host: "prod-db.example.com"
      port: 5432
      database: "myapp"
      user: "app_user"
      password: ""

Creating RLS Policies with Session Variables

Create RLS policies that reference a session variable instead of current_user. The current_setting function reads the variable at query time.

In the following example, the RLS policy filters rows based on the app.tenant_id session variable.

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON orders
    FOR SELECT
    USING (
        tenant_id = current_setting(
            'app.tenant_id'
        )::integer
    );

Setting the Session Variable

This pattern requires the server to call SET on the database connection before executing queries. The server must set the session variable based on the authenticated MCP user identity.

In the following example, the SET command establishes the tenant context for a database session.

SET app.tenant_id = '42';

This approach is the recommended pattern for multi-tenant applications. The server sets the session variable once per connection, and all subsequent queries respect the tenant boundary. You should implement server-side logic to map each authenticated MCP user to the correct tenant identifier.

Combining Session Variables with Per-Token Isolation

The MCP server maintains per-token connection isolation in HTTP mode with authentication enabled. Each API token receives a dedicated connection pool. This isolation ensures that session variables set for one token do not affect queries from another token.

Column-Level Security

Column-level security restricts which columns a role can read from a table. PostgreSQL supports this through GRANT and REVOKE statements and through security views.

Using Column-Level Grants

Revoke all access from the target role and then grant SELECT on specific columns only.

In the following example, the REVOKE and GRANT statements restrict the analyst_role to three columns.

REVOKE SELECT ON users FROM public;

GRANT SELECT (id, name, email) ON users
    TO analyst_role;

The analyst_role cannot query columns such as salary or ssn. Any attempt to select a restricted column returns a permission error.

Using Security Views

Security views provide another approach to column-level restrictions. A view exposes only the desired columns and serves as the access point for restricted roles.

In the following example, the CREATE VIEW statement defines a view that excludes sensitive columns.

CREATE VIEW users_public AS
    SELECT id, name, email, created_at
    FROM users;

GRANT SELECT ON users_public TO readonly_role;

The readonly_role queries the users_public view and cannot access columns omitted from the view definition.

Mapping Column Grants to MCP Connections

Configure per-user database connections that connect as roles with different column-level grants. Each MCP user connects as a PostgreSQL role that has access to only the appropriate columns.

In the following example, the configuration maps two MCP users to roles with different column visibility.

databases:
    # Analyst sees id, name, email only
    - name: "analyst-db"
      host: "localhost"
      port: 5432
      database: "myapp"
      user: "analyst_role"
      password: ""
      available_to_users:
          - "analyst"

    # Manager sees all columns
    - name: "manager-db"
      host: "localhost"
      port: 5432
      database: "myapp"
      user: "manager_role"
      password: ""
      available_to_users:
          - "manager"

Multi-Tenant Example

This section provides a complete worked example for a multi-tenant application. The example demonstrates RLS enforcement through the MCP server using per-tenant database connections.

Creating the Schema

Create an orders table with a tenant_id column that identifies the owning tenant.

In the following example, the CREATE TABLE statement defines the orders table with a tenant identifier.

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    tenant_id INTEGER NOT NULL,
    product VARCHAR(100) NOT NULL,
    quantity INTEGER NOT NULL,
    total DECIMAL(10, 2) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
);

Creating Tenant Roles

Create a PostgreSQL role for each tenant. Each role connects through a dedicated MCP database entry.

In the following example, the statements create two tenant roles and grant table access.

CREATE ROLE tenant_a LOGIN PASSWORD 'password_a';
CREATE ROLE tenant_b LOGIN PASSWORD 'password_b';

GRANT CONNECT ON DATABASE myapp TO tenant_a;
GRANT CONNECT ON DATABASE myapp TO tenant_b;
GRANT USAGE ON SCHEMA public TO tenant_a;
GRANT USAGE ON SCHEMA public TO tenant_b;
GRANT SELECT ON orders TO tenant_a;
GRANT SELECT ON orders TO tenant_b;

Enabling RLS and Creating Policies

Enable RLS on the orders table and create a policy that maps each role to its tenant rows.

In the following example, the policy uses a CASE expression to associate each role with a tenant identifier.

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_access ON orders
    FOR SELECT
    USING (
        tenant_id = CASE current_user
            WHEN 'tenant_a' THEN 1
            WHEN 'tenant_b' THEN 2
        END
    );

Inserting Sample Data

Insert sample data as a superuser so that RLS policies do not filter the inserts.

In the following example, the INSERT statement adds orders for both tenants.

INSERT INTO orders
    (tenant_id, product, quantity, total)
VALUES
    (1, 'Widget A', 10, 99.90),
    (1, 'Widget B', 5, 49.95),
    (2, 'Gadget X', 3, 149.97),
    (2, 'Gadget Y', 7, 279.93);

Configuring the MCP Server

Define per-tenant database connections in the MCP server configuration file.

In the following example, the configuration maps each tenant to a dedicated database entry.

databases:
    # Tenant A connection
    - name: "tenant-a-db"
      host: "localhost"
      port: 5432
      database: "myapp"
      user: "tenant_a"
      password: ""
      available_to_users:
          - "tenant_a_user"

    # Tenant B connection
    - name: "tenant-b-db"
      host: "localhost"
      port: 5432
      database: "myapp"
      user: "tenant_b"
      password: ""
      available_to_users:
          - "tenant_b_user"

Verifying Tenant Isolation

When tenant_a_user queries the MCP server with "show me all orders," the server connects as the tenant_a role. PostgreSQL applies the RLS policy and returns only tenant A's rows.

The following results show what each tenant sees when querying the same orders table.

Tenant A sees:

id | product  | quantity | total
 1 | Widget A |       10 | 99.90
 2 | Widget B |        5 | 49.95

Tenant B sees:

id | product  | quantity | total
 3 | Gadget X |        3 | 149.97
 4 | Gadget Y |        7 | 279.93

Neither tenant can see or access the other tenant's data. The LLM generates standard SELECT queries, and PostgreSQL's RLS policies enforce the tenant boundary transparently.

Security Patterns Reference

The following table summarizes the security patterns available for use with the MCP server.

Pattern Use Case Level
Per-user connections Small user groups Row and column
Session variables Multi-tenant apps Row
Column grants Sensitive columns Column
Security views Read-only subsets Row and column
Combined approach Complex rules Row and column

The per-user connections pattern provides the simplest path to row-level and column-level security. Session variables suit applications with many tenants where creating individual PostgreSQL roles is impractical. Column grants and security views address column-level restrictions independently. The combined approach layers multiple patterns for comprehensive access control.

Best Practices

Follow these guidelines when implementing row-level and column-level security with the MCP server.

  • Test all RLS policies in a psql session before deploying the policies through the MCP server.
  • Use EXPLAIN to verify that PostgreSQL applies the expected RLS policies to each query plan.
  • Monitor PostgreSQL query logs for unexpected access patterns or policy bypass attempts.
  • Document the security model that maps MCP users to PostgreSQL roles and RLS policies.
  • Apply the principle of least privilege by granting each role only the minimum required permissions.
  • Enable FORCE ROW LEVEL SECURITY on tables where the table owner should also be subject to RLS.
  • Review RLS policies after schema changes to ensure the policies still reference valid columns.
  • Use pg_policies system view to audit all active RLS policies across the database.

In the following example, the query lists all RLS policies defined in the current database.

SELECT schemaname, tablename, policyname,
       permissive, roles, cmd, qual
FROM pg_policies
ORDER BY tablename, policyname;

See Also

Consult the following guides for related topics.