Need to use a user other than postgres to register the remote servers

[hrawulwa]

My current setup is that all the remote servers are registered using postgres as username and dbname.
The pg_hba file on the remote servers have the entry of repository server using trust authentication.
The security team does not like it, and wants to use user other than postgres which is super user.
Is it possible to use a normal user? If so, what privileges need to be granted to this user?
The plan is to use PAM authentication. If not possible, then atleast need to use password authentication for the new user.
Appreciate your advice.

Thanks
Hari

[rjuju]

There's no requirement for having a superuser connection for any of the servers in a powa setup, if anything I would also recommend not doing that.

Creating and configuring the various extensions (including pg_stat_statements) requires a superuser connection, but this is only needed for the CREATE EXTENSION and modifying shared_preload_libraries. For everything else you just need a regular user, with the following privileges:

• CONNECT on the database where the powa and various extensions are installed on the remote server
• pg_read_all_stats privilege on the remote server, to be able to see the statistics

If you want the UI to be able to connect to a remote server, you also need

• CONNECT on any of the remote databases you want to be able to connect to

For the authentication, it's just a psycopg2 connection, which relies on libpq. So any authentication method should work just fine with powa, assuming you configure it as needed. You also need to remember that it's mostly powa-collector t hat needs to authenticate on all the servers, so the user that runs powa-collector needs to be configured this way. It could be either with a local .pgpass file, an explicit password in powa_servers table (which I also don't recommend), or anything else. If you want the UI to be able to connect to a remote server, you also need to configure the user that runs powa-web to login on those remote servers,

[hrawulwa]

Ok. The powa-collector is also running on the repository server and connects to the repository database as powa_adm user.
So, do you suggest to create the same user (powa_adm) on all the remote servers, and grant CONNECT and pg_read_all_stats privileges to this user? Just to let you know that powa_adm is AD service account, where password is authenticated by AD. If I can configure pg_hba on remote servers to use PAM authentication, then I believe I do not have to mention anything in the password field for powa_adm user while registering remote servers using powa_register_server. Please confirm.

Thanks
Hari

[rjuju]

yes, using the same powa_adm non superuser role should work, assuming that powa-collector (and possibly powa-web if you need it) can get an AD token

[hrawulwa]

I found out that even for AD user accounts, I have to mention the password while registering the server, because password still needs to be passed to the remote server which gets authenticated by AD server, instead of locally. That's the only difference.
If I do not mention the password, I get the error: fe_sendauth: no password supplied.

I have another question when using the powa_register_server function. Even though I'm passing regular user and not postgres in the username parameter, I still need to pass postgres as the value for dbname parameter, right? Because there will be multiple databases in the remote server, and I need to monitor all of them.

Thanks
Hari

[rjuju]

I found out that even for AD user accounts, I have to mention the password while registering the server, because password still needs to be passed to the remote server which gets authenticated by AD server, instead of locally. That's the only difference.
If I do not mention the password, I get the error: fe_sendauth: no password supplied.

I think that's because the configuration on the remote server side. maybe it's relying on something like ldap authentication (which does require passing a plain text password) rather than gss. or maybe pam is configured to do the same lookup against your AD? but in any case there's nothing powa can do about it, it's what the postgres server requires. you could still rely on a .pgpass file to provide the password rather than storing it in plain text in the powa database, or passing it as env variable using some secret if you rely on containers.

I have another question when using the powa_register_server function. Even though I'm passing regular user and not postgres in the username parameter, I still need to pass postgres as the value for dbname parameter, right? Because there will be multiple databases in the remote server, and I need to monitor all of them.

you need to specify the database where powa is installed. all the extensions (pg_stat_statements and other) provide cluster-wide statistic so you only need to connect on that database.

note that powa v5 will change things here, as the collector can try to connect to all databases to retrieve db-local metrics (pg_stat_*), but there will be configurations to specify which db to connect on (all, none, specific list or exception list).

[hrawulwa]

Yes, it is PAM authentication enabled on the remote server. Btw, I was trying to use .pgpass file on the powa repository server with no success. I still got the fe_sendauth: no password supplied error. May be I did not configure correctly.
I left the password null while registering server.
The format for pgpass is as below:
localhost:5432:powa:powauser:
Note my powa-collector is running on the same server as repository database server.

Regarding another question I asked powa_register_server, the reason I asked if I can specify postgres as the database in the database parameter field, is because, I'm going to now register a lot of remote DB servers, and I don't want to go and find out what is the actual database name over there. And yes, all the extensions are present in all the databases including postgres.

Thanks
Hari

[hrawulwa]

Looks like there was some typo in the pgpass format in last reply.
The format for pgpass is as below:
localhost:5432:powa:powauser:actualpassword

[rjuju]

I just tested locally and the .pgpass file works as expected for powa-collector. You probably have some misconfiguration somewhere (wrong OS user, wrong ownership or permission or something). Can you can check that using psql in the exact same condition as powa-collector can succeed?

Regarding another question I asked powa_register_server, the reason I asked if I can specify postgres as the database in the database parameter field, is because, I'm going to now register a lot of remote DB servers, and I don't want to go and find out what is the actual database name over there. And yes, all the extensions are present in all the databases including postgres.

That shouldn't be a problem. All access done by powa (either by the collector or the UI) are read-only query, so as long as it can access pg_stat_statements and any of the configured stat extensions it will work.

[hrawulwa]

Oh yes, I mentioned the hostname as the localhost (repository server), instead of the remote server.
After mentioning the correct remote server details in .pgpass file, it worked.
Thanks for clarifying question about powa_register_server.

[hrawulwa]

I have another question.
Most of my remote DB servers have older extensions, including powa-archivist and others.
After installing new extensions, I'm not able to update some extensions, like pg_qualstats which throws error like, the extension has no update path from version 2.0.2 to version 2.1.0. So I have to drop and create. Similarly, for hypopg. I'm only successfully able to update pg_stat_kcache and powa.
For simplicity, I'm just thinking to drop and recreate all of these extensions.
Since these are remote servers, I hope I will not be losing any data, except during the time when these are getting recreated.
Please confirm.

[rjuju]

the only requirement is to have the same major version for powa-archivist. so for instance 4.5.x everywhere. this extension takes care of all the backward compatibility with any supported extension.

for the extension without upgrade path, it's because they don't actually store any data so it's doesn't matter if you upgrade or drop/create them. it can of course be problematic if you create custom views on top of them, but if any of the datatype is changed or some column is dropped you will end up with the same problem so I think it's cleaner to always force drop/create rather than randomly forcing it. on the remote server themselves, the powa-archivist extension is only used as a way to expose a compatible interface to the various other extensions, so you can also drop and recreate it. the only exception would be if you also configured local snapshot on the remote server configuring powa in shared_preload_libraries, as then you would have stored data. but then you would also snapshot everything twice which wouldn't really make sense.

[hrawulwa]

Yes, none of the remote servers has powa in shared_preload_libraries. Only the stats extensions are mentioned.
So I believe dropping and recreating extensions on remote servers should be good.
In fact, I do not have powa configured in shared_preload_libraries on the repository database server as well, as I do not want to store the performance data for repository server.

[rjuju]

yes, dropping and recreating the extensions won't be a problem. you might want to do that in a transaction though, in the event of a powa snapshot happening in the middle if you don't want that snapshot to fail.

[hrawulwa]

Yes, I will have to update extensions on DB servers, so will come up with a script to do one DB at a time.
Thanks for your advice.

[hrawulwa]

Just to let you know that granting CONNECT and pg_read_all_stats role to the regular user did not work.
I had to additionally grant the following privileges.
GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO ;
GRANT EXECUTE ON FUNCTION pg_qualstats_reset TO ;

Without the above privileges, the UI throws the below errors:

VDAM-QA1: Error while calling public.powa_kcache_src: permission denied for table powa_databases CONTEXT: SQL statement "SELECT now(), k.queryid, k.top, k.userid, k.dbid, k.plan_reads, k.plan_writes, k.plan_user_time, k.plan_system_time, k.plan_minflts, k.plan_majflts, k.plan_nswaps, k.plan_msgsnds, k.plan_msgrcvs, k.plan_nsignals, k.plan_nvcsws, k.plan_nivcsws, k.exec_reads, k.exec_writes, k.exec_user_time, k.exec_system_time, k.exec_minflts, k.exec_majflts, k.exec_nswaps, k.exec_msgsnds, k.exec_msgrcvs, k.exec_nsignals, k.exec_nvcsws, k.exec_nivcsws FROM pg_stat_kcache() k JOIN pg_roles r ON r.oid = k.userid WHERE NOT (r.rolname = ANY (string_to_array( powa_get_guc('powa.ignored_users', ''), ','))) AND k.dbid NOT IN (SELECT oid FROM powa_databases WHERE dropped IS NOT NULL)" PL/pgSQL function powa_kcache_src(integer) line 14 at RETURN QUERY
VDAM-QA1: Error while calling public.powa_qualstats_src: permission denied for table powa_statements CONTEXT: SQL statement " SELECT now(), pgqs.uniquequalnodeid, pgqs.dbid, pgqs.userid, pgqs.qualnodeid, pgqs.occurences, pgqs.execution_count, pgqs.nbfiltered, pgqs.mean_err_estimate_ratio, pgqs.mean_err_estimate_num, pgqs.queryid, pgqs.constvalues, pgqs.quals FROM ( SELECT coalesce(i.uniquequalid, i.uniquequalnodeid) AS uniquequalnodeid, i.dbid, i.userid, coalesce(i.qualid, i.qualnodeid) AS qualnodeid, i.occurences, i.execution_count, i.nbfiltered, i.mean_err_estimate_ratio, i.mean_err_estimate_num, i.queryid, array_agg(i.constvalue order by i.constant_position) AS constvalues, array_agg(ROW(i.relid, i.attnum, i.opno, i.eval_type)::qual_type) AS quals FROM ( SELECT qs.dbid, CASE WHEN lrelid IS NOT NULL THEN lrelid WHEN rrelid IS NOT NULL THEN rrelid END as relid, qs.userid as userid, CASE WHEN lrelid IS NOT NULL THEN lattnum WHEN rrelid IS NOT NULL THEN rattnum END as attnum, qs.opno as opno, qs.qualid as qualid, qs.uniquequalid as uniquequalid, qs.qualnodeid as qualnodeid, qs.uniquequalnodeid as uniquequalnodeid, qs.occurences as occurences, qs.execution_count as execution_count, qs.queryid as queryid, qs.constvalue as constvalue, qs.nbfiltered as nbfiltered, qs.mean_err_estimate_ratio AS mean_err_estimate_ratio, qs.mean_err_estimate_num AS mean_err_estimate_num, qs.eval_type, qs.constant_position FROM pg_qualstats() qs WHERE (qs.lrelid IS NULL) != (qs.rrelid IS NULL) ) i GROUP BY coalesce(i.uniquequalid, i.uniquequalnodeid), coalesce(i.qualid, i.qualnodeid), i.dbid, i.userid, i.occurences, i.execution_count, i.nbfiltered, i.mean_err_estimate_ratio, i.mean_err_estimate_num, i.queryid ) pgqs JOIN ( -- if we use remote capture, powa_statements won't be -- populated, so we have to to retrieve the content of both -- statements sources. Since there can (and probably) be -- duplicates, we use a UNION on purpose SELECT s1.queryid, s1.dbid, s1.userid FROM pg_stat_statements s1 UNION SELECT s2.queryid, s2.dbid, s2.userid FROM powa_statements s2 WHERE s2.srvid = 0 ) s USING(queryid, dbid, userid) -- we don't gather quals for databases that have been dropped JOIN pg_database d ON d.oid = s.dbid JOIN pg_roles r ON s.userid = r.oid AND NOT (r.rolname = ANY (string_to_array( powa_get_guc('powa.ignored_users', ''), ','))) WHERE pgqs.dbid NOT IN (SELECT oid FROM powa_databases WHERE dropped IS NOT NULL) " PL/pgSQL function powa_qualstats_src(integer) line 84 at RETURN QUERY
VDAM-QA1: Error while calling SELECT pg_qualstats_reset(): permission denied for function pg_qualstats_reset

[rjuju]

ah thanks for the info, I actually wanted to document the permission needed so it's good to know
for pg_qualstats it's a bit troublesome as indeed it doesn't fit with read all stats. for the grant on tables, that should be fixed with the upcoming version 5, which will bring pseudo predefined roles that can be used for that.