search

powa-team / powa-collector

PoWA Collector daemon for remote snapshot
http://powa.readthedocs.io/
PostgreSQL License
7 stars 4 forks source link

Error during remote setup #14

Closed seqizz closed 1 year ago

seqizz commented 1 year ago

Hi,

I'm trying to set up remote powa, but got following error:

2023-04-17 13:39:26 UTC [23966-1] powa@::1 ERROR:  null value in column "queryid" of relation "powa_statements_src_tmp" violates not-null con
straint
2023-04-17 13:39:26 UTC [23966-2] powa@::1 DETAIL:  Failing row contains (697, 2023-04-17 15:39:26.124705+02, 16393, 17403, null, <insufficie
nt privilege>, 6, 2.4073050000000005, 6, 57, 0, 13, 0, 0, 0, 0, 0, 0, 0, 0, 0, 6, 0.07922900000000001, 25, 11, 26617).
2023-04-17 13:39:26 UTC [23966-3] powa@::1 CONTEXT:  COPY powa_statements_src_tmp, line 1: "697 2023-04-17 13:39:26.124705+00   16393   17403
   \N      <insufficient privilege>        6       2.4073050000000005      6       57      ..."
2023-04-17 13:39:26 UTC [23966-4] powa@::1 STATEMENT:  COPY powa_statements_src_tmp FROM stdin

Which is either a table structure issue, or I am missing something on the remote target (not getting any permission issues on there, according to logs). When I run select public.powa_statements_src(0); manually on the target, I get the result but it has stuff like:

1317    2023-04-17 14:42:03.531795+00   16405   16402   \N      <insufficient privilege>        2       1.575663        0       0       0   00       0       0       0       0       0       0       0       0       0       0       0       0       0

So I am not sure what privileges exactly it is missing currently.

Sorry if I opened to the wrong project, this one felt most relevant.

Also thanks for the effort! :rocket:

rjuju commented 1 year ago

Thanks a lot :)

So I am not sure what privileges exactly it is missing currently.

I think you tried to give permission on the varaious schema so that the user declared for that remove server can successfully call the various functions. Unfortunately pg_stat_statements has a 2nd layer of security, as the query string (and the query id) could in theory leak some information. The documentation is far from perfect on that point (and it's in my plan to overhaul the whole security side documentation of powa), but you can already refer to https://powa.readthedocs.io/en/latest/security.html#required-privileges-on-remote-servers for most of it. As noted there, if you're using pg 10 or later (and I hope you're since pg10 is already EOL), what you need is to give pg_read_all_stats privilege on each remote server for the role configured on powa-collector to connect on that server.

seqizz commented 1 year ago

Oh, well thanks for the insta-answer :)

Yep, missing privilege seem to be it. Now it works without any errors/warnings. Sorry for wasting your time, since it should be already clear on documentation.

rjuju commented 1 year ago

Great news! And no worries, the documentation clearly needs some improvements!