Error "permission denied for table pg_statistic" while a DB is being created from a big template

[JVC-MSF]

Hi.

First of all. Thanks for this wonderful and useful piece of software!!

I'm have identified this issue while running the btree_bloat service but can be happening in other ones too.

I have a server that quite often, several times per day, creates new database from dumps from other servers or from big templates built from that dumps.

At the end of the database creation, I run a GRANT statemet to grant permissions to nagios user on the new db.

But some times, Icinga2 is launching the test while the DB creation is not yet finished and then nagios user is not yet having permissions.

This are kind of false positives that are resolved automatically when the db is finally created and the permissions granted. But is annoying and polutes my incident logs.

Is it possible to have check_pgactivity to ignore databases that in process of being created?

As a secondary topic.

The error message looks like that:
psql:/tmp/check_pga-uOCCbDjB:79: ERROR: permission denied for table pg_statistic

Without any indication of which DB is not having the right permissions.

This is annoying when you have 50+ databases in the same server.

Can the error message include which is the offending DB?

If any additional information is needed, just ask.

Thanks in advance
Javier Vilarroig

[frost242]

Hi,
Thanks for the report. IMHO, there's no way to figure out that database is being created. But we definitely should try to do something to improve the error message you're receiving.
Stay tuned.
Thanks

[JVC-MSF]

May be an option is to have a database table somewhere, where we can indicate check_pgactivity to ignore specific databases.

That way we can avoid false positives when a database is having any maintenance task running on it.

It will be sysadmin/dba to have that database properly populated and maintained.

What do you think?

[Krysztophe]

We don't want check_pgactivity to use tables.

Isn't there a moment in the day where there is no risk od DB creation? btree_bloat is not supposed to run very often anyway.

[JVC-MSF]

Yes, I agree that a table is not the best solution. Was just a crazy idea.

No. No guarantee that DB server is not creating DB's.

We have a Jenkins pipeline that deploys new testing servers when a PR is created. This includes creating a database out of a template with some testing data. This can happen at any time, we have our development team spread over different time zones all around.

The case of a DB built on the fly can be managed. The issue is that my development DB server can have some tens of DB's and not knowing which one is having the permissions issue is messy.

As frost242 mentioned, just good logging, including the DB name, will be enough. At least will allow me to identify which is the faulty db in case of real error and fix it without having to change permissions in all the databases.