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 1tu6oC-003gye-Kl for pgadmin-hackers@arkaria.postgresql.org; Mon, 17 Mar 2025 09:30:56 +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 1tu6oA-009ZXW-K2 for pgadmin-hackers@arkaria.postgresql.org; Mon, 17 Mar 2025 09:30:54 +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 1tu6o9-009ZQa-Bt for pgadmin-hackers@lists.postgresql.org; Mon, 17 Mar 2025 09:30:54 +0000 Received: from mail-lf1-x130.google.com ([2a00:1450:4864:20::130]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tu6o6-003IUJ-0J for pgadmin-hackers@postgresql.org; Mon, 17 Mar 2025 09:30:52 +0000 Received: by mail-lf1-x130.google.com with SMTP id 2adb3069b0e04-54954fa61c8so4158817e87.1 for ; Mon, 17 Mar 2025 02:30:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pgadmin.org; s=google; t=1742203848; x=1742808648; 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=sL1H5AiUfEZFjfbxSYgQLDy0KeeKDohEF2izZbxVkBU=; b=ekTweEhPtUhM8cXnbKPhmpJQ1M858uV9ujPUb3bIJ8LY8mo9Mu/eT5jnouMqqluENY QYKe0dJ5K6AV+u1EZg7fcG0OrOOW6GJHjXrs0c7t+fXefBin9H+1aTcIkA+BKo0YG9N0 Ic7rQGRZluZbWLpzc+A8gDlP1R2a7VhwFEjVkGHDXAHrEq5uTh6KAYbN7a85QMSx3o5N tYjmCcKyKmtIxRFiC969DJg93yF8SI8e4M7eOkQHGfFA9vgnF0OSjyCtn1tg7q0sB8+O rzPjBwmKwkXBr+TrPqsGtr+JowL0C95WoVkT74DGRxj2lNlNeQeN9xlLFm3s4uasrVLl FSww== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1742203848; x=1742808648; 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=sL1H5AiUfEZFjfbxSYgQLDy0KeeKDohEF2izZbxVkBU=; b=NLDXwW1FKq49J59XU3Yc1Rd4XJVJs/1aB1AnWDhOaEKt8HBDvje2X59/0JSE7kZncz JKblXyJVEviG6kQDLOw0w99YjNxYdFWWUksXusASZaz6Kj2DyJntmBjIjH7nHZam03LQ FcNFeHnF/M7TuVhZzUFNll6vWoBhyzgfKuNfFlUs8Dj3RLpJIseeMY0n5YULaG5T9N3Y gzpP72SqOdMMLylLXEg/9elD7j/vIltki9j94WPIW9sJO65JSLak4si7e5O9PCXrPOgG ZdQFGCJYjiYyoCZMu7GnzEcVIOnZV3La/Lsx3OyI6Ka258CvFcCw2hEeC2QjxF6fF76h KFfA== X-Gm-Message-State: AOJu0YxmH6nxBeV2C7iXpl4/y0GIeIvWWHfdokTXeDsZkFl0qRK23sxf PLZyMG3VZ5HWVcGGgw+YoraPWP/MUbCXMok+c7JvzNxPY0Qn0VfJR/SGLhdThm/7GG8FsIfy4O5 BXM/pnAOttY4zInwlT1GSihJpqxVZosx1MgbFiUVb08wivTyT8w== X-Gm-Gg: ASbGnctMgRslyvph8FUQZzYMvnXxtrdJ+aeNGRrkmderv74GYQT+MefDYc9U/bO1SwA C1ehfskSXWDuryLiqk/HEKUJ6XxBYsHxSM8W1n063iZbgczaT/mqWxu0c232f9Xoal6PzH+8048 fgZ0bRnl7wE7nmFQgAc0aAZGPKPCphHoTG4UqcHc8= X-Google-Smtp-Source: AGHT+IEjtHW6mYqJATwkEXVw4+KyrIc/PJvlv4NiiXCuCzFm/dXwMZRW67Zr95Hn3hUHXiNT0avjNG7SgvpIMAnQQOo= X-Received: by 2002:a05:6512:696:b0:549:8b24:9896 with SMTP id 2adb3069b0e04-549c360f4a4mr6396166e87.0.1742203847419; Mon, 17 Mar 2025 02:30:47 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Dave Page Date: Mon, 17 Mar 2025 09:30:35 +0000 X-Gm-Features: AQ5f1JqMzM3B0ipE9QWH1N3huDJT_x-ZwEEJUMtaqw4hhCMOr_4oRh-sA2EcZu8 Message-ID: Subject: Re: Role based access control discussion To: Aditya Toshniwal Cc: pgadmin-hackers Content-Type: multipart/alternative; boundary="00000000000073c3ac063086710c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000073c3ac063086710c Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 duplicating server functionality anyway. > 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 wr= ote: >> >>> >>> >>> 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 requested >>>>>>>>>> for custom roles. The roles can then be assigned permissions. He= re'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 an= d 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 star= t 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 i= f >>>>>>> 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 permis= sions >>>>>>> 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 si= ngle >>>>>> permission is an overkill. But based on my past experience - users w= ill >>>>>> 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 acc= ess 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 running= psql >>>>> anyway and bypassing any RBAC we might implement. I suppose there mig= ht 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 wh= ole >>>>> other can of worms around locking down ways for users to execute quer= ies >>>>> 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-permi= ssions/, >>>> 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 cont= rol. >>> >> >> 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" > --=20 Dave Page pgAdmin: https://www.pgadmin.org PostgreSQL: https://www.postgresql.org pgEdge: https://www.pgedge.com --00000000000073c3ac063086710c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

On Mon, 17 Mar 20= 25 at 09:11, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrote:
= Hi Dave,

Essentially, the permissions can b= e based on the menus:

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
= =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:<= br>


<= div class=3D"gmail_quote">
On Thu, 13 = Mar 2025 at 13:19, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com&g= t; 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 Tosh= niwal <aditya.toshniwal@enterprisedb.com> wrote:
Hi=C2=A0Dave,

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


On Thu, 13 Mar 2025 at 10:26, Aditya Toshniwal <aditya.toshniwa= l@enterprisedb.com> wrote:
Hi=C2=A0Da= ve,

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

On Thu, 13 M= ar 2025 at 06:16, Aditya Toshniwal <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"


--
--00000000000073c3ac063086710c--