Search Postgresql Archives

Phantom Permissions Error

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

14

 

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." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public.@" does not exist

DEBUG:  relation "public.@" does not exist

DEBUG:  relation "public." 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.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." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." 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:  relation "public." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." does not exist

DEBUG:  relation "public." 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:  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@xxxxxxxxxxxxx
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]

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux