Rails PG Extras MCP

Rails PG Extras MCP

MCP (Model Context Protocol) interface for rails-pg-extras gem. Easily explore PostgreSQL metadata and debug performance issues. Check for table bloat, slow queries, unused indexes, and more. Run EXPLAIN ANALYZE on bottlenecks and get clear, LLM-powered insights to optimize your database.

Use a minimally privileged, read-only user to eliminate the risk of data modification or exposure.

Check out this post for more in-depth info on the project.

Installation

bundle add rails-pg-extras-mcp

The library supports MCP protocol via HTTP SSE interface.

config/routes.rb

mount RailsPgExtrasMcp::App.build, at: "pg_extras_mcp"

with optional authorization:

opts = { auth_token: "secret" }
mount RailsPgExtrasMcp::App.build(opts), at: "pg_extras_mcp"

Refer to the fast-mcp docs for a complete list of supported options (the opts hash is passed directly as-is). For production deployments, you'll likely need a similar config:

opts = { allowed_origins: [ /.*./ ], allowed_ips: [ "*" ], auth_token: "secret", localhost_only: false }
mount RailsPgExtrasMcp::App.build(opts) at: "pg_extras_mcp"

Next, install mcp-remote:

npm install -g mcp-remote

and in your LLM of choice:

{
  "mcpServers": {
    "pg-extras": {
      "command": "npx",
      "args": [
        "mcp-remote",
        "http://localhost:3000/pg_extras_mcp/sse",
        "--header",
        "Authorization: secret"
      ]
    }
  }
}

You can now ask LLM questions about the metadata and performance metrics of your database.

Optional EXPLAIN ANALYZE support

calls and outliers methods return a list of bottleneck queries. LLM can get better insights into these queries by performing EXPLAIN and EXPLAIN ANALYZE analysis. MCP server exposes two optional methods for this purpose: explain and explain_analyze.

You can enable them by setting the following ENV variables:

ENV['PG_EXTRAS_MCP_EXPLAIN_ENABLED'] = 'true' ENV['PG_EXTRAS_MCP_EXPLAIN_ANALYZE_ENABLED'] = 'true'

Enabling these features means that an LLM, can run arbitrary queries in your database. The execution context is wrapped in a transaction and rolled back, so, in theory, any data modification should not be possible. But it's advised to configure a read-only permission if you want to use these features. By specifying ENV['RAILS_PG_EXTRAS_MCP_DATABASE_URL'] you can overwrite the default Rails ActiveRecord database connection to restrict an access scope:

Least responsibility

If you're not planing to use EXPLAIN ANALYZE features, you should configure a user with read access only to the metadata tables:

CREATE ROLE extras_viewer NOLOGIN;
CREATE USER extras_user WITH PASSWORD 'your_password';
GRANT extras_viewer TO extras_user;

GRANT CONNECT ON DATABASE your_db_name TO extras_user;
GRANT USAGE ON SCHEMA public TO extras_user;

GRANT SELECT ON pg_stat_statements TO extras_user;
GRANT SELECT ON pg_stat_activity, pg_locks TO extras_user;
GRANT SELECT ON pg_stat_user_indexes, pg_index TO extras_user;
GRANT SELECT ON pg_stat_all_tables, pg_stat_database, pg_settings, pg_namespace TO extras_user;

GRANT EXECUTE ON FUNCTION pg_relation_size(regclass) TO extras_user;
GRANT EXECUTE ON FUNCTION pg_indexes_size(regclass) TO extras_user;
GRANT EXECUTE ON FUNCTION pg_table_size(regclass) TO extras_user;
GRANT EXECUTE ON FUNCTION pg_total_relation_size(regclass) TO extras_user;

You can ask an LLM to check which db user it's connected with using connections tool.

Status

The project is in an early beta, so proceed with caution.