You need to be careful about the security of your PostgreSQL instance when installing PoWA.
We designed POWA so that the user interface will only communicate with PostgreSQL via prepared statements. This will prevent the risk of SQL injection.
However to connect to the PoWA User Interface, you will use the login and password of a PostgreSQL user. If you don’t protect your communications, an attacker placed between the GUI and PostgreSQL, or between you and the GUI, could gain your user rights to your database server.
Therefore we strongly recommend the following precautions:
Check your pg_hba.conf file
Do not allow users to access PoWA from the Internet
Do not allow users to access PostgreSQL from the Internet
Run PoWA on a HTTPS server and disable HTTP access
Use SSL to protect the connection between the GUI and PostgreSQL
Reject unprotected connections between the GUI and PostgreSQL (hostnossl …. reject)
Check your pg_hba.conf file again
Please also note that you need to manually authorize the roles to see the data in the powa database. For instance, you might run:
powa=# GRANT SELECT ON ALL TABLES IN SCHEMA public TO ui_user;
powa=# GRANT SELECT ON pg_statistic TO ui_user;
powa-web will connect to the databases you select to help you optimize them.
Therefore, for each postgres roles using powa, you also need to:
grant SELECT privilege on the pg_statistic and the user tables (don’t forget tables that aren’t in the public schema).
give CONNECT privilege on the databases.
If you don’t, some useful parts of the UI won’t work as intended.
TLS and default HTTP/HTTPS ports¶
As powa-web is just a python script, it’s probably better not to run it as root. This means it won’t be able to bind to the default HTTP (or HTTPS) port.
The most secure way of doing is putting a reverse proxy, like nginx, in front of it. You’ll also get the possibility of using an SSL certificate (which can be also done using powa-web script, but is not recommended due to the same security concerns).
PoWA-archivist & PoWA-collector in remote mode¶
Connection on remote servers¶
With PoWA version 4 and newer, you can register remote servers in the powa_servers table (usually using the powa_register_server function).
This table can optionally store a password to connect on this remote server. If the password is NULL, the connection will then be attempted using the authentication method that libpq supports of your choice.
Storing a plain text password in this table is definitely NOT a best practice, and we encourage you to rely on the other libpq authentication methods.
Required privileges on remote servers¶
In order to perform the metric snapshots, the role defined to connect on the remote servers requires the following privilege for each remote server:
for PostgreSQL 9.6 and lower: a superuser role
for PostgreSQL 10 and above: either a superuser role or a roler member of the group pg_read_all_stats (see the default roles documentation)
read & write privileges on all the PoWA tables
remote & repository servers
execute privilege on all the PoWA functions