Secure PostgreSQL, MySQL, MongoDB Connections in Retool

When setting up Retool for internal tools, common issues arise: incorrect permissions, insecure connections, and N+1 queries that kill performance. A typical scenario: a developer grants the database superuser access and then wonders why data leaks. Or connects MongoDB without SSL, leaving traffic in plaintext. Recently, a client lost 2 days deploying an admin panel due to incorrect permissions — we fixed it in 3 hours. We set up database connections (PostgreSQL, MySQL, MongoDB) from scratch: from creating a dedicated user to configuring an SSH tunnel. Below are typical configs, best practices, and real pitfalls we avoid.

Problems We Solve

N+1 queries are a major cause of slowdowns. Retool without caching can execute dozens of queries to render a single widget. For example, a list of customers with their orders: without joins, you get 1 query per customer + N for orders. This increases load time by 500%. The solution: use parameterized queries with LEFT JOIN inside a single call.

Insecure connections — a database with a public IP, a superuser password in plaintext, and no SSL. In one project, we found that MongoDB could be connected without a password from anywhere in the world. We had to urgently change the settings.

Permissions — often roles are too broad. This leads to accidental data deletion or SQL injections. We always create a user with minimal privileges.

Avoiding N+1 Queries

N+1 occurs when for each parent object, a separate child query is executed. In Retool, this is especially noticeable in tables with lookups. The optimal approach is a single SQL query with JOIN, aggregation, or IN () expressions. For example, instead of:

SELECT * FROM customers; -- 1 query -- for each customer: SELECT * FROM orders WHERE customer_id = $1; 

Use:

SELECT c.*, o.order_count FROM customers c LEFT JOIN ( SELECT customer_id, COUNT(*) as order_count FROM orders GROUP BY customer_id ) o ON c.id = o.customer_id; 

Typical latency from N+1 is 2-3 seconds per page. After optimization, the query runs in 50 ms.

Connecting PostgreSQL, MySQL, and MongoDB

Retool supports popular databases. We'll cover connecting each. Important: all connections must be encrypted, and users must have limited privileges.

PostgreSQL

Follow these steps to connect PostgreSQL:

1. Create a resource in Retool: Resources → Create New → PostgreSQL.
2. Provide your database hostname (e.g., your-database-server.com), port 5432, database name, a read-only username, and password.
3. Set SSL mode to require.
4. Create a dedicated read-only user using the SQL example below.

Example SQL to create a read-only user:

CREATE USER retool_readonly WITH PASSWORD 'secure_password'; GRANT CONNECT ON DATABASE app_production TO retool_readonly; GRANT USAGE ON SCHEMA public TO retool_readonly; GRANT SELECT ON ALL TABLES IN SCHEMA public TO retool_readonly; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO retool_readonly; 

MySQL

For MySQL, also use a separate read-only user. Enable SSL: Required and set charset to utf8mb4. Default port is 3306.

MongoDB

Connection string: mongodb://user:pass@host:27017/db?ssl=true. In production, use a replica set with retryWrites=true. Default port is 27017.

SSH Tunnel Configuration

If the database does not have a public IP, use an SSH tunnel. Retool supports setting it up in a few minutes.

1. On the bastion server, create a user retool-tunnel with restricted commands.
2. When creating a resource in Retool, enable SSH tunnel and specify:
   • SSH host: your-bastion-host
   • SSH port: 22
   • SSH username: retool-tunnel
   • SSH private key: your SSH private key
3. On the bastion server, restrict commands:

Match User retool-tunnel ForceCommand /bin/false PermitTunnel yes AllowTcpForwarding yes 

Parameterized Queries vs. SQL Injections

Retool automatically escapes variables in {{ }}. Always use them. Example of safe update:

UPDATE users SET status = {{ statusSelect.value }}, updated_at = NOW(), updated_by = {{ current_user.email }} WHERE id = {{ usersTable.selectedRow.data.id }} AND status != {{ statusSelect.value }} 

Never concatenate strings manually — that is a direct path to injections. According to Retool Documentation, parameterized queries are the only safe method.

Access Permissions for Retool

It is best to create a separate read-only user. If writes are needed, grant rights only to specific tables. Example for PostgreSQL:

GRANT SELECT, UPDATE ON users TO retool_ops; GRANT SELECT, INSERT, UPDATE ON orders TO retool_ops; 

Turnkey Setup: What's Included

We provide a full cycle of work:

• Architecture analysis and optimal stack selection
• Creating database users with minimal privileges
• Configuring SSH tunnel and SSL
• Configuring resources in Retool
• Writing the first 5–10 queries and widgets
• Documentation on connection and security
• Team training (1 hour)

Timeline — from 1 day. Cost is calculated individually based on complexity. Typical pricing for a turnkey Retool setup under key starts at $800 and includes all steps above. For Retool optimization, we recommend regular performance audits and connection pooling.

Common Setup Mistakes

• Incorrect host: use private IP if Retool and database are on the same network.
• Missed firewall: check that Retool's IP is allowed in security group.
• Wrong port: verify with the DBA.
• Missing SSL: always enable SSL mode require or ssl=true.

Why Trust Us with Setup?

We have 5+ years of experience with Retool and databases. We guarantee security and performance. We have configured over 50 projects. Retool is 5x faster than custom admin panels — you save up to 40% development time. Get a consultation — we will assess your project.