Read Only User

This page describes how to create a read-only MySQL/MariaDB user. Through this user you will be sure that the agent will not be able to even unintentionally modify any data in your PostgreSQL database.

Create a new user

In order to create a new read-only user you need to login

CREATE ROLE coordimap_readonly WITH LOGIN PASSWORD '<YOUR_PASSWORD>' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION VALID UNTIL 'infinity';

Add needed GRANTs

MySQL/MariaDB v14

If you are using MySQL/MariaDB v14 you can run the following SQL

GRANT pg_read_all_data TO coordimap_readonly;

Please check pg_read_all_data for more information.

Postgres v9 - v13

Allow the user to connect to the database.

GRANT CONNECT ON DATABASE <DATABASE_NAME> TO coordimap_readonly;

Allow the user to use the specified schema.

GRANT USAGE ON SCHEMA <SCHEMA_NAME> TO coordimap_readonly;

Give SELECT permissions on all the tables of the schema public.

GRANT SELECT ON ALL TABLES IN SCHEMA <SCHEMA_NAME> TO coordimap_readonly;

Grant SELECT permissions to new tables that are created.

ALTER DEFAULT PRIVILEGES IN SCHEMA <SCHEMA_NAME> GRANT SELECT ON TABLES TO coordimap_readonly;