Connect SQL Server, PostgreSQL or MySQL to Reporting Safely

The safe way to connect a production database to any reporting or analytics tool is to create a dedicated read-only user that can see only the tables it needs, require SSL, and restrict which network addresses can connect. That way, even a buggy query or a leaked password cannot change or delete data. Below are example statements for SQL Server, PostgreSQL and MySQL, plus a checklist for the rest.

Principles: least privilege in four lines

• Separate user per tool. Never reuse the application's login. If you stop using the tool, you drop one user.
• Read-only. SELECT permission only. No INSERT, UPDATE, DELETE, DDL or execute rights.
• Narrow scope. Only the schema, tables or views needed. Consider a reporting view that leaves out personal columns.
• Controlled path. SSL required, and connections allowed only from known IP addresses.

PostgreSQL: create a read-only role

Replace reporting_db, reporting and the password with your own values. Run as a superuser or the schema owner.

CREATE ROLE reporting_ro LOGIN PASSWORD 'use-a-long-random-password';
GRANT CONNECT ON DATABASE reporting_db TO reporting_ro;
GRANT USAGE ON SCHEMA reporting TO reporting_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA reporting TO reporting_ro;
ALTER DEFAULT PRIVILEGES IN SCHEMA reporting GRANT SELECT ON TABLES TO reporting_ro;
ALTER ROLE reporting_ro SET default_transaction_read_only = on;

The last line makes sessions read-only by default as an extra safety net. In pg_hba.conf, use a hostssl entry for this role limited to the tool's IP addresses so non-SSL connections are refused. You can also set statement_timeout on the role to stop runaway queries.

MySQL: create a read-only user

CREATE USER 'reporting_ro'@'203.0.113.10' IDENTIFIED BY 'use-a-long-random-password' REQUIRE SSL;
GRANT SELECT ON sales_db.* TO 'reporting_ro'@'203.0.113.10';
FLUSH PRIVILEGES;

The host part (203.0.113.10 here, an example address) limits where the user can connect from; avoid '%' for production. REQUIRE SSL refuses unencrypted connections. To expose only certain tables, grant on each table (GRANT SELECT ON sales_db.orders TO …) instead of sales_db.*.

SQL Server: create a read-only login

CREATE LOGIN reporting_ro WITH PASSWORD = 'use-a-long-random-password', CHECK_POLICY = ON;
USE SalesDb;
CREATE USER reporting_ro FOR LOGIN reporting_ro;
ALTER ROLE db_datareader ADD MEMBER reporting_ro;

db_datareader grants SELECT on all tables and views in that database. For tighter scope, skip the role and grant on a schema instead: GRANT SELECT ON SCHEMA::reporting TO reporting_ro;. You can also add an explicit DENY SELECT on sensitive tables. Enable Force Encryption on the server, or require encryption in the connection, so traffic is protected in transit.

Use views to hide personal information

If a table mixes useful facts with personal details, create a view that exposes only what reporting needs, and grant SELECT on the view rather than the table. For example, a reporting.orders_v view might include order date, region, product and amount, but not customer name, email, phone number or ID number. That is data minimisation in practice, which fits POPIA's principles; see POPIA-compliant data analytics for more. (General information, not legal advice.)

Network and connection checklist

Store and share credentials carefully

Generate a long random password for the reporting user and keep it in a password manager, not in an email thread or a shared spreadsheet. Enter it once into the reporting tool, and check that the tool stores it encrypted. If several people need to set up connections, give each tool its own user rather than sharing one login, so the database logs show exactly which system ran each query. When someone who knew the password leaves, or a tool is retired, rotate or drop the user the same day.

Test the permissions

1. Connect as the new user and run a SELECT on an allowed table. It should work.
2. Try an UPDATE or CREATE TABLE. It should fail with a permission error.
3. Try to SELECT from a table outside the granted scope. It should fail.
4. Try connecting without SSL. It should be refused.

Summarix connects to SQL Server, PostgreSQL and MySQL with this approach in mind: queries are single, read-only SELECT statements run in a sandbox with timeouts and row caps, SSL is on by default, and saved credentials are encrypted with AES-256-GCM. Even so, give it a read-only user as above; defence in depth is the point. More on the security page.

A read-only user, SSL, an IP allowlist and a reporting view take about half an hour to set up and remove most of the risk of connecting analytics to production. Once that is done you can stop exporting CSVs by hand and, if you still need to handle big exports, see analysing large CSV files.