HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Postgres permission for EXPLAIN

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
explainpermissionforpostgres

Problem

I would like to give user a permission to perform EXPLAIN but not to execute the SELECT itself. Is this doable in PostgreSQL?

Solution

Create a function like the one below;

CREATE OR REPLACE FUNCTION public.explain_this (
      l_query text,
      out explain text
    )
    RETURNS SETOF text AS
    $body$
    BEGIN
      RETURN QUERY EXECUTE 'explain (ANALYZE FALSE) ' || l_query;
    END;
    $body$
    LANGUAGE 'plpgsql'
    VOLATILE
    RETURNS NULL ON NULL INPUT
    SECURITY DEFINER
    COST 100 ROWS 1000;


And then arrange privileges;

ALTER FUNCTION public.explain_this(l_query text, out explain text)
    OWNER TO some_user_with_select_only_privileges;

  GRANT EXECUTE
    ON FUNCTION public.explain_this(l_query text, out explain text) TO user_to_execute;

Code Snippets

CREATE OR REPLACE FUNCTION public.explain_this (
      l_query text,
      out explain text
    )
    RETURNS SETOF text AS
    $body$
    BEGIN
      RETURN QUERY EXECUTE 'explain (ANALYZE FALSE) ' || l_query;
    END;
    $body$
    LANGUAGE 'plpgsql'
    VOLATILE
    RETURNS NULL ON NULL INPUT
    SECURITY DEFINER
    COST 100 ROWS 1000;
ALTER FUNCTION public.explain_this(l_query text, out explain text)
    OWNER TO some_user_with_select_only_privileges;

  GRANT EXECUTE
    ON FUNCTION public.explain_this(l_query text, out explain text) TO user_to_execute;

Context

StackExchange Database Administrators Q#143470, answer score: 2

Revisions (0)

No revisions yet.