ChatDB
Docs
MongoDB Database User Setup

MongoDB Database User Setup

How to create a MongoDB database user for executing queries and retrieving schema information

This guide explains how to create a dedicated MongoDB database user with the appropriate permissions for executing queries and retrieving schema information in your Text-to-SQL application.

Prerequisites

  • Access to a MongoDB server with administrative privileges
  • MongoDB Shell (mongosh) or MongoDB Compass
  • Basic knowledge of MongoDB security concepts

Creating a Database User

Using MongoDB Shell (mongosh)

  1. Connect to your MongoDB server as an administrator:
mongosh --host your_host --port 27017 -u admin_username -p admin_password --authenticationDatabase admin
  1. Switch to the database you want to create a user for:
use your_database
  1. Create a new user with read-only permissions:
db.createUser({
  user: "texttosql_user",
  pwd: "your_secure_password",
  roles: [
    { role: "read", db: "your_database" },
    // Add this role if you need to access system collections for schema information
    { role: "clusterMonitor", db: "admin" }
  ]
})
  1. If you need the user to have write access as well, use this command instead:
db.createUser({
  user: "texttosql_user",
  pwd: "your_secure_password",
  roles: [
    { role: "readWrite", db: "your_database" },
    { role: "clusterMonitor", db: "admin" }
  ]
})
  1. Exit the MongoDB shell:
exit

Using MongoDB Compass

  1. Connect to your MongoDB server using MongoDB Compass with administrative privileges
  2. Navigate to your database
  3. Click on the "Users" tab in the left sidebar
  4. Click "Add New User"
  5. Enter the username (e.g., texttosql_user) and a secure password
  6. Under "User Privileges", select:
    • Database: your_database
    • Role: read (or readWrite if you need write access)
  7. Click "Add Role" and add:
    • Database: admin
    • Role: clusterMonitor
  8. Click "Add User" to create the user

Verifying the Setup

To verify that the user has the correct permissions:

  1. Connect to MongoDB using the new user:
mongosh --host your_host --port 27017 -u texttosql_user -p your_secure_password --authenticationDatabase your_database
  1. Try to retrieve collection information:
use your_database
db.getCollectionNames()
  1. Try executing a simple query:
db.your_collection.find().limit(5)
  1. Try retrieving schema information:
db.your_collection.findOne()

Connection Configuration

When configuring your Text-to-SQL application, use these credentials:

  • Host: Your MongoDB server hostname or IP
  • Port: 27017 (default MongoDB port)
  • Database: Your database name
  • Username: texttosql_user
  • Password: your_secure_password
  • Authentication Database: your_database

Schema Inference in MongoDB

Since MongoDB is a NoSQL database with a flexible schema, the Text-to-SQL application will infer the schema by:

  1. Sampling documents from collections
  2. Analyzing the structure and data types of fields
  3. Creating a virtual schema representation for query generation

The clusterMonitor role is important as it allows the application to gather metadata about collections and indexes, which helps in building more accurate schema representations.

Security Considerations

  • Use a strong, unique password for the database user
  • Enable MongoDB's authentication and authorization features
  • Consider enabling TLS/SSL for encrypted connections
  • Limit the user's permissions to only what is necessary
  • Regularly audit the user's permissions to ensure they remain appropriate
  • Store connection credentials securely and never expose them in client-side code
  • Consider using MongoDB Atlas's network security features if you're using their cloud service
MSSQLOracle