Обсуждение: Lost permission

Hello!

I've strange problem with Pyramid application working with postgresql
over psycopg2.

After some period of operation, my application insert new row to one of
tables with the message:

ERROR:  permission denied for relation ...

After restart of application, it starts to work properly again.

I've carefully checked that there are no GRANT/REVOKE statements related
to this table. Application connects to PostreSQL from dedicated account
using peer authentication. Restart of application doesn't change any
permissions.

The problem is related only to this one table (but this table has
biggest number of inserts).

How to diagnose this problem ?

debian stretch, PostgreSQL 9.6.10, all packages updated.

best regards
J.

Jan Kowalski <jk@poczta.srv.pl> writes:
> I've strange problem with Pyramid application working with postgresql
> over psycopg2.
> After some period of operation, my application insert new row to one of
> tables with the message:
> ERROR:  permission denied for relation ...
> After restart of application, it starts to work properly again.

Couple of possible ideas:

* something in your app sometimes issues SET ROLE or SET SESSION
AUTHORIZATION.

* you're using a connection pooler and it sometimes reconnects you
to a connection with the wrong user ID.

Neither of these seem terribly likely, but whatever it is has to
be weird ...

            regards, tom lane