ConfigurationMySQL/MariaDB
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';Assumption
The following SQL statements assume that the user name is coordimap_readonly.
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
Substitute the following variables in the coming SQL statements
- DATABASE_NAME: use the real name of the database
- SCHEMA_NAME: use the specific schema name
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;