How do I grant execute permissions to select client_addr from pg_stat_replication; for a regular PostgreSQL user? How do I grant execute permissions to select client_addr from pg_stat_replication; for a regular PostgreSQL user?

Always after creating a function, give execute rights

grant execute on function client_addr_pg_stat_replication() to user;


Make the function

CREATE OR REPLACE FUNCTION client_addr_pg_stat_replication() RETURNS text AS $$
BEGIN
  PERFORM client_addr from pg_stat_replication;
END;
$$ LANGUAGE plpgsql security definer;


Result:

select * from client_addr_pg_stat_replication();
ERROR:  control reached end of function without RETURN
CONTEXT:  PL/pgSQL function client_addr_pg_stat_replication()


Make the function

CREATE OR REPLACE FUNCTION client_addr_pg_stat_replication() RETURNS SETOF RECORD AS $$
BEGIN
  select client_addr from pg_stat_replication;
END;
$$ LANGUAGE plpgsql security definer;


Result:

select * from client_addr_pg_stat_replication();
ERROR: a column definition list is required for functions returning "record"
LINE 1: select * from client_addr_pg_stat_replication();


Make the function

CREATE OR REPLACE FUNCTION client_addr_pg_stat_replication(TEXT)
    RETURNS setof pg_stat_replication
AS
$BODY$
BEGIN
    RETURN QUERY SELECT client_addr FROM pg_stat_replication;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;


Result:

select * from client_addr_pg_stat_replication(client_addr);
ERROR: column "client_addr" does not exist
LINE 1: select * from client_addr_pg_stat_replication(client_addr);


Make the function

CREATE OR REPLACE FUNCTION client_addr_pg_stat_replication() RETURNS TABLE(client_addr varchar) AS
$$
BEGIN
  SELECT client_addr FROM pg_stat_replication;
END;
$$ LANGUAGE plpgsql security definer;


Result:

select * from client_addr_pg_stat_replication();
ERROR:  column reference "client_addr" is ambiguous
LINE 1: SELECT client_addr FROM pg_stat_replication
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  SELECT client_addr FROM pg_stat_replication
CONTEXT:  PL/pgSQL function client_addr_pg_stat_replication() line 3 at SQL statement


Make the function

CREATE OR REPLACE FUNCTION public.client_addr_pg_stat_replication(varchar)
    RETURNS setof pg_stat_replication
AS
$BODY$
BEGIN
    RETURN QUERY SELECT * FROM pg_stat_replication;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;


Result:

select * from client_addr_pg_stat_replication();
ERROR:  function client_addr_pg_stat_replication() does not exist
LINE 1: select * from client_addr_pg_stat_replication();
                      ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
redcheckpg=&gt; select * from client_addr_pg_stat_replication(client_addr);
ERROR:  column "client_addr" does not exist
LINE 1: select * from client_addr_pg_stat_replication(client_addr);


Thank you
CREATE OR REPLACE FUNCTION client_addr_pg_stat_replication() RETURNS text AS $$
DECLARE ip text;
BEGIN 
select client_addr INTO ip from pg_stat_replication;
RETURN ip;
END;
$$ LANGUAGE plpgsql security definer;


OR

CREATE OR REPLACE FUNCTION client_addr_pg_stat_replication()
RETURNS text AS $x$
BEGIN 
RETURN client_addr from pg_stat_replication;
END;
$x$ LANGUAGE plpgsql security definer;