Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tu6wY-003iRO-Va for pgadmin-hackers@arkaria.postgresql.org; Mon, 17 Mar 2025 09:39:35 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1tu6wW-009efb-Tf for pgadmin-hackers@arkaria.postgresql.org; Mon, 17 Mar 2025 09:39:32 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tu6wW-009edD-DF for pgadmin-hackers@lists.postgresql.org; Mon, 17 Mar 2025 09:39:32 +0000 Received: from mail-ua1-x933.google.com ([2607:f8b0:4864:20::933]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tu6wS-003IXT-3C for pgadmin-hackers@postgresql.org; Mon, 17 Mar 2025 09:39:30 +0000 Received: by mail-ua1-x933.google.com with SMTP id a1e0cc1a2514c-86d5e42c924so4349451241.3 for ; Mon, 17 Mar 2025 02:39:28 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1742204368; x=1742809168; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=zFBnbK7u3nF0+rn65AoKurmRZIAkqDmjAiiKgjGQEsc=; b=fhlyKtDJuSW2Pf5cejOsxiE/e/64uKchUEMwParajRbiuju9g+vVUwzhMdnImWrbOk EBbqgI4/OqZX2C2UWYX7NThTF8948re7fzJvlw2ZWrQNHLzUH0pFD4wTvIzFWj9EyWNM PplHgA/mp4LrFScgyrkjRPAhyYONNIPS2D6uNXUYk0D8Y2tWuatkmJtgVLqWRpmCrVuu yYuEHTbOQTL4EWC8Z5dHM1jicdWU2gROdVFOyUFjvvVXYoH1cY7mv0GgNag39rFlLo/C 2nkFKeOJfWmtUkBr6omG2CBcScrsL5UlSzYoer2C/ZNtXUnx12aDtHBDsm5xZsDCPwkF w2AA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1742204368; x=1742809168; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=zFBnbK7u3nF0+rn65AoKurmRZIAkqDmjAiiKgjGQEsc=; b=I0w1d5OBCBpgMpl1AhR+5Om2Iat+L3OHED9Ukj+VS+pj9sOAE6TuxKNoRRB4AveCEu NFnpug8xs6dmHV9T2fVNGg/OggncxIQYGn3r6ln9KkpieRDxbNAtcNnOgKFv88A8V6Ms 1gZ2ooA19nHbygOhWYjXkEzy/8hUs5orGoVVcqOMBoKsr1pFb2Q94vlqPHjtinWqyorH r7fVOnBSfXGa02ViqxyyLzQol7c8mC9ZcYL6m+kCj4WeczVn+WY1d1yGPg0IdKee4tFJ skLPbAzrf8AAgyUinRxwTtegBqsbz+djGjTSyXwb3tVG0CtbWSQPyqSieH+6bsx7Gu4A yxoQ== X-Gm-Message-State: AOJu0Yw0e2FIs1m0GPT9gWNFFrnKYxn6YssqUKxvc3dxwZd3upTBfllS qJ4gO9p54aJv4iMrcSD7K+S7klY1WKVFQdimT9Oq8k+djjmcUnqU562xCBxaEBMj5jSoR16Bi82 C/nekP/dZzxxSkLLjp5HV9b9H2tXaZ2oipKXjgxg2Zy8CXf+KBg== X-Gm-Gg: ASbGncsg6GTNi/yjAqAUKDbpzjAgQWbo/vtZRaTjJStMlnMmgwC4eTZHZKpr3TM4H+3 Qa59YfM/ybk7u/KQD5WOFRK7zZyje64YlKZCMiU+tigA9V1/UgXzwAwD4XY9qNnpWXHZngUgRds gINZIKGPBBXIhOSErCKoep7yWjzsFV36GBhVQ/4CZvNsAmaOkxpSZ9lbJMBE6g X-Google-Smtp-Source: AGHT+IEXx9mVqQSKU31gfLhO+iB4gWSHQorzZBxCUH/JDyg/MhkHYTAYw3juJ3FfiHK+D0FZlZy6JnCNej9kG0MwEOI= X-Received: by 2002:a05:6102:2922:b0:4c1:7c38:135 with SMTP id ada2fe7eead31-4c382f9a60dmr7710072137.0.1742204368163; Mon, 17 Mar 2025 02:39:28 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Aditya Toshniwal Date: Mon, 17 Mar 2025 15:08:52 +0530 X-Gm-Features: AQ5f1Jr4FJsS_dsLPTmtL7Q_7C9-HMz268Y00C2_9a6NyQXbCjipRRaYVOLOVz0 Message-ID: Subject: Re: Role based access control discussion To: Dave Page Cc: pgadmin-hackers Content-Type: multipart/alternative; boundary="0000000000007db076063086908d" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007db076063086908d Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi Dave, On Mon, Mar 17, 2025 at 3:00=E2=80=AFPM Dave Page wrote= : > Hi > > On Mon, 17 Mar 2025 at 09:11, Aditya Toshniwal < > aditya.toshniwal@enterprisedb.com> wrote: > >> Hi Dave, >> >> Essentially, the permissions can be based on the menus: >> >> Object Explorer >> >> 1. Manage Server Create/Edit/Remove. >> 2. Create database object (user could still be able to create using >> query tool) >> >> Definitely not the second one. We shouldn't do anything that is enforced > in the database server - it's unlikely the two permissions systems will > remain in sync for more than a few minutes, and we shouldn't be duplicati= ng > server functionality anyway. > Yeah. So should I proceed with the implementation? > > >> Tools >> >> 1. Tool access like query tool, backup, etc. >> >> Storage Manager: >> >> 1. Create/Edit/Delete file. >> 2. Create/Edit/Delete folders. >> >> >> On Thu, Mar 13, 2025 at 8:47=E2=80=AFPM Aditya Toshniwal < >> aditya.toshniwal@enterprisedb.com> wrote: >> >>> >>> >>> On Thu, Mar 13, 2025 at 7:25=E2=80=AFPM Dave Page w= rote: >>> >>>> >>>> >>>> On Thu, 13 Mar 2025 at 13:19, Aditya Toshniwal < >>>> aditya.toshniwal@enterprisedb.com> wrote: >>>> >>>>> >>>>> >>>>> On Thu, Mar 13, 2025 at 4:54=E2=80=AFPM Dave Page = wrote: >>>>> >>>>>> >>>>>> >>>>>> On Thu, 13 Mar 2025 at 11:07, Aditya Toshniwal < >>>>>> aditya.toshniwal@enterprisedb.com> wrote: >>>>>> >>>>>>> Hi Dave, >>>>>>> >>>>>>> On Thu, Mar 13, 2025 at 4:27=E2=80=AFPM Dave Page wrote: >>>>>>> >>>>>>>> >>>>>>>> >>>>>>>> On Thu, 13 Mar 2025 at 10:26, Aditya Toshniwal < >>>>>>>> aditya.toshniwal@enterprisedb.com> wrote: >>>>>>>> >>>>>>>>> Hi Dave, >>>>>>>>> >>>>>>>>> On Thu, Mar 13, 2025 at 3:36=E2=80=AFPM Dave Page >>>>>>>>> wrote: >>>>>>>>> >>>>>>>>>> Hi >>>>>>>>>> >>>>>>>>>> On Thu, 13 Mar 2025 at 06:16, Aditya Toshniwal < >>>>>>>>>> aditya.toshniwal@enterprisedb.com> wrote: >>>>>>>>>> >>>>>>>>>>> Hi Hackers, >>>>>>>>>>> >>>>>>>>>>> I have started looking into a feature where users have requeste= d >>>>>>>>>>> for custom roles. The roles can then be assigned permissions. H= ere's what I >>>>>>>>>>> think how it can be done: >>>>>>>>>>> >>>>>>>>>>> 1. Create a framework for roles based access control. >>>>>>>>>>> 2. Allow adding/editing/deleting roles from UI. >>>>>>>>>>> 3. User management dialog can be converted to a tab to get >>>>>>>>>>> extra space for other stuff. >>>>>>>>>>> 4. pgAdmin can have some predefined permissions. The >>>>>>>>>>> permissions can then be used to validate at the API levels a= nd UI. >>>>>>>>>>> 5. New permissions cannot be added from UI as it will >>>>>>>>>>> require code changes. They can be added based on user reques= ts. >>>>>>>>>>> 6. Admin can allow these permissions to the roles and roles >>>>>>>>>>> can be assigned to users. >>>>>>>>>>> 7. Permissions will be used to >>>>>>>>>>> 8. Admin role remains static with no changes allowed. >>>>>>>>>>> >>>>>>>>>>> Let me know your thoughts on this. If everything looks good the= n >>>>>>>>>>> I will proceed. >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> What permissions would we support initially? >>>>>>>>>> >>>>>>>>> >>>>>>>>> Based on https://github.com/pgadmin-org/pgadmin4/issues/7310, we >>>>>>>>> can start with not allowing users to register a server. We'll sta= rt 1 or 2 >>>>>>>>> may be, the intention is to create a framework which will allow u= s to keep >>>>>>>>> adding permissions on future requests. >>>>>>>>> >>>>>>>> >>>>>>>> The reason I ask is that there's no point in creating a framework >>>>>>>> if we just end up with a single permission for adding/removing ser= vers. I >>>>>>>> think it makes sense to be sure there are likely to be other permi= ssions >>>>>>>> before committing to something likely to be a lot more complex tha= n just >>>>>>>> adding an attribute to a user. >>>>>>>> >>>>>>> >>>>>>> I understand, but there have been many user requests for custom >>>>>>> roles. I agree that adding a complex thing like RBAC just for one s= ingle >>>>>>> permission is an overkill. But based on my past experience - users = will >>>>>>> come up with more permissions once they see that they can tweak the >>>>>>> permissions now. >>>>>>> What do you suggest we can do? >>>>>>> >>>>>> >>>>>> I do agree, there is the possibility for additional roles to come up= , >>>>>> however, I'm struggling to think what makes sense right now. RBAC ac= cess to >>>>>> tools like psql or the Query Tool don't make much sense - if you can= login >>>>>> to the database server, then there's nothing to stop you just runnin= g psql >>>>>> anyway and bypassing any RBAC we might implement. I suppose there mi= ght be >>>>>> an argument that pgAdmin is being used as a "gateway" to a server on= an >>>>>> otherwise inaccessible network, but then I worry that that opens a w= hole >>>>>> other can of worms around locking down ways for users to execute que= ries >>>>>> through pgAdmin that we might never have previously considered to be= a >>>>>> problem. >>>>>> >>>>>> You say there have been many user requests for custom roles. What >>>>>> roles were they asking for? >>>>>> >>>>> Roles similar to what Grafana provides >>>>> https://grafana.com/docs/grafana/latest/administration/roles-and-perm= issions/, >>>>> but majorly restrictions around server nodes. >>>>> >>>> >>>> Many of those aren't relevant to pgAdmin, but one that did stand out i= s >>>> the ability to create/delete folders. That might well be useful to con= trol. >>>> >>> >>> So we have 2-3 now. Let me dig in all the modules if I can find more >>> useful permissions. >>> >>>> >>>> -- >>>> Dave Page >>>> pgAdmin: https://www.pgadmin.org >>>> PostgreSQL: https://www.postgresql.org >>>> pgEdge: https://www.pgedge.com >>>> >>>> >>> >>> -- >>> Thanks, >>> Aditya Toshniwal >>> pgAdmin Hacker | Sr. Staff SDE II | *enterprisedb.com* >>> >>> "Don't Complain about Heat, Plant a TREE" >>> >> >> >> -- >> Thanks, >> Aditya Toshniwal >> pgAdmin Hacker | Sr. Staff SDE II | *enterprisedb.com* >> >> "Don't Complain about Heat, Plant a TREE" >> > > > -- > Dave Page > pgAdmin: https://www.pgadmin.org > PostgreSQL: https://www.postgresql.org > pgEdge: https://www.pgedge.com > > --=20 Thanks, Aditya Toshniwal pgAdmin Hacker | Sr. Staff SDE II | *enterprisedb.com* "Don't Complain about Heat, Plant a TREE" --0000000000007db076063086908d Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi=C2=A0Dave,

On Mo= n, Mar 17, 2025 at 3:00=E2=80=AFPM Dave Page <dpage@pgadmin.org> wrote:
Hi

<= div class=3D"gmail_quote">
On Mon, 17 = Mar 2025 at 09:11, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com&g= t; wrote:
Hi Dave,

Essentially, the permissions can be based on the men= us:

Object Explorer

  1. Manage Server Create/Edit/R= emove.
  2. Create database object (use= r could still be able to create using query tool)
Definitely not the second one. We shouldn't do any= thing that is enforced in the database server - it's unlikely the two p= ermissions systems will remain in sync for more than a few minutes, and we = shouldn't be duplicating server functionality anyway.=C2=A0
=
Yeah. So should I proceed with the implementation?
=
=C2=A0

Tools

  1. Tool access like query tool= , backup, etc.

Storage Manager:

  1. Create/Edit/Delete file.
  2. Create/Edit/Delete folders.=

On Thu, Mar 13, 2025 at 8:47=E2=80=AFPM Aditya Toshniwal <aditya.= toshniwal@enterprisedb.com> wrote:


On Thu, Mar 13, 2025 at 7:25=E2=80= =AFPM Dave Page <= dpage@pgadmin.org> wrote:


On Thu, 13 Mar 2025 = at 13:19, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:=

=

= On Thu, Mar 13, 2025 at 4:54=E2=80=AFPM Dave Page <dpage@pgadmin.org> wrote:


On Thu, 13 Mar 2025 at 11:07, Aditya Toshniwal <aditya.tosh= niwal@enterprisedb.com> wrote:
Hi=C2=A0Dave,

On Thu, Mar 13, 2025 at 4:27= =E2=80=AFPM Dave Page <dpage@pgadmin.org> wrote:


On Thu, 13 Mar= 2025 at 10:26, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> = wrote:
Hi= =C2=A0Dave,

On Thu, Mar 13, 2025 at 3:36=E2=80=AFPM Dave Page <dpage@pgadmin.org>= ; wrote:
Hi

On Thu, 13 Mar 2025 at 06:16, Aditya Toshniwa= l <aditya.toshniwal@enterprisedb.com> wrote:
Hi Hackers,

I h= ave started looking into a feature where users have requested for custom ro= les. The roles can then be assigned permissions. Here's what I think ho= w it can be done:
    Create a framework for roles based access control.
  1. Allow adding/e= diting/deleting roles from UI.
  2. User management dialog can be conver= ted to a tab to get extra space for other stuff.
  3. pgAdmin can have s= ome predefined permissions. The permissions can then be used to validate at= the API levels and UI.
  4. New permissions cannot be added from UI as = it will require code changes. They can be added based on user requests.
  5. Admin can allow these permissions to the roles and roles can be assign= ed to users.
  6. Permissions will be used to=C2=A0
  7. Admin role r= emains static with no changes allowed.
Let me know your thoug= hts on this. If everything looks good then I will proceed.

What=C2=A0permissions would we supp= ort initially?

Based on=C2=A0https://github.com/pgad= min-org/pgadmin4/issues/7310, we can start with not allowing users to r= egister a server. We'll start 1 or 2 may be, the intention is to create= a framework which will allow us to keep adding permissions on future reque= sts.

The reason I ask is = that there's no point in creating a framework if we just end up with a = single permission for adding/removing servers. I think it makes sense to be= sure there are likely to be other permissions before committing to somethi= ng likely to be a lot more complex than just adding an attribute to a user.=
=C2=A0
I understand, but there have been many user requests for= custom roles. I agree that adding a complex thing like RBAC just for one s= ingle permission is an overkill. But based on my past experience - users wi= ll come up with more permissions once they see that they can tweak the perm= issions now.
What do you= suggest we can do?

I do = agree, there is the possibility=C2=A0for additional roles to come up, howev= er, I'm struggling to think what makes sense right now. RBAC access to = tools like psql or the Query Tool don't make much sense - if you can lo= gin to the database server, then there's nothing to stop you just runni= ng psql anyway and bypassing any RBAC we might implement. I suppose there m= ight be an argument that pgAdmin is being used as a "gateway" to = a server on an otherwise inaccessible network, but then I worry that that o= pens a whole other can of worms around locking down ways for users to execu= te queries through pgAdmin that we might never have previously considered t= o be a problem.

You say there have been many user = requests for custom roles. What roles were they asking for?
Roles sim= ilar to what Grafana provides=C2=A0https:/= /grafana.com/docs/grafana/latest/administration/roles-and-permissions/,= but majorly restrictions around server nodes.

Many of those aren't relevant to pgAdmin, b= ut one that did stand out is the ability to create/delete folders. That mig= ht well be useful to control.

=
So we have 2-3 now. Let me di= g in all the modules if I can find more useful permissions.


--
Thanks,
Aditya Toshniw= al
pgAdmin Hacker=C2=A0| Sr. Staff SDE II=C2= =A0| enterprisedb.com
"Don't Complain about Heat, Plant a TREE"


--
Thanks,
Aditya Toshniw= al
pgAdmin Hacker=C2=A0| Sr. Staff SDE II=C2= =A0| enterprisedb.com
"Don't Complain about Heat, Plant a TREE"


--


--
Thanks,
Aditya Toshniw= al
pgAdmin Hacker=C2=A0| Sr. Staff SDE II=C2= =A0| enterprisedb.com
"Don't Complain about Heat, Plant a TREE"
--0000000000007db076063086908d--