Creating a Read-Only PostgreSQL User for Coordimap Agent

When configuring the Coordimap agent to connect to your PostgreSQL database (as described in the PostgreSQL Configuration guide), it's crucial for security to use a database user with the minimum necessary privileges. This guide details how to create a dedicated read-only PostgreSQL user, ensuring the Coordimap agent can only read schema information and cannot accidentally modify your data.

Using a read-only user is a security best practice that limits the potential impact if the agent's credentials were ever compromised.

Step 1: Create the User Role

First, connect to your PostgreSQL database using a user with sufficient privileges (like the default postgres user or another superuser). Then, execute the following SQL command to create a new role specifically for Coordimap. We'll name it coordimap_readonly in this example, but you can choose a different name.

-- Create a new role named 'coordimap_readonly' with a secure password
CREATE ROLE coordimap_readonly WITH
  LOGIN
  PASSWORD '<YOUR_SECURE_PASSWORD>' -- Replace with a strong password!
  NOSUPERUSER
  INHERIT
  NOCREATEDB
  NOCREATEROLE
  NOREPLICATION
VALID UNTIL 'infinity';

Step 2: Grant Necessary Permissions

The permissions required depend on your PostgreSQL version.

Option A: PostgreSQL v14 and Newer

PostgreSQL v14 introduced predefined roles that simplify granting read-only access across the entire database. Run the following command:

GRANT pg_read_all_data TO coordimap_readonly;

This single command grants the necessary CONNECT and SELECT privileges for the coordimap_readonly user to read schema information from all tables within the database you are currently connected to. See the official PostgreSQL documentation on predefined roles for more details.

Please check pg_read_all_data for more information.

Option B: PostgreSQL v9 through v13

For older PostgreSQL versions, you need to grant permissions more granularly, typically on a per-schema basis.

Allow Connection: Grant the user permission to connect to the target database.

GRANT CONNECT ON DATABASE <DATABASE_NAME> TO coordimap_readonly;

Allow Schema Usage: Grant the user permission to access objects within a specific schema.

GRANT USAGE ON SCHEMA <SCHEMA_NAME> TO coordimap_readonly;

Grant Table Read Access: Grant SELECT permission on all existing tables within that schema.

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

Grant Access for Future Tables (Optional but Recommended): Ensure the user automatically gets SELECT permissions on any new tables created in the future within that schema.

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

Conclusion

You have now created a dedicated read-only user (coordimap_readonly) suitable for the Coordimap agent. Use this username and the password you set when configuring the PostgreSQL data source in the agent's YAML file. This ensures the agent operates securely with minimal required privileges.