Re: No enough privileges for autovacuum worker

On 3/23/21 9:07 AM, Андрей Сычёв wrote:
> 
> Definition for the table fpbackup.fp_vpn_data_2021w12:
> 
> CREATE TABLE fpbackup.fp_vpn_data_2021w12 (
>          id int8 NOT NULL DEFAULT nextval('fp_vpn_data_id_seq'::regclass),
>          fp_date_start timestamp NOT NULL,
>          fp_date_end timestamp NOT NULL,
>          ....
>          fp_host varchar NOT NULL,
>          ....
>          CONSTRAINT c_d_fp_vpn_data_2021w12 CHECK (((fp_date_start >= '2021-03-19 00:00:00'::timestamp without time
zone)AND (fp_date_start < '2021-03-26 00:00:00'::timestamp without time zone))),
 
>          CONSTRAINT fp_vpn_data_2021w12_pkey PRIMARY KEY (id)
> )
> INHERITS (public.fp_vpn_data)
> TABLESPACE myts4
> ;
> CREATE INDEX fp_vpn_data_2021w12_fp_host_idx ON fpbackup.fp_vpn_data_2021w12 USING btree (fp_host);
> CREATE INDEX fp_vpn_data_2021w12_trigrams_vector_idx ON fpbackup.fp_vpn_data_2021w12 USING gin
(dict.trigrams_vector((fp_host)::text));
> 
> ------------------------
> 
> Definition for the function dict.trigrams_array:
> 
> CREATE OR REPLACE FUNCTION dict.trigrams_array(word text)
>   RETURNS text[]
>   LANGUAGE plpgsql
>   IMMUTABLE STRICT
> AS $function$
>          DECLARE
>                  result text[];
>          BEGIN
>                  FOR i IN 1 .. length(word) - 2 LOOP
>                          result := result || quote_literal(substr(lower(word), i, 3));
>                  END LOOP;
> 
>                  RETURN result;
>          END;
> $function$
> ;
> 
> ------------------------

The function that is throwing the error is trigrams_vector(). The above 
is called in trigrams_vector, though that is not happening due to 
permissions error. So we need information on trigrams_vector().

> 
> I  do not know exactly what user the function running as is because it
> is implicit call.
> 
> I  know that records in table the dict.trigrams_array are inserted by user
> "worker".
> 
> Owner of the table fpbackup.fp_vpn_data_2021w12 is user "fpbkwriter".
> 
> user "worker" and "fpbkwriter" have the following permissions:
> 
> GRANT USAGE
> ON SCHEMA
> dict
> TO worker;
> 
> GRANT EXECUTE
> ON ALL FUNCTIONS IN SCHEMA
> dict
> TO worker;
> 
> GRANT fpbkwriter TO worker GRANTED BY postgres;
> 
> GRANT USAGE, CREATE
> ON SCHEMA
> fpbackup
> TO fpbkwriter;
> 
> The  user  "fpbkwriter" does not have any privileges related to schema
> "dict".
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com