Re: Role based access control discussion

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 PM Aditya Toshniwal <
aditya(dot)toshniwal(at)enterprisedb(dot)com> wrote:

>
>
> On Thu, Mar 13, 2025 at 7:25 PM Dave Page <dpage(at)pgadmin(dot)org> wrote:
>
>>
>>
>> On Thu, 13 Mar 2025 at 13:19, Aditya Toshniwal <
>> aditya(dot)toshniwal(at)enterprisedb(dot)com> wrote:
>>
>>>
>>>
>>> On Thu, Mar 13, 2025 at 4:54 PM Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>>
>>>>
>>>>
>>>> On Thu, 13 Mar 2025 at 11:07, Aditya Toshniwal <
>>>> aditya(dot)toshniwal(at)enterprisedb(dot)com> wrote:
>>>>
>>>>> Hi Dave,
>>>>>
>>>>> On Thu, Mar 13, 2025 at 4:27 PM Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>>>>
>>>>>>
>>>>>>
>>>>>> On Thu, 13 Mar 2025 at 10:26, Aditya Toshniwal <
>>>>>> aditya(dot)toshniwal(at)enterprisedb(dot)com> wrote:
>>>>>>
>>>>>>> Hi Dave,
>>>>>>>
>>>>>>> On Thu, Mar 13, 2025 at 3:36 PM Dave Page <dpage(at)pgadmin(dot)org> wrote:
>>>>>>>
>>>>>>>> Hi
>>>>>>>>
>>>>>>>> On Thu, 13 Mar 2025 at 06:16, Aditya Toshniwal <
>>>>>>>> aditya(dot)toshniwal(at)enterprisedb(dot)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 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 permissions
>>>>>> 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 will
>>>>> 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 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 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 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-permissions/,
>>> 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*
> <https://www.enterprisedb.com/>
> "Don't Complain about Heat, Plant a TREE"
>

--
Thanks,
Aditya Toshniwal
pgAdmin Hacker | Sr. Staff SDE II | *enterprisedb.com*
<https://www.enterprisedb.com/>
"Don't Complain about Heat, Plant a TREE"