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 1tu6VV-003dxd-S0 for pgadmin-hackers@arkaria.postgresql.org; Mon, 17 Mar 2025 09:11:38 +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 1tu6VU-009OxJ-57 for pgadmin-hackers@arkaria.postgresql.org; Mon, 17 Mar 2025 09:11:36 +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 1tu6VT-009OvY-Qj for pgadmin-hackers@lists.postgresql.org; Mon, 17 Mar 2025 09:11:35 +0000 Received: from mail-ua1-x933.google.com ([2607:f8b0:4864:20::933]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tu6VP-003Ksi-0w for pgadmin-hackers@postgresql.org; Mon, 17 Mar 2025 09:11:34 +0000 Received: by mail-ua1-x933.google.com with SMTP id a1e0cc1a2514c-86929964ed3so4217772241.0 for ; Mon, 17 Mar 2025 02:11:31 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1742202689; x=1742807489; 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=FCJTEa+o7WCQvAgjYotUaV3NH4bGvCr7J8H/IrjMg7s=; b=KNYTpd67ii6ozxJUqIsV4BP8uVo5kzZSccrrvHaCvyWC2w1/6yHk/8tUbkmhlwiqRP GMZAdcag3kQeH5M5qMDpFrFO+QbLVaL0eh/B4Rm+7+feHHFlqrS1EA1yMKy0b0KslLme r3e2qw4DSbjBv7Hb4HfTJaKIfEbXk7dudm+GubmJbxLl5STouxZSUqDgfLOUTQ/7WEXF dr7e0teWdkgw1Mc+tpB7sPfYwP9vuUNlUN263D5pEQDFxKoLKIgm5RPuHi1y84s0eXAo DgcqQWTqb6FWJcDQBn/KB2rHIl0Sakt+fWPrwMYeYgCpWOBs9S3ZzmSXov20qY7JwGsB uTCA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1742202689; x=1742807489; 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=FCJTEa+o7WCQvAgjYotUaV3NH4bGvCr7J8H/IrjMg7s=; b=SouALT7mXb+WTJrWCqgiEvpnrqD+UpqP+hGPf3IhBBQwSb/lfihvD66LTubpNLOtoR htR+IjDsU5uRB5lf7szc7QqZzaUtw+GMqzAhXKXmBsnsD4VS9uJfFgMsPWXBGXEJqDE3 LjphedGU9S9emsCyP02P5zZsSzPGQVOizkUPqHHlm2t4H80vdi149t1/ZDniqXVu8gF4 PFJ2bkVkUfoJF6WVWegpD/8zP8R6ZopTYd8Fu4Og878jBaq6Nc2wMeXo3C3b386iAX2b Im+8hBXZQUrFgoQRqAGcsNrvD8fxZ4RSHkA+Rxj4mAKlESh8iD+yAkFEeGBa8T3F0QJL Vk8g== X-Gm-Message-State: AOJu0Yxpqwm1UUHYe2f+4jFj5ieTLiPMv6TfIjVGWHytzI1zhoQEhVR+ epckKsyHWvHgd/mM4M9DQq3oMm0RK8trje8tDToByzVKLlcgtBYtCoh93g21A+Bob7ARVirTZDs 7PmWC3NtUqXFTsgMNY+D0AFabTIx5iqrg/FtuOHO7WijtJpOyEgpz X-Gm-Gg: ASbGncvaAXmQXmevUNS1LiClVSKhbwNOTVg0VDotSaQCMW6yu7+kB7CS6yUF4KPDuWX wDIksclLmI6eXCWsw75MRaOKICznhosFROE2SR36q+eV9rXo7FXpU5oxE085/Y5rz3zS7OB1l1v af9ZbO8z+ZkjOkXO8cwnIy9enU4YFCo4GDV8Ir77Jdhb1yr6RBZlf0xd05OLtu X-Google-Smtp-Source: AGHT+IFIf0tLaoGbezeCx8hODb6uHkNY2XrjF/oY4wuCiye1dXK6Gmjxfa/vV58TPVi4ga4lGBKhCE1lF9geGGMPeVk= X-Received: by 2002:a05:6102:8084:b0:4c3:6979:2ef with SMTP id ada2fe7eead31-4c38322a379mr7130907137.21.1742202689142; Mon, 17 Mar 2025 02:11:29 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Aditya Toshniwal Date: Mon, 17 Mar 2025 14:40:48 +0530 X-Gm-Features: AQ5f1JqXqshq40lSYoG7eV7nXqOeKt-mehD6kcHmq3vs8h07gdbG0I01qr9po2I Message-ID: Subject: Re: Role based access control discussion To: Dave Page Cc: pgadmin-hackers Content-Type: multipart/alternative; boundary="00000000000069e14f0630862c42" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000069e14f0630862c42 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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) 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 wro= te: > >> >> >> 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 w= rote: >>> >>>> >>>> >>>> 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 requested >>>>>>>>> for custom roles. The roles can then be assigned permissions. Her= e'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 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 >>>>>> think it makes sense to be sure there are likely to be other permiss= ions >>>>>> before committing to something likely to be a lot more complex than = 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 single >>>>> 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 >>>>> 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 acce= ss to >>>> tools like psql or the Query Tool don't make much sense - if you can l= ogin >>>> to the database server, then there's nothing to stop you just running = psql >>>> anyway and bypassing any RBAC we might implement. I suppose there migh= t be >>>> an argument that pgAdmin is being used as a "gateway" to a server on a= n >>>> otherwise inaccessible network, but then I worry that that opens a who= le >>>> other can of worms around locking down ways for users to execute queri= es >>>> 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 role= s >>>> were they asking for? >>>> >>> Roles similar to what Grafana provides >>> https://grafana.com/docs/grafana/latest/administration/roles-and-permis= sions/, >>> but majorly restrictions around server nodes. >>> >> >> Many of those aren't relevant to pgAdmin, but one that did stand out is >> the ability to create/delete folders. That might well be useful to contr= ol. >> > > 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" > --=20 Thanks, Aditya Toshniwal pgAdmin Hacker | Sr. Staff SDE II | *enterprisedb.com* "Don't Complain about Heat, Plant a TREE" --00000000000069e14f0630862c42 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi Dave,

Essentially, the permissions can be based on the = menus:

Object Ex= plorer

  1. Manage = Server Create/Edit/Remove.
  2. Create = database object (user could still be able to create using query tool)

Tools

  1. Tool ac= cess like query tool, backup, etc.

Storage M= anager:

  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, Ma= r 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@enterpr= isedb.com> wrote:


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


On Thu, 13 Mar 2025 at 11:07, Adity= a Toshniwal <aditya.toshniwal@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@ent= erprisedb.com> wrote:
Hi=C2=A0Dave,

<= div dir=3D"ltr" class=3D"gmail_attr">On Thu, Mar 13, 2025 at 3:36=E2=80=AFP= M Dave Page <dpag= e@pgadmin.org> 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 req= uested for custom roles. The roles can then be assigned permissions. Here&#= 39;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 and UI.
  4. New permissions cannot = be added from UI as it will require code changes. They can be added based o= n user requests.
  5. Admin can allow these permissions to the roles and= roles can be assigned to users.
  6. Permissions will be used to=C2=A0<= /li>
  7. Admin role remains static with no changes allowed.
Le= t me know your thoughts on this. If everything looks good then I will proce= ed.

What=C2=A0permi= ssions would we support initially?

<= /div>
Based on=C2=A0htt= ps://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 in= tention is to create a framework which will allow us to keep adding permiss= ions 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 think = it makes sense to be sure there are likely to be other permissions before c= ommitting to something likely to be a lot more complex than just adding an = attribute to a user.
=C2=A0
I understand, but there have been ma= ny 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 e= xperience - users will come up with more permissions once they see that the= y can tweak the permissions now.
What do you suggest we can do?
=
I do agree, there is the possibility=C2=A0for additional rol= es 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 s= ense - if you can login to the database server, then there's nothing to= stop you just running psql anyway and bypassing any RBAC we might implemen= t. I suppose there might be an argument that pgAdmin is being used as a &qu= ot;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 way= s for users to execute queries through pgAdmin that we might never have pre= viously 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=C2=A0https://grafana.com/docs/grafana/latest/administration/roles-a= nd-permissions/, but majorly restrictions around server nodes.

Many of those aren't re= levant to pgAdmin, but one that did stand out is the ability to create/dele= te folders. That might well be useful to control.

So we have 2-3 now. Let me dig in all the modules if I can f= ind 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"
--00000000000069e14f0630862c42--