Обсуждение: No enough privileges for autovacuum worker
Hi everyone,
Every minute, I see the following error message in my server logs:
2021-03-23 13:00:07.564 MSK [60828]-[]-[]-[autovacuum worker]-[42501]-[@] ERROR: permission denied for schema dict at
character34
2021-03-23 13:00:07.564 MSK [60828]-[]-[]-[autovacuum worker]-[42501]-[@] QUERY:
SELECT array_to_string(dict.trigrams_array($1), ' ')::tsvector;
2021-03-23 13:00:07.564 MSK [60828]-[]-[]-[autovacuum worker]-[42501]-[@] CONTEXT: SQL function "trigrams_vector"
duringinlining
automatic analyze of table "fpdb.fpbackup.fp_vpn_data_2021w12"
It seems that something related to autovacuum does not have enough
privileges to do some particular work, but I have no idea to what
and/or to whom I should grant privileges to fix this problem.
log setting from postgresql.conf:
log_line_prefix = '%m [%p]-[%h]-[%a]-[%b]-[%e]-[%u@%d] '
PostgreSQL version 13.
Any ideas?
--
Best regards,
Andrey Sychev
andrey.sychev@cifrasoft.com
On 3/23/21 3:20 AM, Андрей Сычёв wrote: > Hi everyone, > > Every minute, I see the following error message in my server logs: > > 2021-03-23 13:00:07.564 MSK [60828]-[]-[]-[autovacuum worker]-[42501]-[@] ERROR: permission denied for schema dict atcharacter 34 > 2021-03-23 13:00:07.564 MSK [60828]-[]-[]-[autovacuum worker]-[42501]-[@] QUERY: > SELECT array_to_string(dict.trigrams_array($1), ' ')::tsvector; > > 2021-03-23 13:00:07.564 MSK [60828]-[]-[]-[autovacuum worker]-[42501]-[@] CONTEXT: SQL function "trigrams_vector" duringinlining > automatic analyze of table "fpdb.fpbackup.fp_vpn_data_2021w12" What is trigrams_vector() doing and does it involve table fpdb.fpbackup.fp_vpn_data_2021w12? Also what user is the function running as and does that user have permissions to schema dict? > > > It seems that something related to autovacuum does not have enough > privileges to do some particular work, but I have no idea to what > and/or to whom I should grant privileges to fix this problem. > > log setting from postgresql.conf: > log_line_prefix = '%m [%p]-[%h]-[%a]-[%b]-[%e]-[%u@%d] ' > > PostgreSQL version 13. > > > Any ideas? > -- Adrian Klaver adrian.klaver@aklaver.com
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$
;
------------------------
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".
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
Sorry, my mistake, but actually
trigrams_array is called by trigrams_vector
Definition for the function dict.trigrams_vector:
CREATE OR REPLACE FUNCTION dict.trigrams_vector(word text)
RETURNS tsvector
LANGUAGE sql
IMMUTABLE STRICT
AS $function$
SELECT array_to_string(dict.trigrams_array($1), ' ')::tsvector;
$function$
;
On 3/23/21 9:18 AM, Андрей Сычёв wrote:
> Sorry, my mistake, but actually
>
> trigrams_array is called by trigrams_vector
Yes and that is at least one of the issues:
2021-03-23 13:00:07.564 MSK [60828]-[]-[]-[autovacuum
worker]-[42501]-[(at)] ERROR: permission denied for schema dict at
character 34
2021-03-23 13:00:07.564 MSK [60828]-[]-[]-[autovacuum
worker]-[42501]-[(at)] QUERY:
SELECT array_to_string(dict.trigrams_array($1), '
')::tsvector;
So who is running below and do they have permissions on schema dict?
Not sure where table fpdb.fpbackup.fp_vpn_data_2021w12 fits in? Are you
running the function against values in the table?
>
> Definition for the function dict.trigrams_vector:
>
> CREATE OR REPLACE FUNCTION dict.trigrams_vector(word text)
> RETURNS tsvector
> LANGUAGE sql
> IMMUTABLE STRICT
> AS $function$
> SELECT array_to_string(dict.trigrams_array($1), ' ')::tsvector;
> $function$
> ;
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
I do not know who is running dict.trigrams_vector. There is no explicit call for this function. It is used implicitly in the index definition for the table fpbackup.fp_vpn_data_2021w12: CREATE INDEX fp_vpn_data_2021w12_trigrams_vector_idx ON fpbackup.fp_vpn_data_2021w12 USING gin (dict.trigrams_vector((fp_host)::text)); As I know there is no explicit call for dict.trigrams_vector by me or anyone else. From the database logs I cannot figure out what user is responsible for calling this function as user field in the log in ordinary situation should be, for example, [user@dbname], not [(at)]
On 3/23/21 9:45 AM, Андрей Сычёв wrote: > I do not know who is running dict.trigrams_vector. > > There is no explicit call for this function. > > It is used implicitly in the index definition for the table fpbackup.fp_vpn_data_2021w12: > > CREATE INDEX fp_vpn_data_2021w12_trigrams_vector_idx ON fpbackup.fp_vpn_data_2021w12 USING gin (dict.trigrams_vector((fp_host)::text)); Aah, I missed that. > > As I know there is no explicit call for dict.trigrams_vector by me or > anyone else. The error is happening when the auto analyze opens the index on the table, though I will have to admit that I don't know why? What happens if you do a manual ANALYZE on the table? > > From the database logs I cannot figure out what user is responsible for > calling this function as user field in the log in ordinary situation should be, for example, [user@dbname], > not [(at)] > -- Adrian Klaver adrian.klaver@aklaver.com
I finally figured out how to tackle the problem: 1. When I manually execute ANALYZE on the table the error remains: [34938]-[192.168.67.81]-[DBeaver 7.3.2 - SQLEditor <Script-2.sql>]-[client backend]-[42501]-[postgres@fpdb] CONTEXT: SQLfunction "trigrams_vector" during inlining [34938]-[192.168.67.81]-[DBeaver 7.3.2 - SQLEditor <Script-2.sql>]-[client backend]-[42501]-[postgres@fpdb] STATEMENT: ANALYZEfpbackup.fp_vpn_data_2021w12 [31203]-[]-[]-[autovacuum worker]-[42501]-[@] ERROR: permission denied for schema dict at character 34 [31203]-[]-[]-[autovacuum worker]-[42501]-[@] QUERY: _to_string(dict.trigrams_array($1), ' ')::tsvector; This happens despite I run this query as superuser. But the hint from you allows me to manually reproduce error. 2. As I wrote before the owner of the table fpbackup.fp_vpn_data_2021w12 is "fpbkwriter" so I run the following query: GRANT USAGE ON SCHEMA dict TO fpbkwriter; and after that the problem has been solved. 3. The following queries do not affect the problem: GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA dict TO fpbkwriter; REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA dict FROM fpbkwriter; -- Adrian, thank you very much for support. Your hints were very useful.
On 3/24/21 1:16 AM, Андрей Сычёв wrote: > I finally figured out how to tackle the problem: > > 1. When I manually execute ANALYZE on the table the error remains: > > [34938]-[192.168.67.81]-[DBeaver 7.3.2 - SQLEditor <Script-2.sql>]-[client backend]-[42501]-[postgres@fpdb] CONTEXT: SQLfunction "trigrams_vector" during inlining > [34938]-[192.168.67.81]-[DBeaver 7.3.2 - SQLEditor <Script-2.sql>]-[client backend]-[42501]-[postgres@fpdb] STATEMENT: ANALYZE fpbackup.fp_vpn_data_2021w12 > [31203]-[]-[]-[autovacuum worker]-[42501]-[@] ERROR: permission denied for schema dict at character 34 > [31203]-[]-[]-[autovacuum worker]-[42501]-[@] QUERY: > _to_string(dict.trigrams_array($1), ' ')::tsvector; > > This happens despite I run this query as superuser. But the hint from you > allows me to manually reproduce error. > > 2. As I wrote before the owner of the table > fpbackup.fp_vpn_data_2021w12 is "fpbkwriter" > so I run the following query: > > GRANT USAGE > ON SCHEMA > dict > TO fpbkwriter; > > and after that the problem has been solved. Hmm. I wonder why the error did not occur when the index was created or used? > > 3. The following queries do not affect the problem: > > GRANT EXECUTE > ON ALL FUNCTIONS IN SCHEMA > dict > TO fpbkwriter; > > REVOKE EXECUTE > ON ALL FUNCTIONS IN SCHEMA > dict > FROM fpbkwriter; > > -- > > Adrian, thank you very much for support. > > Your hints were very useful. > -- Adrian Klaver adrian.klaver@aklaver.com
Because in trigger where table fpbackup.fp_vpn_data_2021w12 has been created there are several DDL commands: EXECUTE 'CREATE TABLE "' || fp_schema_name || '"."' || fp_table_name || '" (LIKE '|| fp_parent_table_name || ' INCLUDINGDEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES) WITHOUT OIDS TABLESPACE ' || fp_tablespace; EXECUTE 'ALTER TABLE "' || fp_schema_name || '"."' || fp_table_name || '" OWNER TO fpbkwriter'; The trigger was called by user "worker" that already had permission on USAGE of schema dict, so table creation was successful. But after that the ownership of the table was moving to "fpbkwriter" that had no permission on USAGE of schema dict, thus the error occured. In reality, there was no user "fpbkwriter" at the beginning. Only user "worker" was created. And when the user "fpbkwriter" was created, he was not granted by enough privileges mistakenly.
On 3/25/21 1:25 AM, Андрей Сычёв wrote: > Because in trigger where table fpbackup.fp_vpn_data_2021w12 has > been created there are several DDL commands: > > EXECUTE 'CREATE TABLE "' || fp_schema_name || '"."' || fp_table_name || '" (LIKE '|| fp_parent_table_name || ' INCLUDINGDEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES) WITHOUT OIDS TABLESPACE ' || fp_tablespace; > > EXECUTE 'ALTER TABLE "' || fp_schema_name || '"."' || fp_table_name || '" OWNER TO fpbkwriter'; > > The trigger was called by user "worker" that already had permission on > USAGE of schema dict, so table creation was successful. But after that > the ownership of the table was moving to "fpbkwriter" that had no permission on > USAGE of schema dict, thus the error occured. > > In reality, there was no user "fpbkwriter" at the beginning. Only user > "worker" was created. And when the user "fpbkwriter" was created, he was > not granted by enough privileges mistakenly. > Alright that I understand. Still after the ALTER TABLE ... OWNER TO fpbkwriter, the index was running as fpbkwriter yet there where no errors? Did that mean the table was never queried or the index never used? -- Adrian Klaver adrian.klaver@aklaver.com