search

powa-team / powa-collector

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

List Permissions for DB user in powa-collector config #7

Closed gowtham500 closed 3 years ago

gowtham500 commented 3 years ago

Hello team,

Versions - Remote server

powa-4.1.2 PostgreSQL 12

Versions - Central Repository

powa-4.1.2 powa-collector powa-collector.py version 1.1.1 PostgreSQL 13

cat powa-collector.conf

{ "repository": { "dsn": "postgresql://powa_user:<pwd>@<central repository>:5432/powa" }, "debug": true }

Questions:

  1. Each Remote server has powa DB and powa extension is installed in it.
  2. powa-collector is using powa_user to connect to remote server. DEBUG output shows CONNECTED.
  3. powa-user has read/write on all powa tables. It has execute permission on all powa functions.
  4. While taking snapshot I get error

2021-02-06 15:12:52,394 <remote server fqdn>:5432 WARNING: Error while inserting data: null value in column "queryid" of relation "powa_statements_src_tmp" violates not-null constraint DETAIL: Failing row contains (1, 2021-02-06 15:12:50.951799+00, 16501, 16502, null, <insufficient privilege>, 87, 0.33797199999999994, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0). CONTEXT: COPY powa_statements_src_tmp, line 1: "1 2021-02-06 15:12:50.951799+00 16501 16502 \N <insufficient privilege> 87 0.33797199999999994 0 0 0..."

This error seems to go away if I make powa_user as SUPERUSER. Not sure if this is related to COPY.

Can you please let me know the minimum permissions required for this user.

rjuju commented 3 years ago

hello @gowtham500

Thanks a lot! You raised a very good point. This should be covered in https://powa.readthedocs.io/en/latest/security.html but clearly I failed to properly document the required permissions for the remote servers. I'll fix that shortly.

powa-collector is using powa_user to connect to remote server. DEBUG output shows CONNECTED.

Just in case, note that the powa_user specified in the powa-collector.conf is not the role used to connect on the remote server, only for the repository server. The roles used to connect on the remotes servers are provided when calling powa_register_server(), and are stored in the powa_servers table. It's of course possible that those are the same.

powa-user has read/write on all powa* tables.

This part is only required for the repository server

It has execute permission on all powa* functions.

this part is required for both repository and remote servers.

While taking snapshot I get error [...]

That's the root issue. On the remote servers, the role you're using must have enough permissions to read pg_stat_statements (and pg_qualstats if used) data. This means superuser for pg10-, and for pg10+ either superuser or member of pg_read_all_stats (see https://www.postgresql.org/docs/10/default-roles.html).

gowtham500 commented 3 years ago

Thanks for quick reply. Looks like granting role pg_read_all_stats seems to have worked. I will keep testing and let you know of any issues.

rjuju commented 3 years ago

Thanks a lot for the confirmation!

rjuju commented 3 years ago

For the record I just pushed documentation changes for those requirements at https://github.com/powa-team/powa/commit/a54de377cc4c89e91abc70eff9f9280b9735698a

You can see the results at https://powa.readthedocs.io/en/latest/security.html#required-privileges-on-remote-servers. Let me know if you think it needs some more details.

gowtham500 commented 3 years ago

The doc update looks good. Permissions - i have tested and didnt see any issues so far on PG12, PG13.

rjuju commented 3 years ago

Perfect. Thanks again!