I have previously written a blog post on the detailed steps involved in enabling PostgreSQL monitoring using PMM. In that post, you could see me talking about the role: pg_monitor that can be granted to monitoring users. The pg_monitor role restricts a monitoring user from accessing user data but only grants access to statistic views needed for monitoring. The following are the simple steps to create a monitoring user for using Percona Monitoring and Management (PMM).
1 2 | CREATE USER pmm_user WITH ENCRYPTED PASSWORD 'secret'; GRANT pg_monitor to pmm_user; |
However, this feature is only available from PostgreSQL 10. So, let’s discuss how to deal with the previous releases such as PostgreSQL 9.6 or earlier.
One of the easiest methods is to grant SUPERUSER role to the monitoring user. But, granting the SUPERUSER access may not work in all the environments as it has the privileges to access, modify, and alter the database objects. For that reason, we could use SECURITY DEFINER to safely grant access to selected statistics views. Let us first understand the difference between a security invoker and a security definer in PostgreSQL functions.
Security Invoker vs Security Definer in PostgreSQL
Security Invoker
When you execute a function in PostgreSQL, it is executed using the privileges of the user calling it. So, if the calling user does not have access to select a specific table, then, the SQL statements on that table may fail, so the execution of the function fails.
Security Definer
When you execute a function in PostgreSQL using SECURITY DEFINER, it is executed by the privileges of the user who created it. Even if the calling user does not have access to the database objects being queried in the function, the function execution succeeds when the user who created the function has the required privileges on those database objects.
Statistic views accessed by PMM that need access using a security definer:
To enable PostgreSQL monitoring using PMM, you should be granting access to some of the views being accessed by PMM as of today.
- pg_stat_activity
- pg_stat_statements
We shall now see the steps involved in creating a monitoring user ( pmm_user) who should be given indirect access to the above-mentioned views.
Step 1: Create the monitoring user and the schema in which the functions and views can be created.
1 2 3 | CREATE USER pmm_user WITH ENCRYPTED PASSWORD 'secret'; CREATE SCHEMA pmm AUTHORIZATION pmm_user; ALTER USER pmm_user SET SEARCH_PATH TO pmm, pg_catalog; |
Step 2: Create the functions to access the views being accessed by PMM, as a SUPERUSER. Make sure to specify SECURITY DEFINER so that the user calling this function can use the superuser role to access the data from the views: pg_stat_activity and pg_stat_statements.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | CREATE OR REPLACE FUNCTION pmm.get_pg_stat_activity() RETURNS SETOF pg_stat_activity AS $$ SELECT * FROM pg_catalog.pg_stat_activity; $$ LANGUAGE sql VOLATILE SECURITY DEFINER; GRANT EXECUTE ON FUNCTION pmm.get_pg_stat_activity() TO pmm_user; -- The Following function needs to be created when you need QAN (Query Analytics) with PMM. CREATE OR REPLACE FUNCTION pmm.get_pg_stat_statements() RETURNS SETOF pg_stat_statements AS $$ SELECT * FROM public.pg_stat_statements; $$ LANGUAGE sql VOLATILE SECURITY DEFINER; GRANT EXECUTE ON FUNCTION pmm.get_pg_stat_statements() TO pmm_user; |
While creating the last function pmm.get_pg_stat_statements(), it assumes that the extension pg_stat_statements has been created in the public schema. If you have specified another schema while creating this extension, please specify the appropriate schema prefix instead of public.
Step 3: Create views with the same name as the original views in the pmm schema. Then, grant the SELECT access on the newly created view to the monitoring user.
1 2 3 4 5 6 7 8 | CREATE VIEW pmm.pg_stat_activity AS SELECT * FROM pmm.get_pg_stat_activity(); CREATE VIEW pmm.pg_stat_statements AS SELECT * FROM pmm.get_pg_stat_statements(); GRANT SELECT ON pmm.pg_stat_activity TO pmm_user; GRANT SELECT ON pmm.pg_stat_statements TO pmm_user; |
So, when you connect to postgres using the monitoring user pmm_user, you would automatically query the view pg_stat_activity in the pmm schema, that is calling the function pmm.get_pg_stat_activity() using SUPERUSER privileges but not the pg_catalog.pg_stat_activity view. This is because we have set the search_path of the pmm_user to pmm, pg_catalog in Step 1. If the view it is trying to access is not present in the pmm schema, it looks for that view in the pg_catalog schema.
Your monitoring user for Percona Monitoring and Management is successfully created once the above three steps are completed.
Our white paper “Why Choose PostgreSQL?” looks at the features and benefits of PostgreSQL and presents some practical usage examples. We also examine how PostgreSQL can be useful for companies looking to migrate from Oracle.
