chemtech
@chemtech
Линуксойд, DevOps

Как выдать права на выполнение select client_addr from pg_stat_replication; для обычного юзера PostgreSQL?

Всегда после создания функции выдаю права на выполнение
grant execute on function client_addr_pg_stat_replication() to user;


Делаю функцию
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;


Результат:
select * from client_addr_pg_stat_replication();
ERROR:  control reached end of function without RETURN
CONTEXT:  PL/pgSQL function client_addr_pg_stat_replication()


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;


Результат:
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();

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;


Результат:
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);

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;


Результат:
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


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;


Результат:
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=> 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);
  • Вопрос задан
  • 189 просмотров
Решения вопроса 1
chemtech
@chemtech Автор вопроса
Линуксойд, DevOps
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;


Или
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;
Ответ написан
Комментировать
Пригласить эксперта
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Похожие вопросы