Database tree lists nothing when the connecting role lacks CONNECT on other databases (pg_database_size -> permission denied)

[brains-fatman]

Summary

When the connecting role does not have CONNECT privilege on every database in the cluster, expanding a connection shows no user databases at all — including the ones the role can access. The "Databases" node still shows a non-zero count, but expanding it yields an empty list.

Environment

• PgStudio: 1.4.1
• Editor: VSCodium 1.121 (also reproducible on VS Code)
• PostgreSQL: 14–16 (any), shared / multi-tenant cluster with per-role database isolation

Steps to reproduce

-- as superuser
CREATE ROLE limited LOGIN PASSWORD 'x';
CREATE DATABASE app_a;     -- the role's own DB (alphabetically NOT first)
CREATE DATABASE other_b;   -- a neighbouring DB the role must not touch
REVOKE CONNECT ON DATABASE other_b FROM PUBLIC;
GRANT  CONNECT ON DATABASE app_a   TO limited;
-- 'limited' has no CONNECT on other_b and is not a member of pg_read_all_stats

1. Add a connection as role limited.
2. Expand the connection → expand Databases.

Expected

The databases the role can access (app_a) are listed. Inaccessible databases are skipped (or shown without a size).

Actual

The list is empty and an error is raised:
permission denied for database "other_b"
The role's own app_a never appears.

Root cause

src/providers/tree/loaders/ConnectionLoader.ts enumerates databases with a single query that calls pg_database_size() for every row:

SELECT datname, pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database
ORDER BY datname;

Per the PostgreSQL docs, pg_database_size() requires CONNECT on the target database (or membership in pg_read_all_stats). Since it runs in one statement over the whole cluster, the first inaccessible database aborts the entire SELECT, so the result set is empty and no databases render.

The same pattern is also used in the system-databases-group branch (same file), which can fail on template0/template1/postgres for the same reason.

Suggested fix

Guard the size computation with has_database_privilege, so inaccessible databases are skipped and size is only computed where allowed:

SELECT datname,
       CASE WHEN has_database_privilege(datname, 'CONNECT')
            THEN pg_size_pretty(pg_database_size(datname))
            ELSE NULL
       END AS size
FROM pg_database
WHERE datname <> 'template0'          -- never connectable
ORDER BY datname;

If listing inaccessible databases is undesirable, add AND has_database_privilege(datname, 'CONNECT') to the WHERE. The same guard should be applied to the system-databases-group query.

Impact

Affects anyone with restricted privileges in a shared cluster — the extension is effectively unusable for them even for their own database.

[github-actions]

Welcome to the project, @brains-fatman! 👋
Thank you for taking the time to improve our codebase. We really appreciate the effort.

[ric-v]

@brains-fatman Thanks for the detailed issue and solution. We have taken a note of this and will release a fix for this asap. We are in the process of a major overhaul to the extension and a rebranding.
We'll share an update and fix on this issue soon.