Postgres materialized view hides some data

Question

First of all, I can not recreate this example in SQL fiddle, I got some error when I try select from view.

Problem:

I have a materialized view which fetches function parameters from my schema, based on information_schema. When I create it, it works just fine. When I refresh it, it works just fine. When I assign it to some role, and then refresh it - it loses about 75% of its contents, and refreshing does not work. Only thing that works is dropping and re creating the whole view.

Examples:

All examples was executed as superuser. Lets say that I have role:

 CREATE ROLE table_owner NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

And I have materialized view like this:

CREATE MATERIALIZED VIEW function_def AS 
    SELECT 
        regexp_replace(r.specific_name::text, '^(.*)_[0-9]+$'::text, '\1'::text) AS function_name,
        r.data_type AS output_type,
        r.type_udt_name AS output_udt_name,
        p.ordinal_position,
        p.parameter_name,
        p.data_type,
        p.udt_schema,
        regexp_replace(p.udt_name::text, '^_'::text, ''::text) AS udt_name
    FROM information_schema.routines r
    LEFT JOIN information_schema.parameters p ON p.specific_name::text = r.specific_name::text
    WHERE 1 = 1 AND p.specific_schema::text = 'mySchema'::text
    ORDER BY regexp_replace(r.specific_name::text, '^(.*)_[0-9]+$'::text, '\1'::text), p.ordinal_position
WITH DATA;
CREATE INDEX i_function_def_function_name ON function_def(function_name);

And lets say that in this point statement:

SElECT count(*) FROM function_def

returns 231 rows, which is correct number. Then I assign the ownership of view to some role:

ALTER TABLE function_def OWNER TO table_owner;

and select still returns 231 rows, which is correct number.

SElECT count(*) FROM function_def;

but when I refresh view like this:

REFRESH MATERIALIZED VIEW function_def WITH DATA;

and then:

SElECT count(*) FROM function_def;

the returned number of rows is constant 54, which is not correct.

I am quite puzzled here and would appreciate some help, or hint. Is this a postgres bug, or am I doing something wrong?

EDIT - solution:

As stated by Klin it is in fact a privilege issue! Because all my functions are owned by function_owner, this code has done the trick, and now everything is fine:

ALTER TABLE function_def OWNER TO function_owner;
GRANT SELECT ON TABLE function_def TO GROUP table_owner;

Answer 1

REFRESH MATERIALIZED VIEW is executed with the privileges of the view owner, i.e. table_owner in this case. The user have no access to some functions and therefore he does not see some records in information_schema.routines.

You can check what functions are not accessible by table_owner by executing this query as superuser:

SELECT 
    regexp_replace(r.specific_name::text, '^(.*)_[0-9]+$'::text, '\1'::text) AS function_name,
    r.data_type AS output_type,
    r.type_udt_name AS output_udt_name,
    p.ordinal_position,
    p.parameter_name,
    p.data_type,
    p.udt_schema,
    regexp_replace(p.udt_name::text, '^_'::text, ''::text) AS udt_name
FROM information_schema.routines r
LEFT JOIN information_schema.parameters p ON p.specific_name::text = r.specific_name::text
WHERE 1 = 1 AND p.specific_schema::text = 'mySchema'::text

EXCEPT

SELECT * FROM function_def;