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 1tu7Rh-003o0z-Pw for pgadmin-hackers@arkaria.postgresql.org; Mon, 17 Mar 2025 10:11:46 +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 1tu7Rf-00A4I2-GS for pgadmin-hackers@arkaria.postgresql.org; Mon, 17 Mar 2025 10:11:43 +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 1tu7Rf-00A4Hs-6M for pgadmin-hackers@lists.postgresql.org; Mon, 17 Mar 2025 10:11:43 +0000 Received: from mail-vk1-xa2c.google.com ([2607:f8b0:4864:20::a2c]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tu7RZ-003LW8-2o for pgadmin-hackers@postgresql.org; Mon, 17 Mar 2025 10:11:41 +0000 Received: by mail-vk1-xa2c.google.com with SMTP id 71dfb90a1353d-523de538206so1632501e0c.2 for ; Mon, 17 Mar 2025 03:11:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1742206297; x=1742811097; 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=d97BzyuDxtj7lo8MUMR8wdQUHq/mVY2F1iesimk1Y+4=; b=i9juFuyim56pwZJLngbNocVXJSiZn42VDyESYUp66/kixtfyOdQDoMQQbHp5EKTZlL gn31GW16NkjHGN+vuhm+NH6GuFzkzzZJSZAHprmgACFOR2eQudjS3AK7BvLXPN2yl6ma LEs4XwfHVEQeddxqZPBpiRQveo7svRxECc4tKmLVrbtcbF2I2MZn22YirHVyBT5RIp9P aRhqHjfusv7esMjLJ3XPuDaaIJrmdEhnt0szb9ZYPJ/yusXaQyz2K2kRnHLoVSzo06YI +LJu6I9Le9Id0mWsGFS/Pa3qZgsaAgIF9C0/mloqW95Ii8aoqQn3aE0T8tAXKTQros9/ 6xAw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1742206297; x=1742811097; 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=d97BzyuDxtj7lo8MUMR8wdQUHq/mVY2F1iesimk1Y+4=; b=g1UQXxdHGGpKe4N/+oWtK/qUfT7J7/XENXxMoHHLw+colz4tDAveygUUsG7yCiiXa7 ztsOsVUNlGCO1AeXInf+gRTioTrAB03uQdd8D7PTlz8PdK6upT7uU9WBcrX0fPq3ICEp pwlVV+NmuKIqdLErzbJyAkB7Gnr4prjSH+M/hdUcsOQL5pvuumtom3HMOBBnwU+OGlZK dHLGtEggWeIrogkI5O5sdEiR4E76tqs4pRWNQjvccgMWxiqW78MVCjf+eyEgyuGmyGiS LMHXHrBemempZkiyVnRla3AoEdrVBUSzrcXT8CINHUFx8IoycOLcsoiBdH4/a1ILm/uH s0JQ== X-Gm-Message-State: AOJu0YwXaCVqORFn9NAysbbmvwmdChq+HYSiERo0rlOucHrK0oHD+BYa eK2+Ijb5j8Xz228H86FVT9vkhJAOy+/OQ0Q6WfJvGT5zI/uu2C1NrSDHRaFOrhBWJ50cDMB3V9M +B6hK8LynCCLSphdBZrU8VKNvH6vXqG15uMCr X-Gm-Gg: ASbGncttVncezDgojkFcpf6gQGVhbgf28P7YhoVvIXGmJkHDwL7OIfcN6ggJhiHU0x/ lqyFprmC/g5x0VIlPpOK1ykZNMC9vxUItqJj7/xFiBjoWzH7oeVOxosFX7y1lEYc2LCafGDx7iw RHSAiUiR8YQ2QLhmF3awo3x7edE1QqklNE17b5BLF5ZXBDa3PT1SwLLGx8aQBr X-Google-Smtp-Source: AGHT+IEIFw4aTfqZW4zCXb15nTHnT8oRWTfjtO1NAGt3WhYusVlAI73cYY0Nm6Xp3pXGinRz7Fa6J7Pwe3PICoP8aKE= X-Received: by 2002:a05:6122:17a7:b0:518:865e:d177 with SMTP id 71dfb90a1353d-524499ca7d5mr5978350e0c.9.1742206296928; Mon, 17 Mar 2025 03:11:36 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Aditya Toshniwal Date: Mon, 17 Mar 2025 15:41:00 +0530 X-Gm-Features: AQ5f1JraPNzDU8kG_pjGP5AlNcrrrSyC5JSWuC9SuymRQ1xPsPOf8YHWqd7CVow Message-ID: Subject: Re: Role based access control discussion To: Dave Page Cc: pgadmin-hackers Content-Type: multipart/alternative; boundary="0000000000007452a806308703e1" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000007452a806308703e1 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Mon, Mar 17, 2025 at 3:39=E2=80=AFPM Dave Page wrote= : > 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 wr= ote: >> >>> 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 sys= tems >>> will remain in sync for more than a few minutes, and we shouldn't be >>> duplicating server functionality anyway. >>> >> Yeah. So should I proceed with the implementation? >> > > > If that=E2=80=99s what Akshay wants you working on, then sure :-) > I was waiting for confirmation if the pgAdmin team would accept it or not := ) > > >>> >>>> 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 pe= rmissions. >>>>>>>>>>>>> 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 requ= ests. >>>>>>>>>>>>> 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'l= l start 1 or >>>>>>>>>>> 2 may be, the intention is to create a framework which will all= ow us to >>>>>>>>>>> keep adding permissions on future requests. >>>>>>>>>>> >>>>>>>>>> >>>>>>>>>> The reason I ask is that there's no point in creating a framewor= k >>>>>>>>>> if we just end up with a single permission for adding/removing s= ervers. I >>>>>>>>>> think it makes sense to be sure there are likely to be other per= missions >>>>>>>>>> before committing to something likely to be a lot more complex t= han 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 - user= s will >>>>>>>>> come up with more permissions once they see that they can tweak t= he >>>>>>>>> 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. R= BAC >>>>>>>> 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 s= uppose >>>>>>>> there might be an argument that pgAdmin is being used as a "gatewa= y" 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 user= s to >>>>>>>> execute queries through pgAdmin that we might never have previousl= y >>>>>>>> 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-pe= rmissions/, >>>>>>> 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 t= o >>>>>> 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" >> > --=20 Thanks, Aditya Toshniwal pgAdmin Hacker | Sr. Staff SDE II | *enterprisedb.com* "Don't Complain about Heat, Plant a TREE" --0000000000007452a806308703e1 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


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

On Mon, 17 Mar 2025 at 09:39, Aditya Toshniwal <aditya.toshniwal@enter= prisedb.com> wrote:
Hi=C2=A0Dave,

On Mon, Mar 17, 2025 at 3:00=E2=80=AFPM = Dave Page <dpage@= pgadmin.org> 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)
<= 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

I was waiting for confi= rmation if the pgAdmin team would accept it or not :)

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


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