Home > mailing lists

Phantom Permissions Error - Mailing list pgsql-general

From	Upesh Desai
Subject	Phantom Permissions Error
Date	November 14, 2022 19:30:30
Msg-id	DBAPR02MB6264BC2A78D6D1544E918405D9059@DBAPR02MB6264.eurprd02.prod.outlook.com Whole thread Raw
List	pgsql-general

Tree view

What type of bug is this?

Unexpected error

What subsystems and features are affected?

Other

What happened?

We are running into a weird permissions error that possibly seems to be related to Postgres, but it’s not clear what exactly is causing the issue. We run a script which grants the permissions below to a user, and for some reason, there are a many extraneous tables or relations that we have not created. When GRANT commands are run, does timescale do any extra permission changes? We only have 10 tables created in the public schema.

PostgreSQL version used

What operating system did you use?

docker image: timescale-ha-base:pg14.5-ts2.7.2-p0-r0

What installation method did you use?

Docker, Kubernetes

What platform did you run on?

On prem/Self-hosted

Relevant log output and stack trace

postgres@timescale-0:~$ psql -q -v ON_ERROR_STOP=1 -d mydatabase <<-EOF

set client_min_messages=DEBUG;

REVOKE ALL ON DATABASE mydatabase FROM PUBLIC;

GRANT CONNECT ON DATABASE mydatabase TO sinkd;

GRANT USAGE ON SCHEMA public TO sinkd;

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO sinkd; < ---------------- THIS LINE CAUSES THE ERROR

> EOF

DEBUG: relation "public." does not exist

DEBUG: relation "public.@" does not exist

DEBUG: relation "public." does not exist

DEBUG: relation "public.8_19?]?" does not exist

DEBUG: relation "public." does not exist

DEBUG: relation "public.@" does not exist

DEBUG: relation "public." does not exist

DEBUG: relation "public.8_19?]?" does not exist

DEBUG: relation "public." does not exist

DEBUG: rehashing catalog cache id 50 for pg_class; 257 tups, 128 buckets

DEBUG: rehashing catalog cache id 51 for pg_class; 257 tups, 128 buckets

DEBUG: rehashing catalog cache id 50 for pg_class; 513 tups, 256 buckets

DEBUG: rehashing catalog cache id 51 for pg_class; 513 tups, 256 buckets

DEBUG: rehashing catalog cache id 50 for pg_class; 1025 tups, 512 buckets

DEBUG: rehashing catalog cache id 51 for pg_class; 1025 tups, 512 buckets

DEBUG: rehashing catalog cache id 50 for pg_class; 2049 tups, 1024 buckets

DEBUG: rehashing catalog cache id 51 for pg_class; 2049 tups, 1024 buckets

DEBUG: relation "public." does not exist

DEBUG: rehashing catalog cache id 50 for pg_class; 4097 tups, 2048 buckets

DEBUG: rehashing catalog cache id 51 for pg_class; 4097 tups, 2048 buckets

ERROR: relation "public." does not exist

The resulting error is also not consistent, this last two times I ran the job, the error that was shown was:

ERROR: relation "public.+2022-09-17T03:00:00Z" does not exist

ERROR: relation "public.k__compressed_hypertable_20_tid__ts" does not exist

One other point to consider is the command succeeds if run interactively from psql, but not if the command comes from stdin exactly like written above.

Adding the verbose setting shows this:

postgres@timescale-0:~$ psql -q -v ON_ERROR_STOP=1 -d obcerv <<-EOF
-- sinkd
\set VERBOSITY verbose
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO sinkd;
> EOF
ERROR: 42P01: relation "public." does not exist
LOCATION: RangeVarGetRelidExtended, namespace.c:428

We initially thought this error may be related to the TimescaleDB extension, so the bug ticket for that is here: https://github.com/timescale/timescaledb/issues/4936

However, they are saying this is not the case so I wanted to ping this group.

Thanks,
Upesh

Upesh Desai

Senior Software Developer

udesai@itrsgroup.com

www.itrsgroup.com

Internet communications are not secure and therefore the ITRS Group does not accept legal responsibility for the contents of this message. Any view or opinions presented are solely those of the author and do not necessarily represent those of the ITRS Group unless otherwise specifically stated.

[itrs.email.signature]

Attachment

image431672.png

pgsql-general by date:

From: jian he
Date: 14 November 2022, 10:29:42
Subject: Re: ON CONFLICT and WHERE

From: "Daniel Verite"
Date: 14 November 2022, 19:42:16
Subject: Re: Q: fixing collation version mismatches

Phantom Permissions Error - Mailing list pgsql-general

Attachment

Previous

Next