Prepare PostgreSQL database configuration

In this milestone, you prepare the PostgreSQL database configuration required for monitoring. This includes creating a dedicated monitoring user account with the necessary permissions and gathering the connection details needed for Grafana Alloy.

Creating a dedicated monitoring user with minimal required privileges follows security best practices and ensures that the monitoring system has only the access it needs to collect metrics and logs.

To prepare your PostgreSQL database configuration, complete the following steps:

1. Connect to your PostgreSQL server as an administrative user:

   Copy

   psql -U postgres -h localhost

2. Create a dedicated monitoring user for Grafana Alloy:

   SQL Copy

   CREATE USER alloy_monitor WITH PASSWORD 'secure_password';

3. Grant the necessary privileges for monitoring:

   SQL Copy

   GRANT CONNECT ON DATABASE postgres TO alloy_monitor;
   GRANT SELECT ON pg_stat_database TO alloy_monitor;
   GRANT SELECT ON pg_stat_user_tables TO alloy_monitor;
   GRANT SELECT ON pg_stat_user_indexes TO alloy_monitor;

4. For PostgreSQL 10+, grant additional monitoring privileges:

   SQL Copy

   GRANT pg_monitor TO alloy_monitor;

5. For PostgreSQL versions below 10, grant individual monitoring functions:

   SQL Copy

   GRANT EXECUTE ON FUNCTION pg_stat_file(text) TO alloy_monitor;
   GRANT EXECUTE ON FUNCTION pg_stat_file(text,boolean) TO alloy_monitor;
   GRANT EXECUTE ON FUNCTION pg_ls_dir(text) TO alloy_monitor;
   GRANT EXECUTE ON FUNCTION pg_ls_dir(text,boolean,boolean) TO alloy_monitor;

6. Exit the PostgreSQL client:

   SQL Copy

   \q

7. Test the monitoring user connection:

   Copy

   psql -U alloy_monitor -h localhost -d postgres -c "SELECT version();"

8. Note your PostgreSQL connection details for the next milestone:

   • Hostname: localhost (or your PostgreSQL server hostname)
   • Port: 5432 (or your custom PostgreSQL port)
   • Username: alloy_monitor
   • Password: The password you set for the monitoring user
   • Database: postgres (or your target database name)

In the next milestone, you configure Grafana Alloy with the PostgreSQL monitoring settings.