patternsqlMinor
Postgres permission for EXPLAIN
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;
And then arrange privileges;
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.