pgadmin-org / pgadmin-org/pgadmin4

Views/Materialized Views: MAINTAIN privilege silently dropped on PG17+ (allowed_privs.json looked up at wrong path)

Open Beginner friendly
#10,350 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Bug
Dominant language
Python
Stars
3.8k
Forks
891
Avg merge
4d 7h
Merged PRs (30d)
8

Description

Describe the bug

views/__init__.py:360 sets a fixed relative suffix for locating the allowed-privileges list:

_ALLOWED_PRIVS_JSON = 'sql/allowed_privs.json'

used at views/__init__.py:289:

self.allowed_acls = render_template(
    "/".join([self.template_path, self._ALLOWED_PRIVS_JSON])
)

For the default version bucket, the file genuinely lives under a sql/ subdirectory (e.g. templates/views/pg/default/sql/allowed_privs.json), so this resolves correctly. But for the PG17+ bucket, the file was added directly under the bucket directory, with no sql/ subdirectory:

web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/pg/17_plus/allowed_privs.json
web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/pg/17_plus/allowed_privs.json
web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/views/ppas/17_plus/allowed_privs.json
web/pgadmin/browser/server_groups/servers/databases/schemas/views/templates/mviews/ppas/17_plus/allowed_privs.json

So on PG17+/EPAS17+, render_template is asked for views/pg/17_plus/sql/allowed_privs.json, which doesn't exist. The failure is swallowed silently:

try:
    self.allowed_acls = render_template(...)
    self.allowed_acls = json.loads(self.allowed_acls)
except Exception as e:
    current_app.logger.exception(e)

with no user-visible error — self.allowed_acls simply doesn't pick up the 17_plus list, which is the one that adds MAINTAIN ('m') for views and materialized views. The result: the MAINTAIN checkbox still appears in the Grant Wizard / privileges UI (added via table.ui.js, shared across table-like objects), the user can tick it, but because allowed_acls never validated/loaded it for views/mviews, _parse_privileges drops it and the generated GRANT statement never includes MAINTAIN.

To Reproduce

  1. Connect to a PostgreSQL 17+ (or EPAS 17+) server.
  2. Open a view or materialized view's Properties/Security tab.
  3. Grant a role the MAINTAIN privilege and save.
  4. Check the generated SQL / re-open the properties: MAINTAIN was not actually granted.

Expected behavior

Either move the 17_plus allowed_privs.json files under a sql/ subdirectory to match the default bucket's layout, or make _ALLOWED_PRIVS_JSON version-bucket-aware, so the 17+ allowed-privileges list (including MAINTAIN) is actually loaded.

Found while re-verifying #5597 (MAINTAIN privilege support) — the table-side implementation is correct and complete; this is a views/mviews-specific packaging bug in the same feature.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start in views/init.py at the _ALLOWED_PRIVS_JSON definition and the allowed_acls loading code, then compare the default and 17_plus template paths listed in the issue for views and materialized views. Verify that the 17_plus allowed_privs.json is loaded and that granting MAINTAIN produces it in the generated SQL for PostgreSQL 17+ or EPAS 17+.

Written by the indexing model from the issue text.

Assessment

Tech stack
postgresql, python
Domain
backend, database
Issue type
Bug
Difficulty
2/5
Estimated time
1-3 hours
Activity status
Active
Clarity
Clearly specified
Newbie friendliness
82/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.