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_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).

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.

1. pg_stat_activity
2. 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.

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

.

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.

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.

Download PDF

by Avinash Vallarapu via Percona Database Performance Blog