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 1tsiT2-004xH0-Ux for pgadmin-hackers@arkaria.postgresql.org; Thu, 13 Mar 2025 13:19:21 +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 1tsiT1-00DcD5-NC for pgadmin-hackers@arkaria.postgresql.org; Thu, 13 Mar 2025 13:19:19 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1tsiT1-00DcCw-DZ for pgadmin-hackers@lists.postgresql.org; Thu, 13 Mar 2025 13:19:19 +0000 Received: from mail-vk1-xa2f.google.com ([2607:f8b0:4864:20::a2f]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tsiSx-002ee9-2D for pgadmin-hackers@postgresql.org; Thu, 13 Mar 2025 13:19:19 +0000 Received: by mail-vk1-xa2f.google.com with SMTP id 71dfb90a1353d-523cbce071bso452063e0c.0 for ; Thu, 13 Mar 2025 06:19:16 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1741871955; x=1742476755; 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=gcfyNfP4+IgrIiIjrlyiEdWbvOxRrwAkU1Y54Ip1Occ=; b=XqqDIAKIFhYaiwkq3RxrQynM9MRVuCJwk59ZWDug+QbGbrKpB0QLWleu2jABeVDJ0r nYasazuJoINtGIDNHcGpy1CbnebhLzGrxDefyLiQcoFj5fSqkoNbDas7X4OLVYV85a2e +uiJ7JYaV+nSk7cw1SNYBbRBS8tF7e7cVa5xRKXlf6288/Qe5/eDDEPFVuAGHOK8W0M1 4I9KtZHsP2kwskboEF/pZ9xcX0LzjcCIsQW2p+jvzr19ej33HgP7PRVQd93joAgg0XJA EWvBfU6n4JRcO3Ihs3KKV7FW6GEWbLGJw/oo9t8+G4miaKDKNrEzBnh64hCSKjb3a2J1 v4Ng== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1741871955; x=1742476755; 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=gcfyNfP4+IgrIiIjrlyiEdWbvOxRrwAkU1Y54Ip1Occ=; b=LEgLeVpnBkrruvnuC/xTDOzCS33RQXDnjsfG/j0j2Kh4Z3O8TxExvtKflTlMoDkTnB 8TzbbViGlaomxr8YA6mNCzREVTGcKMe5VEs6G4/cFDS2BcLXcN/pLdnfcQa1N+S9Bw9h /u84OamoQV8l5In3S0PLhcEV71bMiBTRBa3L/lGPKuqYo4kNzQcGIGn03rov0+KWqsmO f5fX6AXWkMgAVVpapE4jyPZL5puB2XDKc3I6o2ybGtWn+cBTKedq9sWnLewhF9bvyOP+ 6BjsYncQVPKd5dRrBI37XStwsuB1cbqCaJ2cSkNKLbq5uOTvQ7VT4y7B6VYWNpF3vYA7 P8KQ== X-Gm-Message-State: AOJu0Yx+5F+8GwDtLBNu+C5pRb8D2el1Vnnb/WVnvuuE1BgSSLQqFoxn /vcozvdVKq+qS7h0ryr0KlJFTq0XMLYA/RkdtNaY9nLjIQVCar36/sJGNvsSDp5u97r5HOoRvnv PkWmROc9Uram1BXq+i21y3KaH7fVeM2Vj0Gj0 X-Gm-Gg: ASbGncs3eF+aeDyc0pi7ZsvnoQo9gL9j7imO2nxzbjLcnaiknD3xUO/3L+N3nJkuX9o I1K0oPieieXPW6LJGG558TW5dc7QpNbUTnoevURk42rpvyzAkOtgPu9hoAT1glE0VfqwVSNb3MT 3J0ZsVLBWbt9Gebkd+ia6+b0+v6Qi4hpTc8ZWYZzPH2f+RslrE+5mgnhaAHI4f X-Google-Smtp-Source: AGHT+IFOC6U9Iry3mB6mxoS042BOLRGs9+xft7m1QmC/ty9yxFZvvhE4AiUF6fVL5VJXxUp9q8EZfEZpDTN6Q3LrKFs= X-Received: by 2002:a05:6122:310c:b0:51f:4151:4834 with SMTP id 71dfb90a1353d-523e422de37mr19892803e0c.7.1741871954637; Thu, 13 Mar 2025 06:19:14 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Aditya Toshniwal Date: Thu, 13 Mar 2025 18:48:36 +0530 X-Gm-Features: AQ5f1JqMCXyBwJYb9cCL-gT4wgjFMsjTBFW8ydPoCsDbGOSx34J_J1H8yqyUWnw Message-ID: Subject: Re: Role based access control discussion To: Dave Page Cc: pgadmin-hackers Content-Type: multipart/alternative; boundary="00000000000019db160630392b5b" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000019db160630392b5b Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 wr= ote: >> >>> >>> >>> 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 requested for >>>>>> custom roles. The roles can then be assigned permissions. Here's wha= t 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 and UI. >>>>>> 5. New permissions cannot be added from UI as it will require >>>>>> code changes. They can be added based on user requests. >>>>>> 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 then 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 start 1 or 2= may >>>> be, the intention is to create a framework which will allow us 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 servers. I thi= nk >>> it makes sense to be sure there are likely to be other permissions befo= re >>> committing to something likely to be a lot more complex than just addin= g 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 single permissi= on >> is an overkill. But based on my past experience - users will come up wit= h >> 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 access = to > tools like psql or the Query Tool don't make much sense - if you can logi= n > to the database server, then there's nothing to stop you just running psq= l > anyway and bypassing any RBAC we might implement. I suppose there might b= e > 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 whole > other can of worms around locking down ways for users to execute queries > 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-permission= s/, but majorly restrictions around server nodes. > -- > 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" --00000000000019db160630392b5b Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


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


On Thu, 13 Mar 202= 5 at 11:07, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrot= e:
Hi=C2=A0= Dave,

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


On Thu, 13 Mar 2025 at 10:26, Aditya Toshniwal <= ;adi= tya.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 Toshniwal <aditya.toshniwal@enterprisedb.com<= /a>> wrote:
<= div dir=3D"ltr">
Hi Hacke= rs,

I have started looking into a feature w= here users have requested for custom roles. The roles can then be assigned = permissions. Here'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. <= li>User management dialog can be converted to a tab to get extra space for = other stuff.
  3. pgAdmin can have some predefined permissions. The perm= issions 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 c= an be added based on user requests.
  5. Admin can allow these permissio= ns to the roles and roles can be assigned to users.
  6. Permissions wil= l be used to=C2=A0
  7. Admin role remains static with no changes allowe= d.
Let me know your thoughts on this. If everything looks goo= d then I will proceed.

<= div>What=C2=A0permissions would we support initially?

Based= on=C2=A0https://github.com/pgadmin-org/pgadmin4/issues/7310, we= can start with not allowing users to register 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 requests.
<= div>
The reason I ask is that there's no point in creatin= g a framework if we just end up with a single permission for adding/removin= g servers. I think it makes sense to be sure there are likely to be other p= ermissions before committing to something likely to be a lot more complex t= han 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 single permission is an overkill. But= based on my past experience - users will come up with more permissions onc= e they see that they can tweak the permissions now.
What do you suggest we can do?

I do agree, there is the possibility=C2= =A0for additional roles to come up, however, I'm struggling to think wh= at makes sense right now. RBAC access to tools like psql or the Query Tool = don't make much sense - if you can login to the database server, then t= here's nothing to stop you just running psql anyway and bypassing any R= BAC we might implement. I suppose there might be an argument that pgAdmin i= s being used as a "gateway" to a server on an otherwise inaccessi= ble network, but then I worry that that opens a whole other can of worms ar= ound locking down ways for users to execute queries 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 ro= les were they asking for?
Roles similar to wh= at Grafana provides=C2=A0https://grafana.com/docs/grafana/la= test/administration/roles-and-permissions/, but majorly restrictions ar= ound server nodes.



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