Using Security Definer to Monitor PostgreSQL 9.6 or Earlier Using Percona Monitoring and Management
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_monitorrole 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).
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
SUPERUSERrole 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 DEFINERto safely grant access to selected statistics views. Let us first understand the difference between a
security invokerand a
security definerin PostgreSQL functions.
Security Invoker vs Security Definer in PostgreSQL
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.
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.
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.
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 DEFINERso that the user calling this function can use the superuser role to access the data from the views:
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_statementshas been created in the
publicschema. If you have specified another schema while creating this extension, please specify the appropriate schema prefix instead of
Step 3: Create views with the same name as the original views in the
pmmschema. Then, grant the SELECT access on the newly created view to the monitoring user.
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
pmmschema, that is calling the function
SUPERUSERprivileges but not the
pg_catalog.pg_stat_activityview. This is because we have set the
pmm, pg_catalogin Step 1. If the view it is trying to access is not present in the
pmmschema, it looks for that view in the
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.
by Avinash Vallarapu via Percona Database Performance Blog