How to Grant Execute Permissions for pg_stat_replication in PostgreSQL

functionspostgresql

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=> 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

Best Answer

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;

Related Solutions

PostgreSQL – Concatenating SETOF Type or SETOF Record

The approach you're using is unnecessarily complex - and very inefficient. Instead of the first function use:

create or replace function compute_pair_id_value(id bigint, value integer)
    returns setof pair_id_value
as $$
SELECT $1, generate_series(0,$2);
$$                          
language sql;

or better, get rid of it entirely and write the whole operation like this:

-- Sample data creation:
CREATE TABLE my_obj(id bigint, obj_value integer);
insert into my_obj(id,obj_value) VALUES (1712437,2),(17000,5);

-- and the query:
SELECT id, generate_series(0,obj_value) FROM my_obj;

Resulting in:

regress=> SELECT id, generate_series(0,obj_value) FROM my_obj;
   id    | generate_series 
---------+-----------------
 1712437 |               0
 1712437 |               1
 1712437 |               2
   17000 |               0
   17000 |               1
   17000 |               2
   17000 |               3
   17000 |               4
   17000 |               5
(9 rows)

This exploits PostgreSQL's behaviour with set-returning functions called in the SELECT list. Once PostgreSQL 9.3 comes out it can be replaced with a standards-compliant LATERAL query.

Since it turns out your question was a simplified version of the real problem, let's tackle that. I'll work with the simplified compute_pair_id_value above to avoid the hassle of plpython3. Here's how to do what you want:

SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;

Result:

regress=> SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;
   id    | value 
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

but again, be warned that compute_pair_id_value will be called more than once. This is a limitation of PostgreSQL's query executor that can be avoided in 9.3 with LATERAL support, but as far as I know you're stuck with it in 9.2 and below. Observe:

create or replace function compute_pair_id_value(id bigint, value integer)
    returns setof pair_id_value
as $$
BEGIN
  RAISE NOTICE 'compute_pair_id_value(%,%)',id,value;
  RETURN QUERY SELECT $1, generate_series(0,$2);
END;
$$             
language plpgsql;

output:

regress=> SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(17000,5)
NOTICE:  compute_pair_id_value(17000,5)
   id    | value 
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

See how compute_pair_id_value is called once per output column?

There is a workaround: Another layer of subquery to unpack the composite type result. See:

regress=> SELECT (val).* FROM (SELECT compute_pair_id_value(id,obj_value) FROM my_obj) x(val);
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(17000,5)
   id    | value 
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

You can use the same technique in your code if you really must LOOP over the results (it's slow to do that, so avoid it if you can).

Postgresql – Error: set_valued function called in context that cannot accept a set. What is it about

The error message isn't very helpful:

regress=> SELECT * FROM  compute_all_pair_by_craig(100);
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM  compute_all_pair_by_craig(100);

but if you rephrase the query to call it as a proper set-returning function you'll see the real problem:

regress=> SELECT * FROM compute_all_pair_by_craig(100);
ERROR:  a column definition list is required for functions returning "record"
LINE 1: SELECT * FROM compute_all_pair_by_craig(100);

If you're using SETOF RECORD without an OUT parameter list you must specify the results in the calling statement, eg:

regress=> SELECT * FROM compute_all_pair_by_craig(100) theresult(a integer, b integer);

However, it's much better to use RETURNS TABLE or OUT parameters. With the former syntax your function would be:

create or replace function compute_all_pair_by_craig(id_obj bigint)
    returns table(a integer, b integer) as $$
begin
    return query select o.id, generate_series(0,o.value) from m_obj as o;     
end;
$$ language plpgsql;

This is callable in SELECT-list context and can be used without creating a type explicitly or specifying the result structure at the call site.

As for the second half of the question, what's happening is that the 1st case specifies two separate columns in a SELECT-list, wheras the second returns a single composite. It's actually not to do with how you're returning the result, but how you're invoking the function. If we create the sample function:

CREATE OR REPLACE FUNCTION twocols() RETURNS TABLE(a integer, b integer) 
AS $$ SELECT x, x FROM generate_series(1,5) x; $$ LANGUAGE sql;

You'll see the difference in the two ways to call a set-returning function - in the SELECT list, a PostgreSQL specific non-standard extension with quirky behaviour:

regress=> SELECT twocols();
 twocols 
---------
 (1,1)
 (2,2)
 (3,3)
 (4,4)
 (5,5)
(5 rows)

or as a table in the more standard way:

regress=> SELECT * FROM twocols();
 a | b 
---+---
 1 | 1
 2 | 2
 3 | 3
 4 | 4
 5 | 5
(5 rows)

Best Answer

Related Solutions

PostgreSQL – Concatenating SETOF Type or SETOF Record

Postgresql – Error: set_valued function called in context that cannot accept a set. What is it about

Related Question