Using Security Definer to Monitor PostgreSQL 9.6 or Earlier Using Percona Monitoring and Management

security definer postgresql

security definer postgresqlI 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

Comments