Create a read-only database user for the Agent

Connector setup · Every database

Créer un lecture seule database user for the Agent

The Agent only ever reads. Give it a login that can only read, so nothing anyone does in Query Streams can change your data. One section per database, with the exact commands to copy.

Environ 10 minutes SQL Server, PostgreSQL, MySQL et bien plus encore Read only

Ce que vous obtiendrez

A new database login, called lecteur qs in the examples below, with a password you choose. It can sign in and read the tables you allow, and nothing else. You type that name and password into the Nom d'utilisateur et Mot de passe boxes when you add the connector in Agents de données.

Why a separate read-only login

The Agent already refuses to run anything that is not a read. Every query it is asked to run, whether it comes from the Portal, Excel, Google Sheets, an AI assistant or the REST API, is checked on your computer before it reaches the database. Anything that starts with something other than SELECTIONNER (or a few other read-only words such as AVEC, SHOW et EXPLIQUER), or that contains INSÉRER, MISE À JOUR, SUPPRIMER, BAISSE, MODIFIER, ACCORDER and similar words, is turned away with the message Only SELECT queries are permitted in read-only mode.

A read-only login is the second lock. That check is a word filter on our side; a read-only login is enforced by your database itself. With both in place, even a mistake on our side could not change a row. It also keeps the powerful logins (sa, PostgreSQL, root, your WordPress or hosting account) out of Query Streams entirely.

Ce dont vous avez besoin

Pick your database

• Microsoft SQL Server (including SQL Express)
• PostgreSQL
• MySQL and MariaDB (including cPanel and WordPress)
• Oracle, Flocon de neige ou Google BigQuery

In every command below, replace lecteur qs if you want a different name, replace ChangeMe-2026! with your own password, and replace Ventes with the name of your own database.

Microsoft SQL Server

The Portal’s SQL Server connector signs in with a SQL Server login (a name and a password), not with your Windows account. Many SQL Server and SQL Express installs only accept Windows accounts until you switch one setting, so the first two steps check that. Then you create the login, either by typing or by clicking.

1. Open SQL Server Management Studio

   On the computer that runs SQL Server (or any computer that has SQL Server Management Studio, SSMS for short), click Start, type SSMS and open it. A box titled Connect to Server appears. Leave Authentification sur Authentification Windows et cliquez Connecter.

   A panel called Object Explorer fills the left side, with your server’s name at the top and folders such as Bases de données et Sécurité under it. If instead you see an error that mentions a certificate chain ou not trusted, go back to the Connect box, tick Trust Server Certificate, et cliquez Connecter again.

2. Turn on SQL Server logins (the Windows-only trap)

   In Object Explorer, right-click the server name at the very top and choose Propriétés. In the list on the left of the window that opens, click Sécurité. Sous Server authentication there are two choices.

   Si SQL Server and Windows Authentication mode is already selected, click Cancel and go to step 3. If Windows Authentication mode is selected, click SQL Server and Windows Authentication mode, puis D'ACCORD. A message says the change takes effect after SQL Server restarts; click D'ACCORD. Then right-click the server name again and choose Restart, et cliquez Oui when asked. The icon by the server name turns grey and back to green in under a minute.

   Why this matters: while the server is on Windows-only, the Agent’s test fails with Connection failed: Login failed for user ‘qs_reader’. That is exactly the message you would get for a wrong password, so people retype the password again and again. If you are sure of the password, this setting is the reason.

3. Open a query window

   Cliquez sur le bouton New Query button on the toolbar near the top. A blank white page opens in the middle. That is where you paste. (Prefer clicking to typing? Skip to the click-through version below.)

4. Paste the commands and run them

   Copy the block below, paste it into the query window, change the password and the database name (Ventes appears twice), then click Exécuter on the toolbar or press F5:

   Paste into the query window, then press F5

   CREATE LOGIN qs_reader WITH PASSWORD = 'ChangeMe-2026!', CHECK_EXPIRATION = OFF;
   GO
   USE Sales;
   GO
   CREATE USER qs_reader FOR LOGIN qs_reader;
   ALTER ROLE db_datareader ADD MEMBER qs_reader;
   GO

   Le Messages tab at the bottom says Commands completed successfully. If it says the password does not meet the operating system policy, pick a longer password with a mix of letters, numbers and a symbol, and run it again. If it says Database ‘Sales’ does not exist, expand Bases de données in Object Explorer to see the real name and change both Ventes lines.

   Want the Agent to read more than one database? Run the last three lines again for each one, with its name in place of Ventes.

The click-through version. Instead of steps 3 and 4: in Object Explorer, expand Sécurité, right-click Logins, choisir New Login…. On the General page type lecteur qs dans Login name, choisir SQL Server authentication, type the password in Mot de passe et Confirm password, et untick User must change password at next login (the Agent cannot change a password, so with this ticked it never gets in). Then click User Mapping on the left. Under Users mapped to this login, tick the box beside your database. In the lower box, Database role membership for, tick lecteur de données de base de données and leave publique ticked. Click D'ACCORD. The new name appears under Security → Logins.

Agent on a different computer from SQL Server? Port 1433 (or the instance’s own port, plus UDP 1434 for a named instance such as SQLEXPRESS) has to be open on the SQL Server computer: open a firewall port on Windows.

PostgreSQL

Three parts: create the login, then make sure PostgreSQL accepts connections from the Agent’s computer, which it does not out of the box. If the Agent runs on the same computer as PostgreSQL and you will type localhost as the server, you only need part one.

1. Open a query window as the postgres user

   With pgAdmin (installed alongside PostgreSQL on Windows): open pgAdmin, expand Serveurs on the left, enter the PostgreSQL password you chose when PostgreSQL was installed, expand Bases de données, click your database once, then choose Tools → Query Tool. A blank editor opens.

   On a Linux server, type this and press Enter; the prompt changes to sales=#:

   Type this, then press Enter

   sudo -u postgres psql -d sales

2. Create the login and give it read access

   Paste the block below, change the password and ventes, and run it (in pgAdmin press F5; in psql press Enter). If your tables are not in the publique schema, change publique too, or repeat the last three lines for each schema.

   Paste, then run

   CREATE ROLE qs_reader LOGIN PASSWORD 'ChangeMe-2026!';
   GRANT CONNECT ON DATABASE sales TO qs_reader;
   GRANT USAGE ON SCHEMA public TO qs_reader;
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO qs_reader;
   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO qs_reader;

   You see CREATE ROLE, puis ACCORDER three times and ALTER DEFAULT PRIVILEGES. The last line makes tables created later readable too, but only tables created by the user who ran it; if your application creates tables as its own user, run that line while signed in as that user. If you see database "sales" does not exist, use the name exactly as pgAdmin lists it; if it contains capital letters, put it in double quotation marks, like "Sales".

3. Find PostgreSQL’s two settings files

   Still in the query window, run these two lines one at a time:

   Run each, note the answer

   SHOW config_file;
   SHOW hba_file;

   Each answers with a full path. On Windows they are usually in C:\Program Files\PostgreSQL\17\data\ (the number is your version); on Ubuntu or Debian, /etc/postgresql/17/main/. The first file is postgresql.conf, the second pg_hba.conf. Write both paths down.

4. Let PostgreSQL listen to the network

   Ouvrir postgresql.conf in a text editor. On Windows: click Start, type Notepad, right-click it and choose Run as administrator, puis File → Open, paste the path, and change the file type box at the bottom right to All Files. On Linux: sudo nano followed by the path.

   Find the line that starts listen_addresses (Notepad: press Ctrl et F together). If it reads listen_addresses = '*', leave it. If it reads 'localhost' or starts with a #, replace the whole line with:

   The line in postgresql.conf

   listen_addresses = '*'

   Save the file. Nothing changes yet; this setting is only read when PostgreSQL restarts (step 6).

5. Allow the Agent’s computer in pg_hba.conf

   First find the Agent computer’s address: on that computer click Start, type cmd, ouvrir Command Prompt, type ipconfig et presse Enter. The line IPv4 Address shows something like 192.168.1.25.

   Ouvrir pg_hba.conf the same way as the last file. At the bottom are lines starting hôte. Look at the last word on those lines, usually scram-sha-256 (older servers say md5), and add this line at the very end, using that same last word and the Agent’s address with /32 after it:

   Add at the end of pg_hba.conf

   host    all    qs_reader    192.168.1.25/32    scram-sha-256

   Save the file. To allow any computer on that office network instead of one, write 192.168.1.0/24.

6. Restart PostgreSQL

   Windows: cliquez Start, type Services, ouvrez le Services app, find the entry whose name starts with postgresql-x64-, right-click it and choose Restart. Its Statut column blinks and shows Running again. Linux: type the line below; it prints nothing when it works.

   Linux: type this, then press Enter

   sudo systemctl restart postgresql

   If PostgreSQL does not come back (Status stays blank, or the command prints failed), a typo crept into one of the two files. Open them again, compare with the lines above, save, and restart again. Port 5432 also has to be open in the server’s firewall: open a firewall port on Windows.

The Use SSL tick box. The Portal’s PostgreSQL connector has Use SSL ticked by default, and with it ticked the Agent insists on an encrypted connection. A PostgreSQL installed on Windows usually has encryption switched off, so the test fails with Connection failed: SSL connection requested. No SSL enabled connection from this host is configured. That message means: untick Use SSL and test again. Leave it ticked for any database in the cloud.

What a missing pg_hba.conf line looks like today. You might expect a clear refusal. Instead, the Portal shows an amber Signed in avec No database opened yet. Name it on the next step. and the Agent’s message Login succeeded, but no database could be opened (tried postgres, template1). Enter the database name. If you then type your database name, it says This login can’t open database ‘sales’. Despite the words “Login succeeded”, this is almost always pg_hba.conf turning the Agent away: go back to step 5. We are fixing the message so it says so.

MySQL and MariaDB

MySQL and MariaDB use the same commands. One thing surprises everybody: a MySQL user belongs to a place. 'qs_reader'@'localhost' can only sign in from the database computer itself; the same name and password from the Agent’s computer is refused. The part after the @ has to match where the Agent is.

1. Open a query window as an administrator

   MySQL Workbench: open it, click your server under MySQL Connections, enter the root password; a query tab opens. On a Linux server: type sudo mysql et presse Enter; the prompt changes to mysql> ou MariaDB [(none)]>.

2. Create the user and allow SELECT only

   Paste the block below, change the password, the database name (shop) and the part after the @ if you chose something other than '%' (it appears twice), and run it (Workbench: the lightning-bolt button; command line: Enter):

   Paste, then run

   CREATE USER 'qs_reader'@'%' IDENTIFIED BY 'ChangeMe-2026!';
   GRANT SELECT ON shop.* TO 'qs_reader'@'%';
   FLUSH PRIVILEGES;

   Each line answers Query OK. To let it read every database on the server, write *.* au lieu de shop.*. If it says Operation CREATE USER failed, a user of that name and place already exists: pick another name, or ask whoever set it up.

3. Let the server listen to the network

   On Ubuntu and Debian, MySQL and MariaDB only listen to the database computer itself until you change bind-address. Open the settings file (sudo nano and the path):

   MySQL on Ubuntu/Debian

   /etc/mysql/mysql.conf.d/mysqld.cnf

   MariaDB on Ubuntu/Debian

   /etc/mysql/mariadb.conf.d/50-server.cnf

   MySQL on Windows

   mon.ini dans C:\ProgramData\MySQL\MySQL Server 8.0\ (ProgramData is hidden: paste the path into File Explorer’s address bar)

   Find the line bind-address = 127.0.0.1. Change it to the line below and save. On Windows there is usually no such line, which means it already listens; leave the file alone.

   The line in the settings file

   adresse de liaison = 0.0.0.0

4. Restart the database

   Linux: taper sudo systemctl restart mysql (ou sudo systemctl restart mariadb) and press Enter; it prints nothing when it works. Windows: dans le Services app right-click MySQL80 (ou MariaDB) and choose Restart. Port 3306 also has to be open in the server’s firewall: open a firewall port on Windows.

The Use SSL tick box and the RSA message. The Portal’s MySQL and MariaDB connectors have Use SSL ticked by default. A MariaDB without encryption set up fails with Connection failed: Server does not support SSL: untick Use SSL and test again. On MySQL 8 or later, keep it ticked. MySQL creates its own certificate, and unticking it leads to Connection failed: Authentication method 'caching_sha2_password' failed. Either use a secure connection, specify the server's RSA public key with ServerRSAPublicKeyFile, or set AllowPublicKeyRetrieval=True. The Agent cannot do the last two today, so the answer is to tick Use SSL again. (Switching the user to the older mot de passe natif MySQL method also works on MySQL 8.0, but MySQL 8.4 switches that method off by default and MySQL 9 removed it, so we do not recommend it.)

The cPanel route (most web hosts). In cPanel the Databases section has two pages you need. Older cPanel calls them MySQL Databases et Remote MySQL; cPanel 120 and later calls them Manage My Databases et Remote Database Access.

1. Create the user

   Ouvrir Manage My Databases (ou MySQL Databases). Under Ajouter un utilisateur, type lecteur qs dans Nom d'utilisateur, type the password in both password boxes, and click Create User. cPanel puts your account name in front, so the real username becomes something like myaccount_qs_reader. Write that full name down; it is what goes in the Portal.

2. Give it SELECT on your database, nothing else

   Sous Add User To Database, pick the new user and your database in the two menus and click Ajouter. A page of privilege tick boxes opens (MySQL Account Maintenance). Do pas tick ALL PRIVILEGES. Tick only SELECTIONNER, puis cliquez Make Changes.

3. Allow the Agent’s address

   On the Agent’s computer, open a browser and search for what is my IP; note the address it shows (this is the office’s public address, not the 192.168 one). In cPanel open Remote Database Access (ou Remote MySQL), type that address in Hôte, add a Comment tel que Agent de flux de requêtes, et cliquez Add Host. If you do not see this page at all, your host has switched it off; ask them, or install the Agent on the hosting server if they allow it.

WordPress sites. The database name, server and login are in the site’s wp-config.php file, on the lines DB_NAME, DB_HOST, DB_USER et DB_PASSWORD. Utiliser DB_NAME et DB_HOST from there, but do not give the Agent that DB_USER: it is the account WordPress itself writes with. Make the separate read-only user above instead.

Oracle

Run this as a DBA (for example SYSTEM) in SQL Developer or SQL*Plus, connected to the pluggable database that holds your tables (on Oracle XE that is XEPDB1, on Oracle Free FREEPDB1). Replace SALES.ORDERS with each table the Agent should read, one GRANT line per table.

CREATE USER qs_reader IDENTIFIED BY "ChangeMe-2026!";
GRANT CREATE SESSION TO qs_reader;
GRANT SELECT ON SALES.ORDERS TO qs_reader;

Each line answers User created ou Grant succeeded. In the Portal, type the owner of the tables (here SALES, in capitals) in Schéma par défaut. Left blank, it uses QS_READER, which owns no tables, and the test says Schema `QS_READER` doesn’t exist or this login can’t read any of its tables. Granting every table at once (READ ANY TABLE) covers every schema in the database, including system ones; ask your DBA before using it.

Flocon de neige

In Snowsight, open a new SQL worksheet as a user with the SECURITYADMIN ou ACCOUNTADMIN role and run the block below. Replace COMPUTE_WH, SALES et QS_USER (the Snowflake user the Agent signs in as) with your own names.

CREATE ROLE QS_READER;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE QS_READER;
GRANT USAGE ON DATABASE SALES TO ROLE QS_READER;
GRANT USAGE ON ALL SCHEMAS IN DATABASE SALES TO ROLE QS_READER;
GRANT SELECT ON ALL TABLES IN DATABASE SALES TO ROLE QS_READER;
GRANT SELECT ON FUTURE TABLES IN DATABASE SALES TO ROLE QS_READER;
GRANT ROLE QS_READER TO USER QS_USER;

Each line answers Statement executed successfully. In the Portal, type QS_READER dans Rôle et COMPUTE_WH dans Entrepôt. If a warehouse or role is missing a grant, the test says Invalid Warehouse ou Invalid Role. The FUTURE line needs SECURITYADMIN or higher, and if a schema has its own future grants, Snowflake ignores this one for that schema.

Google BigQuery

BigQuery has no SQL logins. The Agent signs in as a Google Cloud service account, and two roles make it read-only:

Visionneuse de données BigQuery

Reads tables and views. Give it on the project, or only on the datasets you want to share.

Utilisateur de tâches BigQuery

Lets it run queries. Give it on the project. Without it the test says This service account can’t run queries in project …

In the Google Cloud console go to IAM & Admin → IAM, find the service account (or click Grant access and paste its email address), and add both roles. Do not add BigQuery Data Editor, BigQuery Admin, Editor ou Owner; any of those can change data. You then paste the account’s JSON key into Clé JSON du compte de service in the Portal.

What the message means

After you click Test de connexion in Data Agents, the message under Connection failed (or under an amber Signed in) is the Agent’s own words. Test connection runs on the Agent’s computer, so it needs the Agent online first: Is the Agent online? Then find your message:

Next: add the connection

Ouvrir Agents de données, cliquez Ajouter un connecteur on your Agent, pick your database, and type the new user’s name and password into Nom d'utilisateur et Mot de passe. Then follow the guide for where you want the data:

• Microsoft SQL Server to Excel
• PostgreSQL vers Excel
• MySQL vers Excel · MariaDB vers Excel
• Oracle vers Excel · Snowflake vers Excel · BigQuery vers Excel

Guides associés

• Installer l'agent sur Windows
• Is the Agent online?
• Open a firewall port on Windows
• Configurez le connecteur Microsoft Access
• Tous les guides