Support Multiple Clusters with Granular SSO Access

[yoav-katz]

allow multiple clusters to be managed from a single UI. The main goals are:
• Centralized visibility: DBAs can see and manage all clusters in one place.
• Granular access: SSO groups can have limited access to specific clusters (e.g., only there environment out of the whole company).

Use Case:
As a DBA managing a large number of clusters, it would be helpful to have a unified view and the ability to grant fine-grained access to different teams. This would simplify management and reduce the overhead of maintaining multiple UIs.

[yoav-katz]

I haven’t seen any open issue about this yet. I noticed in the PoWA demo that there’s a login with cluster selection, but I haven’t been able to find any code related to SSO or fine-grained access management.

[rjuju]

Hi,

Yes you can have multiple instances stored on a single repository server, and have a single powa-web instance used to access all of them, see for instance https://powa.readthedocs.io/en/latest/architecture.html#local-vs-remote-mode for a high level overview.

For the SSO and permission part, it's all based on plain postgres connections. If you have some SSO to connect to your postgres instances then it should work the same way with powa (assuming psycopg2 supports it). Not much has been done yet for the permissions, mostly because it's performance data that don't contain any sensitive information. The only exception is if you use pg_qualstats, which can contain sensitive values. We have some default "powa_*" roles that can be used to configure some access, including whether or not a user can see the pg_qualstats data (the powa_read_all_metrics role doesn't have access to pg_qualstats data while powa_read_all_data does).

[yoav-katz]

Thanks for your detailed response!

Do you think it would make sense for me to contribute a PR for the permissions part? My idea is that PoWA-web could:
• Keep track of all the databases it monitors.
• Manage connections to these databases and define which groups can view each cluster.
• Include an admin page to add new clusters and manage group access.

I’d love to hear your thoughts—do you think this approach is feasible, or would it be too much for PoWA-web?

[rjuju]

Do you think it would make sense for me to contribute a PR for the permissions part?

That would be highly appreciated! I just remembered that we did have some request for that some time ago (#249) but it's unfortunately not the highest priority currently.

• Keep track of all the databases it monitors.

Do you mean postgres instances (meaning a single remote server / postgres daemon running, ) or actual databases in each of those remote servers?

We already have the list of instances with associated metadata in the powa_servers.

We also have the list of databases on each remote servers in powa_databases maintained by the usual snapshot process, although I don't think that it's guaranteed to be up to date all the time (for instance if you don't have any query at all on those databases then they will never appear). There is also powa_catalog_databases, which should be a better choice I think, but it's also only refreshed at a low frequency. The UI allows you to ask for a catalog refresh so it shouldn't be a blocker.

• Manage connections to these databases and define which groups can view each cluster.

There are actually 2 kind of access: the "pure metrics only", which means that the connection will be on the same repository server for any database/remote server. In that case there should be a new permission layer in the app route depending on the server/database and the current user. The other one is the "remote server access", that is used when you need dynamic access. For instance if you want to check index suggestion with hypothetical indexes, you need to connect to run some EXPLAIN queries. Those are real connections to the target server/database and will need a different approach.

• Include an admin page to add new clusters and manage group access.

+1

We wanted to add the possibility to configure stuff in the UI for a long time. There is an SQL API to configure the remote servers and the extensions you want to snapshot that was written as a first step, so that the UI can eventually use it.

[yoav-katz]

Regarding your question about “databases” — I meant Postgres clusters, or what you referred to as instances (i.e., a remote server running postgres).

What I have in mind is that the repository would maintain the connection info for each target cluster (e.g., certificates or other connection methods). Then, as you mentioned, we would have two types of permissions:

1. Pure metrics access — where users can view metrics from any cluster through a centralized connection.
2. Remote server access — for users who need to connect directly to a specific cluster for advanced operations like running EXPLAIN queries or hypothetical indexes.

Ideally, users would authenticate through SSO, and based on their permissions, they could seamlessly access the clusters without needing to individually configure connections on their end.

I was thinking of using OIDC for the SSO integration since LDAP typically handles username and password management and SAML feels somewhat dated, but I’d be interested to hear if you have a preference or other suggestions.

Please let me know if that aligns with your understanding or if there’s anything I’m missing. I’d be happy to contribute a PR around the permissions layer and/or the UI admin page when you think the timing is right!

[yoav-katz]

this seems like a really big change and a big PR,
how do you think we should handle this? split it to more than 1 PR? how?

[rjuju]

Yes that's definitely a big project, and ideally it should be split in multiple smaller and self contained PR.

I understand how relying on oauth2/oidc would solve both the authentication / authorization, I don't think that we can have a hard requirement on it. A lot of people don't have that setup, and we should still fully handle authn/z for them too.

I also see some problems down the road with that. Postgres itself doesn't allow "modern" SSO, at least for now. For all the supported version you usually have to rely on kerberos for that (or possibly ldap) if you want SSO, otherwise just use plain user/password or certificate authentication. If I understand correctly, what you have in mind is to always store the actual postgres credentials for all the remote servers and use that to connect? That's supported for now but it's definitely not a good idea from a security point of view, and we recommend relying on the provided credentials to connect on any remote server.

[yoav-katz]

You're right about keeping OIDC optional. I agree it shouldn't be a hard requirement.
It could just be a pluggable option for environments where OIDC is already in use, while falling back to existing authentication mechanisms for others.

Regarding the actual postgres connections: yes, I was initially thinking about storing connection credentials centrally (for example using certificates or password-based configs) and controlling access through the app, but I understand the security concerns with long-term credential storage. My idea was mainly to simplify user experience (like SSO-based access without configuring connections), but i definitely agree security as more important :)

Given the current state of postgres and the limitations around "modern" SSO, maybe optional LDAPS is the most practical approach, bc it would allow integrating with existing directory services securely but still support environments that prefer user-pw/certs.

Also - do you think it would make sense to separate the authorization logic for metrics access and actual database connections? They serve different purposes and involve different levels of sensitivity, so treating them distinctly might give us more flexibility and cleaner permission handling.

thanks so much for your help! this has been really helpful in terms of locking in on the right approach for our goals :)

[yoav-katz]

After thinking a bit more, here’s what I have in mind:

• An admin UI (and corresponding API) to manage which instances each SSO group can view.

• If a user has access to an instance but cannot authenticate to the remote Postgres instance, they would see metrics only. Otherwise, they would get the full dashboard (with EXPLAIN and other features). I’m not entirely sure if this is how it works currently.

• Kerberos authentication would be the best option and optional. If it’s not configured in powa-web.conf, authentication and authorization would fall back to the current behavior.

What do you think about this approach?

[rjuju]

I think that there really is 3 main areas to work on.

1. For now authentication to powa-web is done using the real postgres credentials. It's convenient as there is no need to store any database credential on the powa-web machine itself, and you can also easily track what anyone did
2. As a consequence, connection from powa-web to the repository server and any remote server is also done using the exact same credential
3. there is no real permission feature in powa-web. You can achieve it with plain GRANT/REVOKE on the underlying servers, or even row level security, but this is troublesome to manage.

1 and 2 have to be dealt together, as they're for now a single feature that will likely need to be split, while 3 can be done as a separate project, but it does rely on 1 being done first.

So I think for now the real question is how to handle powa and database authentication, how to make it secure and extensible. Note that one good thing is that all database connections will come from powa-web, so it's still possible to setup the host itself (for instance provide certificats, .pgpass files, env variables and so on) to avoid storing plain text password in the database.