A Function that will allow unprivileged users to list session using any database schema.

Summary

Sometimes, developers need to know which sessions are currently using the database schema they’re working on. This function provides an easy way to list those sessions efficiently.

Function’s code

Use this code to create the function:

CREATE OR REPLACE FUNCTION public.list_sessions_using_schema(p_schema varchar)
    RETURNS TABLE (
        database_name varchar,
        database_user varchar,
        client_address varchar,
        return_application_name varchar,
        running_query text)
    SECURITY DEFINER
    LANGUAGE 'plpgsql'
    AS $$
BEGIN
    RETURN query
    SELECT
        datname::varchar AS database_name,
        usename::varchar AS database_user,
        client_addr::varchar AS client_address,
        application_name::varchar AS return_application_name,
        query::text AS running_query
    FROM
        pg_stat_activity
    WHERE
        datname <> 'postgres'
        AND (query ILIKE '%' || p_schema || '%'
            OR usename IN (
                SELECT
                    r.rolname
                FROM
                    pg_db_role_setting rs
                LEFT JOIN pg_roles r ON r.oid = rs.setrole
            WHERE
                rs.setconfig::text LIKE '%' || p_schema || '%'))
        AND pid <> pg_backend_pid();
END;
$$;

Function description

This function will query pg_stat_activity and filter the results with the desired schema received as the parameter: p_schema
The WHERE clause will look through QUERY field on pg_stat_activity but also the default schema for the any user in the cluser matching p_schema.

You have to consider that if any user has changed the default schema via set search_path clause, that won’t be shown here (which is also a caveat).

Function Security considerations

This code place the function on public schema making it available for almost every user.
But we’re defining SECURITY DEFINER! So the function will run as if the user that has created it had called it!
The reason for that is not grant anything over pg_stat_activity, pg_db_role_settings…

Function Description

This function queries pg_stat_activity and filters the results based on the desired schema specified by the parameter: p_schema.
The WHERE clause checks both the query field in pg_stat_activity and the default schema for any user in the cluster that matches p_schema.

It’s important to note that if any user has modified their default schema using the SET search_path clause, this change will not be reflected in the results. This is another caveat to keep in mind.

Running

Example, sessions using the famous datadog schema:

select * from public.list_sessions_using_schema('datadog') ;

Caveats

This function has a significant caveat regarding security.
At OneClickDBA, we specialize in helping you identify and mitigate such vulnerabilities, ensuring your systems remain secure.