Databases

With database access, the agent can check the facts behind a ticket: whether the order exists, what state the booking is in, when the payment was recorded. It only ever reads.

It works with PostgreSQL, MySQL / MariaDB, Microsoft SQL Server (including Azure SQL Database and Azure SQL Managed Instance) and MongoDB (including MongoDB Atlas). You can add as many servers as you have, of any of these kinds, and several databases on each.

You set it up in two steps:

1. Add a database server, once per server, with a read-only login.
2. Choose the databases on that server the agent may use, and describe each one.

How the agent's queries are limited#

• Read-only, always. On PostgreSQL and MySQL / MariaDB, every query runs inside a read-only transaction: even if the login could write, nothing can change from a query. SQL Server has no read-only transaction, so there the limits are layered: see SQL Server: how reads stay read-only. MongoDB isn't queried with SQL at all: see MongoDB: how reads stay read-only.
• One read statement at a time. Only SELECT, WITH, SHOW, EXPLAIN and DESCRIBE. On SQL Server, only SELECT and WITH. On MongoDB, one search of one collection, written in JSON.
• A time limit. A query stops after 15 seconds.
• A row limit. At most 200 rows per query (on MongoDB, 200 documents). Long values are cut short.
• Secrets removed. Values that look like secrets are removed before the results are stored.
• The requester's account only. The agent is told to filter every query to the requester's account, and never to put another account's data in its findings or reply. See Account field on the Ticket rules tab of Connect your ticket system.

Step 1: Create a read-only login#

Run these as an admin of your database server. Use a long random password. For MongoDB, see MongoDB and MongoDB Atlas below.

PostgreSQL#

Run the first two statements once. Run the rest for each database the agent may use, while connected to that database. In these examples the database is shop_prod and the tables are in the public schema.

-- Once per server: the login.
CREATE ROLE support_reader LOGIN PASSWORD 'a-long-random-password';
ALTER ROLE support_reader SET default_transaction_read_only = on;

-- For each database:
GRANT CONNECT ON DATABASE shop_prod TO support_reader;
\connect shop_prod
GRANT USAGE ON SCHEMA public TO support_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO support_reader;

-- Tables created later, by the role your app migrates with (here: app_owner):
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA public
  GRANT SELECT ON TABLES TO support_reader;

GRANT SELECT ON ALL TABLES covers the tables that exist now. ALTER DEFAULT PRIVILEGES covers tables that app_owner creates in future. Replace app_owner with the role that owns your tables. To find it:

SELECT DISTINCT tableowner FROM pg_tables WHERE schemaname = 'public';

Repeat the GRANT USAGE, GRANT SELECT and ALTER DEFAULT PRIVILEGES lines for each schema the agent should see.

Keep sensitive tables out. After the grants, take back the ones the agent shouldn't read:

REVOKE SELECT ON public.user_passwords, public.api_tokens, public.card_details
  FROM support_reader;

Tables created later still get SELECT through the default privileges, so revoke new sensitive tables when you add them. Or, instead of ON ALL TABLES, grant table by table:

GRANT SELECT ON public.orders, public.customers, public.invoices TO support_reader;

You can also grant only some columns of a table:

GRANT SELECT (id, email, plan, created_at) ON public.users TO support_reader;

MySQL / MariaDB#

MariaDB works exactly like MySQL here: choose MySQL / MariaDB when you add the server.

CREATE USER 'support_reader'@'%' IDENTIFIED BY 'a-long-random-password';

-- For each database (here: shop):
GRANT SELECT, SHOW VIEW ON `shop`.* TO 'support_reader'@'%';

'%' lets the login connect from any address. Replace it with the address Cerberius Agents connects from if you know it.

Keep sensitive tables out. MySQL and MariaDB can't take back one table from a database-wide grant. Grant table by table instead. This query writes the GRANT statements for every table except the ones you list. Copy its output and run it:

SELECT CONCAT('GRANT SELECT ON `', table_schema, '`.`', table_name,
              '` TO ''support_reader''@''%'';')
FROM information_schema.tables
WHERE table_schema = 'shop'
  AND table_name NOT IN ('user_passwords', 'api_tokens', 'card_details');

Run it again when you add tables.

Microsoft SQL Server and Azure SQL#

The read login gets the built-in role db_datareader in each database, and nothing more. In these examples the database is bookings.

-- In master: the login.
CREATE LOGIN support_reader WITH PASSWORD = 'a-long-random-password';

-- In each database the agent may use:
USE bookings;
CREATE USER support_reader FOR LOGIN support_reader;
ALTER ROLE db_datareader ADD MEMBER support_reader;

Azure SQL Database has no USE: connect to master for CREATE LOGIN, then connect to each database for the rest. Or skip the login in master and make a user that exists only in the database (a contained user):

-- Connected to the database itself:
CREATE USER support_reader WITH PASSWORD = 'a-long-random-password';
ALTER ROLE db_datareader ADD MEMBER support_reader;

A contained user can't open master, so Check connection can't list your databases: add the database by name (see Step 3), then check again.

Keep sensitive tables out. Take them back from the read login after the role:

DENY SELECT ON dbo.user_passwords TO support_reader;
DENY SELECT ON dbo.card_details TO support_reader;

Or, instead of db_datareader, grant table by table:

GRANT SELECT ON dbo.orders TO support_reader;
GRANT SELECT ON dbo.customers TO support_reader;

If the agent should also see the definitions of views and procedures, add GRANT VIEW DEFINITION TO support_reader; in that database. It isn't needed to read rows.

SQL Server: how reads stay read-only#

SQL Server can't make a session read-only, so the agent's queries are limited in layers:

• The login. With only db_datareader, the server itself refuses anything but reading. This is the layer that matters most: never give the read login more.
• One query, checked first. It must be one SELECT or WITH statement. Anything in it that writes or runs code is refused before it reaches the server, wherever it appears: for example INSERT, UPDATE, DELETE, INTO, EXEC, OPENROWSET, OPENQUERY, OPENDATASOURCE, any procedure starting xp_ or sp_, SET, DECLARE and transaction commands. Words inside quoted text, quoted names and comments don't count.
• Always rolled back. The query runs inside a transaction that is rolled back afterwards, whatever happens.
• Time limits. The query stops after 15 seconds, and waits no longer than that for another query's locks.

Azure SQL: before you add the server#

• Firewall. Azure SQL lets in only the addresses you allow. Under the SQL server's Networking in the Azure portal, allow the address the queries come from: our service's, or, with your own runner, your runner's. Ask us for our addresses.
• Encryption. Azure SQL needs encrypted connections. Ours always are, and the server's certificate is checked. Use the server's full name, such as bookings.database.windows.net.
• Connection policy. The login goes only to the server you name. If the server asks to move the connection to another address (the Redirect connection policy, used by default for connections from inside Azure), it is refused, and the check says so. Our service connects from outside Azure, which uses Proxy. A runner inside Azure needs the server's connection policy set to Proxy (Azure portal: the SQL server › Networking › Connectivity).
• Managed Instance. Use its public endpoint (port 3342) or, from a runner in the same network, its private one (port 1433).

MongoDB and MongoDB Atlas#

The read login gets MongoDB's built-in read role on each database the agent may use, and nothing more. Run this in mongosh as an admin. Logins are usually kept in the admin database, and the role names the database (here: shop):

db.getSiblingDB("admin").createUser({
  user: "support_reader",
  pwd: "a-long-random-password",
  roles: [{ role: "read", db: "shop" }]
})

// Another database for the same login:
db.getSiblingDB("admin").grantRolesToUser("support_reader", [{ role: "read", db: "orders" }])

If you keep the login in another database instead (db.getSiblingDB("shop").createUser(…)), enter that database under Authentication database when you add the server.

Keep sensitive collections out. The read role covers every collection in the database. To leave some out, make a role that reads only the collections the agent needs, and give the login that role instead:

db.getSiblingDB("admin").createRole({
  role: "support_read_some",
  privileges: [
    { resource: { db: "shop", collection: "orders" }, actions: ["find"] },
    { resource: { db: "shop", collection: "customers" }, actions: ["find"] }
  ],
  roles: []
})
db.getSiblingDB("admin").createUser({
  user: "support_reader",
  pwd: "a-long-random-password",
  roles: [{ role: "support_read_some", db: "admin" }]
})

MongoDB Atlas. You make the login in the Atlas website, not with mongosh:

1. 1

   Open your project, then Database Access › Add New Database User.

2. 2

   Choose Password, and enter the username and a long random password.

3. 3

   Under Database User Privileges, choose the built-in role Only read any database. Or, to limit it to some databases, give it read on just those (Specific Privileges, or a custom role).

4. 4

   Click Add User.

Atlas keeps its logins in admin, so leave Authentication database empty. Two more things before you add the server:

• Network access. Atlas lets in only the addresses on its list. Under Network Access, add the address the queries come from: our service's, or, with your own runner, your runner's. Ask us for our addresses.
• The address. Atlas shows it under Connect › Drivers, for example mongodb+srv://<username>:<password>@cluster0.abcde.mongodb.net/?retryWrites=true. Paste it, with the real username and password, into Connection address when you add the server.

MongoDB: how reads stay read-only#

MongoDB isn't queried with SQL. The agent writes each query in JSON, MongoDB's own way of asking. The limits are layered:

• The login. With only the read role, the server itself refuses anything but reading. This is the layer that matters most: never give the read login more.
• One query, checked first. Each query is one of four kinds, on one collection: find (documents that match, with the fields, order and number wanted), aggregate (a list of steps that group, count or join), count or distinct (the different values of one field). Anything in it that writes or runs code on the server is refused before it reaches the server: $out, $merge, $where, $function and $accumulator. So is anything that watches for changes or reads the server's own state, such as $changeStream and $currentOp.
• Time and size limits. A query stops after 15 seconds. At most 200 documents come back, and long ones are cut short.
• Only the servers you name. The agent connects to the host you entered and nowhere else. With an Atlas-style address, it uses the servers DNS lists for that name, and only ones under the same domain (for cluster0.abcde.mongodb.net, names ending in .abcde.mongodb.net).

To see what a database holds, the agent lists its collections and looks at a few documents in each to learn their fields.

Step 2: Add the server#

1. 1

   Go to Settings › Databases and click Add a database server.

2. 2

   Choose what kind of server it is: PostgreSQL, MySQL / MariaDB, Microsoft SQL Server or MongoDB. Each shows its logo.

3. 3

   Paste a Connection address to fill the fields in, or fill them in yourself.

4. 4

   Under Server, check Name, Host and Port. For MongoDB, also check the settings in Settings for MongoDB.

5. 5

   Under Read login, enter the Read username and Read password.

6. 6

   Click Add server.

7. 7

   On the new server's card, open the ⋯ menu and click Check connection.

To add another server later, click Add a database server at the top, or Add another database server under the last card. The servers can be of different kinds.

Change next to the kind of server at the top of the form takes you back to choose another. When editing a server, its kind is a list under Type.

The How to create the logins link at the bottom of the form shows a shorter version of the commands above, for the kind of server you chose.

Settings for MongoDB#

A MongoDB server has three more settings under Server:

• Atlas-style address (mongodb+srv). Switch it on when the address starts with mongodb+srv://, as MongoDB Atlas's always do. The host is then a name, such as cluster0.abcde.mongodb.net, whose servers are listed in DNS, so the form doesn't ask for a port. The connection is always encrypted.
• Authentication database. The database the logins are kept in. Leave it empty for admin, where they usually are, and where MongoDB Atlas always keeps them.
• Encrypted (TLS). On by default, and always on with an Atlas-style address. Switch it off only for a server on a private network that has no encryption set up: the logins and the data would then cross the network readable. The form warns about this, and the server's card says Not encrypted.

Pasting an address fills these in too. From a mongodb:// address, the form reads the login, the host and port, authSource (or else the database named after the host, as MongoDB itself does) and tls. Other options in the address are ignored. If the address lists several servers, only the first is filled in and used. For approved changes, that must be the main server (the primary, the one that takes writes).

Trust the server's certificate (SQL Server and MongoDB)#

Connections to SQL Server are always encrypted, and so are MongoDB's unless you switch that off. The server's certificate is checked: it must come from a known certificate authority and carry the server's name. The certificates of Azure SQL and MongoDB Atlas pass.

A server on your own network often has a certificate it made itself, which can't be checked. Then switch on Trust the server's certificate. The connection stays encrypted, but the certificate isn't checked, so someone between us and the server could pretend to be it and see the login. The form warns about this, and the server's card says Encrypted, but the server's certificate isn't checked. Leave it off for Azure SQL and MongoDB Atlas.

The Databases page#

Until you add a server, the page shows the kinds of server it works with and an Add a database server button. After that, the button sits at the top right, and the page has two rows:

• Servers and their databases: one card per database server. Each card shows the server's logo, name, kind and address, its logins, the result of the last check and, for SQL Server and MongoDB, whether it is encrypted and the certificate checked. An Atlas-style address shows as mongodb+srv:// and the name. A MongoDB server whose logins aren't kept in admin also says where they are. Under that come the Databases on this server the agent may use, and an Add a database on this server button.
• Approved changes: whether database changes are on. See The login for approved changes below.

The label at the top of the page shows None before you add a server, Choose a database when there is a server but no database added yet, and how many databases the agent may use once there are some.

Each server's ⋯ menu has:

What Check connection tells you#

The check opens a pop-up with what it found. The server's card then shows Connected, Has warnings or Can't connect, or Not checked before the first check. Click it to open the results again.

For the Read login, the pop-up shows the result, the server's version and how many tables the login can see. Under Databases this login can see, it lists every database on the server the login can reach.

Click Check again to run it again, or Done to close it.

Step 3: Choose the databases and describe them#

In the check's pop-up, each database the agent already uses shows Used as and its name. The others have a Let the agent use it button. After a check, the same databases also appear on the server's card as small + name buttons, after Also on this server.

1. 1

   Click Let the agent use it, or a + name button.

2. 2

   Check Name for the agent. It's filled in from the database's name, for example shop_prod becomes shop.

3. 3

   Fill in What's in it (for the agent).

4. 4

   Click Add database.

You can also click Add a database on this server on the server's card. If the last check found databases, Database on the server is a list to choose from. Choose Another, by name… to type a name instead. Typing the name is needed when the login can't see the list of databases.

What to write#

Tell the agent how your accounts map to tables. That is what lets it keep to the requester's account. For example:

The shop's main database. merchants.id is the merchant's account_id.
Most tables have a merchant_id column. orders belong to a merchant;
order_items belong to an order. Payment status is in payments.status
(pending, paid, refunded). Times are in UTC.

For MongoDB, name the collections and fields instead:

The shop's orders. merchants._id is the merchant's account.
Most collections have a merchantId field. Each order keeps its
lines in items. Payment status is in payments.status
(pending, paid, refunded). Dates are in UTC.

Each database's line on its server's card shows the agent's name for it, its name on the server, and its description, or No description yet. Use Edit… in its ⋯ menu to change the name or description, and Remove to stop the agent using it.

The login for approved changes (optional)#

If Database changes are switched on under Settings › Approved changes, the server form has a third part: Login for approved changes (optional). This login is used only when a person approves a database change, and never by the agent. See Allowing changes.

The Approved changes row on this page says whether database changes are on. Until they are, the form says so, and any saved change login is never used. To remove a saved one while they are off, click Remove it in the form, then save.

With your own runner, the change password is kept by you like the read password, and your runner makes the changes you approve (see Changes made by your runner).

Approved changes may only be INSERT, UPDATE or DELETE statements, and every UPDATE and DELETE must have a WHERE. They run in one transaction with a 30-second limit per statement. If they would change more rows than the approver agreed to, everything is rolled back.

On SQL Server, which runs two statements one after another even without a ;, each entry is also checked to be exactly one statement, with its own WHERE, and nothing that writes elsewhere (OUTPUT … INTO, SELECT … INTO) or runs code. Each statement's count of changed rows is read straight after it.

On MongoDB, approved changes are written in JSON too. Each entry is one operation on one collection: insert_one, insert_many, update_one, update_many, delete_one or delete_many. Updates and deletes need a filter that names the documents. Updates change fields ($set, $unset, $inc and the like), and never replace a whole document or add a missing one. The entries run together in one transaction, with 30 seconds for all of them. If they would touch more documents than the approver agreed to, everything is rolled back.

MongoDB can only do that on a replica set (a server kept as several copies). A single standalone server can't take approved changes; Check connection says so. MongoDB Atlas is always a replica set.

Create the change login#

Give it only the tables changes may touch. It needs SELECT too, so its UPDATE and DELETE statements can find the rows.

PostgreSQL:

CREATE ROLE support_writer LOGIN PASSWORD 'another-long-random-password';
GRANT CONNECT ON DATABASE shop_prod TO support_writer;
\connect shop_prod
GRANT USAGE ON SCHEMA public TO support_writer;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.orders, public.order_items TO support_writer;
-- Only if it inserts rows into tables with generated IDs:
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO support_writer;

MySQL / MariaDB:

CREATE USER 'support_writer'@'%' IDENTIFIED BY 'another-long-random-password';
GRANT SELECT, INSERT, UPDATE, DELETE ON `shop`.`orders` TO 'support_writer'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON `shop`.`order_items` TO 'support_writer'@'%';

Microsoft SQL Server and Azure SQL (on Azure SQL Database, connect to the database instead of USE, or make a contained user as for the read login):

-- In master:
CREATE LOGIN support_writer WITH PASSWORD = 'another-long-random-password';

-- In the database:
USE bookings;
CREATE USER support_writer FOR LOGIN support_writer;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.orders TO support_writer;
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.order_items TO support_writer;

MongoDB (in mongosh; on MongoDB Atlas, give the user readWrite on those databases under Database Access, through Specific Privileges or a custom role):

db.getSiblingDB("admin").createUser({
  user: "support_writer",
  pwd: "another-long-random-password",
  roles: [{ role: "readWrite", db: "shop" }]
})

Never give it rights to drop or alter tables, or to manage users. On SQL Server, that means no db_owner, db_ddladmin or db_datawriter for the whole database: grant table by table. On MongoDB, no dbOwner, dbAdmin, userAdmin or any database roles. readWrite can also drop collections. To be stricter, make a role with only find, insert, update and remove on the collections changes may touch, as in the read login's example.

When a change login is in use, the server's card shows Can make approved changes, and its ⋯ menu has Check change login. That connects with the change login, without changing anything. Otherwise the card shows Read only.

Change or remove a server#

• Edit… changes the server's details or logins. Leave a password empty to keep it. Click Save changes.
• Remove deletes the server and its saved logins. The agent stops using all its databases.