Create a read-only database user for the Agent

Connector setup · Every database

Criar um somente leitura 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.

Cerca de 10 minutos SQL Server, PostgreSQL, MySQL e muito mais Read only

O resultado final será o que você obterá.

A new database login, called leitor_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 Nome de usuário e Senha boxes when you add the connector in Agentes de dados.

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 SELECIONAR (or a few other read-only words such as COM, SHOW e EXPLICAR), or that contains INSERIR, ATUALIZAR, EXCLUIR, DERRUBAR, ALTERAR, CONCEDER 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, postgres, root, your WordPress or hosting account) out of Query Streams entirely.

Do que você precisa

Pick your database

• Microsoft SQL Server (including SQL Express)
• PostgreSQL
• MySQL and MariaDB (including cPanel and WordPress)
• Oráculo, Floco de neve ou Google BigQuery

In every command below, replace leitor_qs if you want a different name, replace ChangeMe-2026! with your own password, and replace Vendas 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 Autenticação sobre Autenticação do Windows e clique em Conectar.

   A panel called Object Explorer fills the left side, with your server’s name at the top and folders such as Bancos de dados e Segurança 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 Certificatee clique Conectar 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 Propriedades. In the list on the left of the window that opens, click Segurança. Sob Server authentication there are two choices.

   Se 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, então OK. A message says the change takes effect after SQL Server restarts; click OK. Then right-click the server name again and choose Restarte clique Sim 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

   Clique no botão 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 (Vendas appears twice), then click Executar 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

   O 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 Bancos de dados in Object Explorer to see the real name and change both Vendas 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 Vendas.

The click-through version. Instead of steps 3 and 4: in Object Explorer, expand Segurança, right-click Logins, escolher New Login…. On the General page type leitor_qs em Login name, escolher SQL Server authentication, type the password in Senha e Confirm password, e 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 leitor_de_dados_banco_de_dados and leave público ticked. Click OK. The new name appears under Security → Logins.

Agent on a different computer from SQL Server? Porto 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 Servidores on the left, enter the postgres password you chose when PostgreSQL was installed, expand Bancos de dados, 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 vendas, and run it (in pgAdmin press F5; in psql press Enter). If your tables are not in the público schema, change público 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, então CONCEDER 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

   Aberto postgresql.conf in a text editor. On Windows: click Start, type Notepad, right-click it and choose Run as administrator, então 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 e 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, abrir Command Prompt, type ipconfig e pressione Enter. The line IPv4 Address shows something like 192.168.1.25.

   Aberto pg_hba.conf the same way as the last file. At the bottom are lines starting hospedar. 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: clique Start, type Serviços, abra o Serviços app, find the entry whose name starts with postgresql-x64-, right-click it and choose Restart. Its Status 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 com 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 e pressione 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 *.* em vez 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

   meu.ini em 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

   endereço-de-vinculação = 0.0.0.0

4. Restart the database

   Linux: tipo sudo systemctl restart mysql (ou sudo systemctl restart mariadb) and press Enter; it prints nothing when it works. Windows: no Serviços 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 senha_nativa_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 e Remote MySQL; cPanel 120 and later calls them Manage My Databases e Remote Database Access.

1. Create the user

   Aberto Manage My Databases (ou MySQL Databases). Under Adicionar um usuário, type leitor_qs em Nome de usuário, 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

   Sob Add User To Database, pick the new user and your database in the two menus and click Adicionar. A page of privilege tick boxes opens (MySQL Account Maintenance). Do não tick ALL PRIVILEGES. Tick only SELECIONAR, depois clique 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 Hospedar, add a Comment como Agente de fluxos de consultae clique 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 e DB_PASSWORD. Usar DB_NAME e 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.

Oráculo

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 Esquema padrão. 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.

Floco de neve

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 e 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 em Função e COMPUTE_WH em Armazém. 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:

Visualizador de dados do BigQuery

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

Usuário de trabalho do 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 Chave JSON da conta de serviço in the Portal.

What the message means

After you click Teste de conexão 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

Aberto Agentes de dados, clique Adicionar conector on your Agent, pick your database, and type the new user’s name and password into Nome de usuário e Senha. Then follow the guide for where you want the data:

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

Guias relacionados

• Instale o Agente no Windows
• Is the Agent online?
• Open a firewall port on Windows
• Configure o conector do Microsoft Access.
• Todos os guias