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 1tu7Pv-003nh2-Pi for pgadmin-hackers@arkaria.postgresql.org; Mon, 17 Mar 2025 10:09: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 1tu7Pt-00A34V-Va for pgadmin-hackers@arkaria.postgresql.org; Mon, 17 Mar 2025 10:09:53 +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 1tu7Pt-00A34M-Lo for pgadmin-hackers@lists.postgresql.org; Mon, 17 Mar 2025 10:09:53 +0000 Received: from mail-lf1-x131.google.com ([2a00:1450:4864:20::131]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tu7Po-003LVI-2O for pgadmin-hackers@postgresql.org; Mon, 17 Mar 2025 10:09:52 +0000 Received: by mail-lf1-x131.google.com with SMTP id 2adb3069b0e04-5495078cd59so4632181e87.1 for ; Mon, 17 Mar 2025 03:09:49 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pgadmin.org; s=google; t=1742206189; x=1742810989; 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=mJvWgnVOqpc6aWoGt/2FUPgPf0NdOCjGwlLfFNlybcQ=; b=o8wPDuLsmC5AIt137xKAgDCtSINot2wPELT733sWbAkBnGSI+3YQtl95O7SFcT1Vvy swNq4Plkw+QCmRFS0HaDz5vz8zBsKz5hCKEjpjwrgRjL9ahhnYtDP1xLw0DDiNi4xXBX KCyK/y3nu7FQCISIRkq4jevkpLx2noCtPvS+vvsKaSrmZ0EFGTYHjleFAsQCPKD51Xew 2YL19P82xv95Np/V1Spbyi3kiTKQcsxyzYXltwmDjIApYDCPYuMzRhha09yndI1YUF+n NBpx3pFi/7PlaxXAHH8WKoiMNWYPpDn3S4Z5qM7EOFAGnCgr/16FEQHcFrSDlHHRNraP kSxQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1742206189; x=1742810989; 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=mJvWgnVOqpc6aWoGt/2FUPgPf0NdOCjGwlLfFNlybcQ=; b=JxXvtPjsG2RIvqJYny8OiB5JaafmW6XgABWi6vA4gkpzLFpxpXogCuwtFnsw8hDzBI sSOvkNCgWui8SCSc7P8114p7wy9jEOz2HnfdB3jJwY7wMFTYohL1Pmt8uNtDX0mMr432 CLcPf+hskb5mOxFlmVHkjFO0yGuTUDk/ih1GBYjLkoXSD1Cs/fI+xHrZ5axZz+U8WNhS IG5qpD1n555YWOhfPCwdrzDU9E6bYlyL8AhqQKPKF31T3J0Tiu0CrLEoVf/b8MxLPrZf xsWpG66786op1+sgoWXbE2eJf+k45ym2nIhQNocIi3s3Yc1dzwnjhgSyDtjM0FHK95u3 N/XQ== X-Gm-Message-State: AOJu0Yy6Z0K5mao3HAeE9ng4PL43yWPaamOvhXGgFUvZ8mngz0rWDLpx FXRhmt4QVrlK3tcyQjfXcnFHTJJqjIlj/lTw4WMhWGNzITbp3BNwOV6pXMm8Jweh0HsFJ40l/fQ mGkOr8Wd8fFMdcYvM5t+v5M5lUIRDhdGwRt2tZUBTaxRBY+F1Kg== X-Gm-Gg: ASbGncvh1BzV/PhYrY2vpTRD0vZ7cHAi9suOQsBpYugj3g9EFyYWj6shMc7lvuyivpP 1S2A6aTERcSvPbBhRB4odZv66he4j7kiUVwGY8PfcbdRkC0VuvU72rLcc0B+znbqR3AH8rxFq0a CU+MwKWbpVWNmZCVpgpVnTFpZCj77lQ72ultjAtCmieO5dTtkC/wsWq5GeUGfF X-Google-Smtp-Source: AGHT+IGK250rkZpKiQMH5ZGyKboSzyKE4uBI2EDrknr+6ih9kxc4obaXymLe/TRS1KbHHLkRHWH2MzmI0M2bMynd7lc= X-Received: by 2002:a05:6512:2c0a:b0:549:b0fa:6733 with SMTP id 2adb3069b0e04-549c396e405mr6032658e87.37.1742206188593; Mon, 17 Mar 2025 03:09:48 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Dave Page Date: Mon, 17 Mar 2025 10:09:37 +0000 X-Gm-Features: AQ5f1Jpn-9_JY4aOnEyvzR1yCgaiT9F4fK7_YE0AejmaShgKmMA0jujvsNjBi6o Message-ID: Subject: Re: Role based access control discussion To: Aditya Toshniwal Cc: pgadmin-hackers Content-Type: multipart/alternative; boundary="000000000000ff4f83063086fca8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ff4f83063086fca8 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Hi On Mon, 17 Mar 2025 at 09:39, Aditya Toshniwal < aditya.toshniwal@enterprisedb.com> wrote: > Hi Dave, > > On Mon, Mar 17, 2025 at 3:00=E2=80=AFPM Dave Page wro= te: > >> 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 enforce= d >> 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 duplicat= ing >> server functionality anyway. >> > Yeah. So should I proceed with the implementation? > If that=E2=80=99s what Akshay wants you working on, then sure :-) >> >>> 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 = 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 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 per= missions. >>>>>>>>>>>> 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. 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 reque= sts. >>>>>>>>>>>> 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 st= art 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 se= rvers. I >>>>>>>>> think it makes sense to be sure there are likely to be other perm= issions >>>>>>>>> before committing to something likely to be a lot more complex th= an 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= will >>>>>>>> come up with more permissions once they see that they can tweak th= e >>>>>>>> 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. RB= AC >>>>>>> access 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 su= ppose >>>>>>> there might 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 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-per= missions/, >>>>>> 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 >>>>> control. >>>>> >>>> >>>> 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 >> >> > > -- > Thanks, > Aditya Toshniwal > pgAdmin Hacker | Sr. Staff SDE II | *enterprisedb.com* > > "Don't Complain about Heat, Plant a TREE" > --000000000000ff4f83063086fca8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi

On Mon, 17 Mar 2025 at 09:39, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com= > wrote:
Hi=C2=A0= Dave,

On Mon, Mar 17, 2025 at 3:00=E2=80=AFPM Dave Page <dpage@pgadmin.org> wrot= e:
Hi

=
On Mon, 17= Mar 2025 at 09:11, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com&= gt; wrote:
Hi Dave,

Essential= ly, 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)
<= div>Definitely not the second one. We shouldn't do anything that is enf= orced in the database server - it's unlikely the two permissions system= s 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?


If that=E2=80=99s what Akshay wants you working on, then sur= e :-)=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:
  1. Create a framework for roles based access control.
  2. Allow adding/editing/deleting roles fro= m UI.
  3. User management dial= og can be converted to a tab to get extra space for other stuff.
  4. pgAdmin can have some predefined per= missions. The permissions can then be used to validate at the API levels an= d UI.
  5. New permissions cann= ot be added from UI as it will require code changes. They can be added base= d 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=C2=A0
  8. Admin role remai= ns static with no changes allowed.
Let me know your thoughts on this. If everything looks good = then I will proceed.

What=C2=A0permissions would we support initially?

Based o= n=C2=A0https://github.com/p= gadmin-org/pgadmin4/issues/7310, we can start with not allowing users t= o register a server. We'll start 1 or 2 may be, the intention is to cre= ate a framework which will allow us to keep adding permissions on future re= quests.

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 some= thing likely to be a lot more complex than just adding an attribute to a us= er.
=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 on= e single 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 p= ermissions now.
What do = you suggest we can do?

I = do agree, there is the possibility=C2=A0for additional roles to come up, ho= wever, 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= login to the database server, then there's nothing to stop you just ru= nning psql anyway and bypassing any RBAC we might implement. I suppose ther= e might be an argument that pgAdmin is being used as a "gateway" = to a server on an otherwise inaccessible network, but then I worry that tha= t opens a whole other can of worms around locking down ways for users to ex= ecute queries through pgAdmin that we might never have previously considere= d to be a problem.

You say there have been many us= er requests for custom roles. What roles were they asking for?
<= /div>
Roles = similar to what Grafana provides=C2=A0https://grafana.com/docs/grafana/lates= t/administration/roles-and-permissions/, but majorly restrictions aroun= d server nodes.

Man= y of those aren't relevant to pgAdmin, but one that did stand out is th= e ability to create/delete 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 Toshniwal=
pgAdmin Hacker=C2=A0| Sr= . Staff SDE II=C2= =A0| enterprisedb.com
&= quot;Don't Complain about Heat, Plant a TREE"
<= /div>


--
Thanks,
Aditya Toshniwal=
pgAdmin Hacker=C2=A0| Sr= . Staff SDE II=C2= =A0| enterprisedb.com
&= quot;Don't Complain about Heat, Plant a TREE"
<= /div>


--


--
Thanks,
Aditya Toshniwal=
pgAdmin Hacker=C2=A0| Sr= . Staff SDE II=C2= =A0| enterprisedb.com
&= quot;Don't Complain about Heat, Plant a TREE"
<= /div>
--000000000000ff4f83063086fca8--