Needed postgresql user permissions for netdata monitoring?

Help
1

I’m trying to monitor Postgresql servers, so I’ve enabled the python.d/postgresql plugin but I want to create a dedicated postgresql user for this plugin.
Could you tell me what are the needed permissions, to allow this plugin to work with all the features enabled ?

Thanks a lot.

Morgan

2

Hey Morgan :wave:

I quickly deployed a test db and got the plugin working even with a user that didn’t have permissions (or rather attributes), created him with CREATE USER fotis WITH PASSWORD 'test';.

My \du results from within psql is:

 List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 fotis     |                                                            | {}
 pi        | Superuser, Create role, Create DB                          | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

And my postgres.conf file is like this:

socket:
  name         : 'socket'
  user         : 'postgres'
  database     : 'postgres'

tcp:
    name     : 'local'
    database : 'fotisdb'
    user     : 'fotis'
    password : 'test'
    host     : 'localhost'
    port     : 5432

And the plugin works as intended!

3

Hi @Ancairon,
thanks for your answer. I’m ok with you but you’ll see some new charts (like Replication Delta and Archive WAL) if the user has more permissions (tested with ‘postgres’ user), especially for a cluster with replication.

Thanks

Morgan

1 Like
4

I did some searching on the Postgresql documentation and on Netdata’s own code, the charts Wal and Archive Wal for example seem to be made only if you are a superuser. Snippet of the source code:

1244 if self.is_superuser:
1245            self.queries[query_factory(QUERY_NAME_ARCHIVE, self.server_version)] = METRICS[QUERY_NAME_ARCHIVE]
1246
1247            if self.server_version >= 90400:
1248                self.queries[query_factory(QUERY_NAME_WAL, self.server_version)] = METRICS[QUERY_NAME_WAL]

This comes from this file.
I could be wrong but it seems as to get these kind of charts you need to make the new user a superuser, defying probably your needs because a superuser is like the postgres user.

(there may be no default role to include all of the functions Netdata needs to run, and to grant the permition you should need a list of all these functions to give them execute access to the new user… But even that user probably wouldn’t pass the checks mentioned above)

I hope this helps,
Fotis

5

Ok, thanks for your help and your time !

1 Like