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 1tskK3-005DIP-2v for pgadmin-hackers@arkaria.postgresql.org; Thu, 13 Mar 2025 15:18:11 +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 1tskK0-00G8Fw-Bf for pgadmin-hackers@arkaria.postgresql.org; Thu, 13 Mar 2025 15:18:08 +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 1tskJz-00G8FT-RT for pgadmin-hackers@lists.postgresql.org; Thu, 13 Mar 2025 15:18:08 +0000 Received: from mail-ua1-x92d.google.com ([2607:f8b0:4864:20::92d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1tskJw-002dLy-1F for pgadmin-hackers@postgresql.org; Thu, 13 Mar 2025 15:18:05 +0000 Received: by mail-ua1-x92d.google.com with SMTP id a1e0cc1a2514c-868ddc4c6b6so498880241.2 for ; Thu, 13 Mar 2025 08:18:04 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=enterprisedb.com; s=google; t=1741879083; x=1742483883; 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=msIHGIHZtbV/s+U+le8pcPIWk7Og9rETXRjForTC/xo=; b=gXqIiIySnFjErT9VTa88o0Icf189EJwP74df8cNheQFxNZNL6ovbi46YuKqvteN2wt QqHqUYex/6MTYziz7yCefKqhhYqFKRSxXtypt1LohsVjoVFq/9ZY8QLzTYJ4FsIePqvd cahqBtp86HsbZAZPm9PjDZor5UIrfQEqfDqmfd4akwb5DTMhmbIgGq6JtSDjuHKBLwkG RbV1ewp6l2VYbg5iiInmBs9neF8TEfr8Ohn1zvOlqrrmGRTLnDFdn67Mtxx/mIlbBXB3 UApyuhIoLVP8XkauI+svtsf9hnJjeJTT+39MLwruzpCYcs1sYZYNyI1TjeDZpWvQ9BgM Ps4Q== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1741879083; x=1742483883; 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=msIHGIHZtbV/s+U+le8pcPIWk7Og9rETXRjForTC/xo=; b=hE2gttwIfVIPHUXc5aDYhwTNFAJNTtaW/pAw/u2+lChCtNdUqXJPpPYxzNQSn3JeHc Vz5zhgJyE6/a+qiOf71gEazr6mKW42x1K0dniLd+8Bw3xnJrQLfp2Gbv1XbiL2qxPzEG GRIXeVGg9GCOZZmczLSEfDOlUDSd0/w7NeTfrDUKqXUOQ+POaRhuMZLhNlFUSX6cbZeQ 1Sl4VEe46VDUZU1hnlOzAj1KLG+TIUt/Vb/YgJuu/b51RHF4RGoaWIgCSyHZNul5GTRZ +XGRIsj0M4ORnHZDcMA0wjAdGv03klxeFGpUvMlvrECOmMRvc36XfxFJdeLoIUa9pQyi f9+Q== X-Gm-Message-State: AOJu0Yykhkg74L8WWzQR5gUQAOrCcoQVzibkvUmSqadvu3qEFKVJ4kKB NBcEpkRQguYrYf+KGLALiErDhvxv/rdfXO9FbBu9mr+2Fr0asPSEduK8r4L/7oYi+geT9Xbgivf /FtwjAu/bWBVQhbz1VwoFC6893Y3Fe0l/3VpV X-Gm-Gg: ASbGncvckIBfQqCSy/3iO1V0PKgXqAEgiXFtrmdrllU5aC+ExMlI5vZYHzIdc6Rz3P8 CUa2wAP1A9SVouprHZmjp0vbuGGYBDejglzbcCm2IkxvrjIOl5+DLkcuiZIdIEQIHDm5IRNLiW7 TzK1Nd4NlzD5B7Vmb/VHWGj/CNszvKaEz07mNSqFTiSoc8wmeqhuHP31JNGKAh X-Google-Smtp-Source: AGHT+IGbPgtd2o3/geU3zik3HEuFPuoNUnJxpXZwzh9SR8rFxYs/1vbIAk60uj+Uayadu3EFiiJaxm060jlJtqv4DJQ= X-Received: by 2002:a05:6102:3e8e:b0:4c1:86bc:f959 with SMTP id ada2fe7eead31-4c37ec8c423mr441390137.8.1741879083263; Thu, 13 Mar 2025 08:18:03 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Aditya Toshniwal Date: Thu, 13 Mar 2025 20:47:26 +0530 X-Gm-Features: AQ5f1Jrc1qXPJzHPvuE7X354qA0PfbSVtLqqICYrTmlKb0b6x2DpYdfIgUTYDrA Message-ID: Subject: Re: Role based access control discussion To: Dave Page Cc: pgadmin-hackers Content-Type: multipart/alternative; boundary="000000000000000de306303ad4e5" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000000de306303ad4e5 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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 wr= ote: >> >>> >>> >>> 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. 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 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 ke= ep >>>>>> 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 permissi= ons >>>>> before committing to something likely to be a lot more complex than j= ust >>>>> 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 wil= l >>>> 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 acces= s 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 running p= sql >>> anyway and bypassing any RBAC we might implement. I suppose 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 whol= e >>> other can of worms around locking down ways for users to execute querie= s >>> 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-permiss= ions/, >> 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 contro= l. > 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 > > --=20 Thanks, Aditya Toshniwal pgAdmin Hacker | Sr. Staff SDE II | *enterprisedb.com* "Don't Complain about Heat, Plant a TREE" --000000000000000de306303ad4e5 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


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


On Thu, 13 Mar 202= 5 at 13:19, Aditya Toshniwal <aditya.toshniwal@enterprisedb.com> wrot= e:


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:
    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 dig in all the modules if I can find more useful perm= issions.

--
<= div dir=3D"ltr" class=3D"gmail_signature">


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