Postgres MCP Server

Instructions

Implementation Reference

• src/index.ts:1555-1685 (handler)

  Handler function implementing the 'security' tool logic. Dispatches to specific security checks using SQL queries for SSL status, authentication methods, session security, row-level security, and audit logs.

  private async handleSecurity(args: any) {
    const { operation, table, policy_name, policy_expression, audit_type } = args;
  
    switch (operation) {
      case 'check_ssl':
        const sslInfo = await this.queryClient.executeQuery(`
          SELECT 
            name,
            setting,
            context,
            short_desc
          FROM pg_settings 
          WHERE name LIKE '%ssl%' OR name LIKE '%tls%'
          ORDER BY name
        `);
        return {
          content: [{
            type: 'text',
            text: JSON.stringify(sslInfo.rows, null, 2)
          }]
        };
  
      case 'list_auth_methods':
        const authMethods = await this.queryClient.executeQuery(`
          SELECT 
            type,
            database,
            user_name,
            address,
            netmask,
            auth_method,
            options,
            error
          FROM pg_hba_file_rules
          ORDER BY line_number
        `);
        return {
          content: [{
            type: 'text',
            text: JSON.stringify(authMethods.rows, null, 2)
          }]
        };
  
      case 'session_security':
        const sessionInfo = await this.queryClient.executeQuery(`
          SELECT 
            inet_client_addr() as client_ip,
            inet_server_addr() as server_ip,
            current_user,
            session_user,
            current_database(),
            pg_backend_pid() as backend_pid,
            pg_is_in_recovery() as in_recovery,
            current_setting('ssl') as ssl_enabled
        `);
        return {
          content: [{
            type: 'text',
            text: JSON.stringify(sessionInfo.rows[0], null, 2)
          }]
        };
  
      case 'row_level_security':
        if (!table) {
          // List all RLS policies
          const rlsPolicies = await this.queryClient.executeQuery(`
            SELECT 
              schemaname,
              tablename,
              policyname,
              permissive,
              roles,
              cmd,
              qual,
              with_check
            FROM pg_policies
            ORDER BY schemaname, tablename, policyname
          `);
          return {
            content: [{
              type: 'text',
              text: JSON.stringify(rlsPolicies.rows, null, 2)
            }]
          };
        } else {
          // Show RLS status for specific table
          const rlsStatus = await this.queryClient.executeQuery(`
            SELECT 
              schemaname,
              tablename,
              rowsecurity,
              forcerowsecurity
            FROM pg_tables 
            WHERE tablename = $1
          `, [table]);
          return {
            content: [{
              type: 'text',
              text: JSON.stringify(rlsStatus.rows, null, 2)
            }]
          };
        }
  
      case 'audit_log':
        const auditQuery = `
          SELECT 
            datname as database,
            usename as username,
            application_name,
            client_addr,
            backend_start,
            query_start,
            state,
            query
          FROM pg_stat_activity 
          WHERE state = 'active' 
          ORDER BY query_start DESC
          LIMIT 50
        `;
        const auditInfo = await this.queryClient.executeQuery(auditQuery);
        return {
          content: [{
            type: 'text',
            text: JSON.stringify(auditInfo.rows, null, 2)
          }]
        };
  
      default:
        throw new Error(`Unknown security operation: ${operation}`);
    }
  }

• src/index.ts:480-515 (schema)

  Input schema and definition for the 'security' MCP tool, specifying supported operations like check_ssl, list_auth_methods, etc.

  {
    name: 'security',
    description: 'Database security management: SSL, authentication, encryption, auditing',
    inputSchema: {
      type: 'object',
      properties: {
        operation: {
          type: 'string',
          enum: [
            'check_ssl', 'list_auth_methods', 'check_encryption', 'audit_log',
            'password_policy', 'connection_limits', 'session_security',
            'row_level_security', 'column_encryption', 'security_labels'
          ],
          description: 'Security operation to perform'
        },
        table: {
          type: 'string',
          description: 'Table name for RLS operations'
        },
        policy_name: {
          type: 'string',
          description: 'RLS policy name'
        },
        policy_expression: {
          type: 'string',
          description: 'RLS policy expression'
        },
        audit_type: {
          type: 'string',
          enum: ['connections', 'queries', 'ddl', 'dml', 'errors'],
          description: 'Type of audit information'
        }
      },
      required: ['operation']
    }
  }

• src/index.ts:670-674 (registration)

  Registration of the 'security' tool in the CallToolRequestSchema handler switch statement, dispatching calls to handleSecurity.

  case 'permissions':
    return await this.handlePermissions(args);
  
  case 'security':
    return await this.handleSecurity(args);