Generic SQL Connection

The Generic SQL connection allows you to integrate any PostgreSQL or MySQL database with OpsTower using a standard connection string. Agents can run read-only SQL queries against your database for data lookups and analysis. All queries are limited to SELECT statements and return a maximum of 100 rows per query.

Prerequisites

You need a PostgreSQL or MySQL database that is accessible over the network. The database must accept connections from external hosts (or be reachable through a tunnel).

Credentials Required

To set up a Generic SQL connection, you need two pieces of information:

• Connection String — a standard database connection URI
• Database Type — either PostgreSQL or MySQL

Connection String Formats

PostgreSQL

postgres://user:password@host:5432/dbname

or

postgresql://user:password@host:5432/dbname

MySQL

mysql://user:password@host:3306/dbname

Replace user, password, host, 5432/3306, and dbname with your actual database credentials, host address, port, and database name.

Security Considerations

OpsTower validates all queries as read-only before execution, but it is strongly recommended to create a dedicated read-only database user for the connection. This provides defense in depth.

Creating a Read-Only User for PostgreSQL

CREATE USER opstower WITH PASSWORD 'your-secure-password';
GRANT CONNECT ON DATABASE your_database TO opstower;
GRANT USAGE ON SCHEMA public TO opstower;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO opstower;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO opstower;

Creating a Read-Only User for MySQL

CREATE USER 'opstower'@'%' IDENTIFIED BY 'your-secure-password';
GRANT SELECT ON your_database.* TO 'opstower'@'%';
FLUSH PRIVILEGES;

Network Access

Ensure your database is accessible from the internet. Common approaches include:

• Cloud-hosted databases typically provide a public endpoint that you can enable in your provider’s settings.
• SSH tunnels or VPN connections may be needed for databases on private networks.
• Firewall rules should allow inbound connections on the database port from OpsTower’s infrastructure.

Setting Up the Connection in OpsTower

1. In OpsTower, navigate to Connections in the sidebar.
2. Click Add Connection and select Generic SQL.
3. Select your Database Type — either PostgreSQL or MySQL.
4. Enter your Connection String.
5. Click Save to create the connection.

What Agents Can Do with Generic SQL

Once connected and enabled on an agent, the agent can:

• Execute read-only SQL queries (SELECT statements only) against your database
• List all tables and schemas in the database
• Inspect table structures to understand your data model

All queries are validated as read-only before execution. Write operations (INSERT, UPDATE, DELETE, DROP, etc.) are rejected.

Troubleshooting

• Connection refused: Verify that the host and port in your connection string are correct and that the database is accepting remote connections.
• Authentication failed: Double-check the username and password in your connection string. Ensure the user exists and has the correct permissions.
• SSL errors: Some databases require SSL connections. Check your database provider’s documentation for SSL connection string parameters (e.g., ?sslmode=require for PostgreSQL).
• Timeout errors: Ensure your database is reachable from the internet. If the database is behind a firewall, configure access rules to allow inbound connections.
• Permission denied on tables: Verify that the database user has SELECT privileges on the tables you want to query.