I try Get few ip postgresql slave from pg_stat_replication using non-superuser

CREATE OR replace FUNCTION client_addr_pg_stat_replication3 
  () returns setof RECORD 
    rec RECORD; 
    SELECT client_addr 
    FROM   pg_stat_replication 
    INTO   rec; 

  $x$ LANGUAGE plpgsql security definer;

GRANT EXECUTE ON FUNCTION client_addr_pg_stat_replication3(SETOF record) TO non-superuser;

select * from client_addr_pg_stat_replication3() as x(a int, b int);
ERROR:  function client_addr_pg_stat_replication3() does not exist
LINE 1: select * from client_addr_pg_stat_replication3() as x(a int,...
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Second function

CREATE OR replace FUNCTION client_addr_pg_stat_replication4 
  () returns TABLE (client_addr cidr) 
  RETURN query 
  SELECT client_addr 
  FROM   pg_stat_replication; 

END $func$ LANGUAGE plpgsql security definer;

GRANT EXECUTE ON FUNCTION client_addr_pg_stat_replication4() TO non-superuser;

select * from client_addr_pg_stat_replication4();
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_replication4() line 1 at RETURN QUERY

You need a view not just function, e.g.

CREATE OR replace FUNCTION public.pg_stat_repl() returns setof 
    RETURN query 
       SELECT * 
       FROM pg_catalog.pg_stat_replication); 
  end$$ LANGUAGE plpgsql security definer; 

CREATE VIEW public.pg_stat_repl AS SELECT * FROM public.pg_stat_repl(); 

GRANT SELECT ON public.pg_stat_repl TO non-superuser;

Then you can as non-superuser

SELECT * FROM public.pg_stat_repl;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.