Re: Role based access control discussion

[Aditya Toshniwal]

On Thu, Mar 13, 2025 at 7:25 PM Dave Page  wrote:

>
>
> On Thu, 13 Mar 2025 at 13:19, Aditya Toshniwal <
> aditya.toshni...@enterprisedb.com> wrote:
>
>>
>>
>> On Thu, Mar 13, 2025 at 4:54 PM Dave Page  wrote:
>>
>>>
>>>
>>> On Thu, 13 Mar 2025 at 11:07, Aditya Toshniwal <
>>> aditya.toshni...@enterprisedb.com> wrote:
>>>
 Hi Dave,

 On Thu, Mar 13, 2025 at 4:27 PM Dave Page  wrote:

>
>
> On Thu, 13 Mar 2025 at 10:26, Aditya Toshniwal <
> aditya.toshni...@enterprisedb.com> wrote:
>
>> Hi Dave,
>>
>> On Thu, Mar 13, 2025 at 3:36 PM Dave Page  wrote:
>>
>>> Hi
>>>
>>> On Thu, 13 Mar 2025 at 06:16, Aditya Toshniwal <
>>> aditya.toshni...@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 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*

"Don't Complain about Heat, Plant a TREE"

Re: Role based access control discussion

[Aditya Toshniwal]

On Thu, Mar 13, 2025 at 4:54 PM Dave Page  wrote:

>
>
> On Thu, 13 Mar 2025 at 11:07, Aditya Toshniwal <
> aditya.toshni...@enterprisedb.com> wrote:
>
>> Hi Dave,
>>
>> On Thu, Mar 13, 2025 at 4:27 PM Dave Page  wrote:
>>
>>>
>>>
>>> On Thu, 13 Mar 2025 at 10:26, Aditya Toshniwal <
>>> aditya.toshni...@enterprisedb.com> wrote:
>>>
 Hi Dave,

 On Thu, Mar 13, 2025 at 3:36 PM Dave Page  wrote:

> Hi
>
> On Thu, 13 Mar 2025 at 06:16, Aditya Toshniwal <
> aditya.toshni...@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 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.


> --
> 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"

Re: Regarding feature #3319

[Yogesh Mahajan]

Hi Dave,

Yes, we can store connection info. We might need to tweak how we create the
query tool connections.
Thanks for the inputs.

Thanks,
Yogesh Mahajan
EnterpriseDB


On Thu, Mar 13, 2025 at 5:07 PM Dave Page  wrote:

> Hi
>
> On Thu, 13 Mar 2025 at 11:35, Yogesh Mahajan <
> yogesh.maha...@enterprisedb.com> wrote:
>
>> Hi Dave,
>>
>> What about the query tools opened with an ad-hoc server, should we just
>> open a query tool with data without connections?
>>
>
> Can we not save the connection parameters used in the JSON (excluding the
> password that we can prompt for of course)?
>
>
>>
>> Thanks,
>> Yogesh Mahajan
>> EnterpriseDB
>>
>>
>> On Thu, Mar 13, 2025 at 4:59 PM Dave Page  wrote:
>>
>>> Hi
>>>
>>> On Thu, 13 Mar 2025 at 11:20, Yogesh Mahajan <
>>> yogesh.maha...@enterprisedb.com> wrote:
>>>
 Hi Dave,

 Couple of follow up questions -

 Thanks,
 Yogesh Mahajan
 EnterpriseDB


 On Thu, Mar 13, 2025 at 4:37 PM Dave Page  wrote:

>
>
> On Tue, 11 Mar 2025 at 08:31, Yogesh Mahajan <
> yogesh.maha...@enterprisedb.com> wrote:
>
>> Hello Team,
>>
>> Couple of more questions arose during discussion -
>>
>> 1.What all tools should we reopen while restoration? It could be
>> Query tool, ERD, Psql, Schema diff as of now may get additional in 
>> future.
>>
>
> Yes :-). Ideally, as much of the original state as possible should be
> restored.
>

 So, should we open the psql without data and schema diff without
 comparison results?

>>>
>>> I don't see that we have any choice for psql. We absolutely should *not*
>>> attempt to automatically re-run user queries.
>>>
>>> For the schema diff, yes, that could be very expensive. The user can
>>> press the button if they want to incur that cost.
>>>
>>>
 Also for the query tools open with an ad-hoc server, should we just
 open a query tool with data without connections?


>
>> 2.Can we use an existing crypt key to encrypt the query data or
>> simply json encoding should be enough?
>>
>
> We're already storing the query history, so we should follow the
> precedent there.
>
 Currently we are storing this data with json encoding done by the
 request module.


> --
> Dave Page
> pgAdmin: https://www.pgadmin.org
> PostgreSQL: https://www.postgresql.org
> pgEdge: https://www.pgedge.com
>
>
>>>
>>> --
>>> Dave Page
>>> pgAdmin: https://www.pgadmin.org
>>> PostgreSQL: https://www.postgresql.org
>>> pgEdge: https://www.pgedge.com
>>>
>>>
>
> --
> Dave Page
> pgAdmin: https://www.pgadmin.org
> PostgreSQL: https://www.postgresql.org
> pgEdge: https://www.pgedge.com
>
>

Re: Role based access control discussion

[Dave Page]

On Thu, 13 Mar 2025 at 13:19, Aditya Toshniwal <
aditya.toshni...@enterprisedb.com> wrote:

>
>
> On Thu, Mar 13, 2025 at 4:54 PM Dave Page  wrote:
>
>>
>>
>> On Thu, 13 Mar 2025 at 11:07, Aditya Toshniwal <
>> aditya.toshni...@enterprisedb.com> wrote:
>>
>>> Hi Dave,
>>>
>>> On Thu, Mar 13, 2025 at 4:27 PM Dave Page  wrote:
>>>


 On Thu, 13 Mar 2025 at 10:26, Aditya Toshniwal <
 aditya.toshni...@enterprisedb.com> wrote:

> Hi Dave,
>
> On Thu, Mar 13, 2025 at 3:36 PM Dave Page  wrote:
>
>> Hi
>>
>> On Thu, 13 Mar 2025 at 06:16, Aditya Toshniwal <
>> aditya.toshni...@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 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.

-- 
Dave Page
pgAdmin: https://www.pgadmin.org
PostgreSQL: https://www.postgresql.org
pgEdge: https://www.pgedge.com

Re: Role based access control discussion

[Dave Page]

Hi

On Thu, 13 Mar 2025 at 06:16, Aditya Toshniwal <
aditya.toshni...@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?

-- 
Dave Page
pgAdmin: https://www.pgadmin.org
PostgreSQL: https://www.postgresql.org
pgEdge: https://www.pgedge.com

Re: Role based access control discussion

[Aditya Toshniwal]

Hi Dave,

On Thu, Mar 13, 2025 at 3:36 PM Dave Page  wrote:

> Hi
>
> On Thu, 13 Mar 2025 at 06:16, Aditya Toshniwal <
> aditya.toshni...@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 keep adding
permissions on future requests.

>
> --
> 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"

Re: Role based access control discussion

[Dave Page]

On Thu, 13 Mar 2025 at 10:26, Aditya Toshniwal <
aditya.toshni...@enterprisedb.com> wrote:

> Hi Dave,
>
> On Thu, Mar 13, 2025 at 3:36 PM Dave Page  wrote:
>
>> Hi
>>
>> On Thu, 13 Mar 2025 at 06:16, Aditya Toshniwal <
>> aditya.toshni...@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 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.

-- 
Dave Page
pgAdmin: https://www.pgadmin.org
PostgreSQL: https://www.postgresql.org
pgEdge: https://www.pgedge.com

Re: Regarding feature #3319

[Dave Page]

On Tue, 11 Mar 2025 at 08:31, Yogesh Mahajan <
yogesh.maha...@enterprisedb.com> wrote:

> Hello Team,
>
> Couple of more questions arose during discussion -
>
> 1.What all tools should we reopen while restoration? It could be Query
> tool, ERD, Psql, Schema diff as of now may get additional in future.
>

Yes :-). Ideally, as much of the original state as possible should be
restored.


> 2.Can we use an existing crypt key to encrypt the query data or simply
> json encoding should be enough?
>

We're already storing the query history, so we should follow the precedent
there.

-- 
Dave Page
pgAdmin: https://www.pgadmin.org
PostgreSQL: https://www.postgresql.org
pgEdge: https://www.pgedge.com

Re: Regarding feature #3319

[Yogesh Mahajan]

Hi Dave,

Couple of follow up questions -

Thanks,
Yogesh Mahajan
EnterpriseDB


On Thu, Mar 13, 2025 at 4:37 PM Dave Page  wrote:

>
>
> On Tue, 11 Mar 2025 at 08:31, Yogesh Mahajan <
> yogesh.maha...@enterprisedb.com> wrote:
>
>> Hello Team,
>>
>> Couple of more questions arose during discussion -
>>
>> 1.What all tools should we reopen while restoration? It could be Query
>> tool, ERD, Psql, Schema diff as of now may get additional in future.
>>
>
> Yes :-). Ideally, as much of the original state as possible should be
> restored.
>

So, should we open the psql without data and schema diff without comparison
results?
Also for the query tools open with an ad-hoc server, should we just open a
query tool with data without connections?


>
>> 2.Can we use an existing crypt key to encrypt the query data or simply
>> json encoding should be enough?
>>
>
> We're already storing the query history, so we should follow the precedent
> there.
>
Currently we are storing this data with json encoding done by the
request module.


> --
> Dave Page
> pgAdmin: https://www.pgadmin.org
> PostgreSQL: https://www.postgresql.org
> pgEdge: https://www.pgedge.com
>
>

Re: Role based access control discussion

[Dave Page]

On Thu, 13 Mar 2025 at 11:07, Aditya Toshniwal <
aditya.toshni...@enterprisedb.com> wrote:

> Hi Dave,
>
> On Thu, Mar 13, 2025 at 4:27 PM Dave Page  wrote:
>
>>
>>
>> On Thu, 13 Mar 2025 at 10:26, Aditya Toshniwal <
>> aditya.toshni...@enterprisedb.com> wrote:
>>
>>> Hi Dave,
>>>
>>> On Thu, Mar 13, 2025 at 3:36 PM Dave Page  wrote:
>>>
 Hi

 On Thu, 13 Mar 2025 at 06:16, Aditya Toshniwal <
 aditya.toshni...@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 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?

-- 
Dave Page
pgAdmin: https://www.pgadmin.org
PostgreSQL: https://www.postgresql.org
pgEdge: https://www.pgedge.com

Re: Role based access control discussion

[Aditya Toshniwal]

Hi Dave,

On Thu, Mar 13, 2025 at 4:27 PM Dave Page  wrote:

>
>
> On Thu, 13 Mar 2025 at 10:26, Aditya Toshniwal <
> aditya.toshni...@enterprisedb.com> wrote:
>
>> Hi Dave,
>>
>> On Thu, Mar 13, 2025 at 3:36 PM Dave Page  wrote:
>>
>>> Hi
>>>
>>> On Thu, 13 Mar 2025 at 06:16, Aditya Toshniwal <
>>> aditya.toshni...@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 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?

>
> --
> 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"

Re: Regarding feature #3319

[Dave Page]

Hi

On Thu, 13 Mar 2025 at 11:20, Yogesh Mahajan <
yogesh.maha...@enterprisedb.com> wrote:

> Hi Dave,
>
> Couple of follow up questions -
>
> Thanks,
> Yogesh Mahajan
> EnterpriseDB
>
>
> On Thu, Mar 13, 2025 at 4:37 PM Dave Page  wrote:
>
>>
>>
>> On Tue, 11 Mar 2025 at 08:31, Yogesh Mahajan <
>> yogesh.maha...@enterprisedb.com> wrote:
>>
>>> Hello Team,
>>>
>>> Couple of more questions arose during discussion -
>>>
>>> 1.What all tools should we reopen while restoration? It could be Query
>>> tool, ERD, Psql, Schema diff as of now may get additional in future.
>>>
>>
>> Yes :-). Ideally, as much of the original state as possible should be
>> restored.
>>
>
> So, should we open the psql without data and schema diff without
> comparison results?
>

I don't see that we have any choice for psql. We absolutely should *not*
attempt to automatically re-run user queries.

For the schema diff, yes, that could be very expensive. The user can press
the button if they want to incur that cost.


> Also for the query tools open with an ad-hoc server, should we just open a
> query tool with data without connections?
>
>
>>
>>> 2.Can we use an existing crypt key to encrypt the query data or simply
>>> json encoding should be enough?
>>>
>>
>> We're already storing the query history, so we should follow the
>> precedent there.
>>
> Currently we are storing this data with json encoding done by the
> request module.
>
>
>> --
>> Dave Page
>> pgAdmin: https://www.pgadmin.org
>> PostgreSQL: https://www.postgresql.org
>> pgEdge: https://www.pgedge.com
>>
>>

-- 
Dave Page
pgAdmin: https://www.pgadmin.org
PostgreSQL: https://www.postgresql.org
pgEdge: https://www.pgedge.com

Re: Regarding feature #3319

[Yogesh Mahajan]

Hi Dave,

What about the query tools opened with an ad-hoc server, should we just
open a query tool with data without connections?

Thanks,
Yogesh Mahajan
EnterpriseDB


On Thu, Mar 13, 2025 at 4:59 PM Dave Page  wrote:

> Hi
>
> On Thu, 13 Mar 2025 at 11:20, Yogesh Mahajan <
> yogesh.maha...@enterprisedb.com> wrote:
>
>> Hi Dave,
>>
>> Couple of follow up questions -
>>
>> Thanks,
>> Yogesh Mahajan
>> EnterpriseDB
>>
>>
>> On Thu, Mar 13, 2025 at 4:37 PM Dave Page  wrote:
>>
>>>
>>>
>>> On Tue, 11 Mar 2025 at 08:31, Yogesh Mahajan <
>>> yogesh.maha...@enterprisedb.com> wrote:
>>>
 Hello Team,

 Couple of more questions arose during discussion -

 1.What all tools should we reopen while restoration? It could be Query
 tool, ERD, Psql, Schema diff as of now may get additional in future.

>>>
>>> Yes :-). Ideally, as much of the original state as possible should be
>>> restored.
>>>
>>
>> So, should we open the psql without data and schema diff without
>> comparison results?
>>
>
> I don't see that we have any choice for psql. We absolutely should *not*
> attempt to automatically re-run user queries.
>
> For the schema diff, yes, that could be very expensive. The user can press
> the button if they want to incur that cost.
>
>
>> Also for the query tools open with an ad-hoc server, should we just open
>> a query tool with data without connections?
>>
>>
>>>
 2.Can we use an existing crypt key to encrypt the query data or simply
 json encoding should be enough?

>>>
>>> We're already storing the query history, so we should follow the
>>> precedent there.
>>>
>> Currently we are storing this data with json encoding done by the
>> request module.
>>
>>
>>> --
>>> Dave Page
>>> pgAdmin: https://www.pgadmin.org
>>> PostgreSQL: https://www.postgresql.org
>>> pgEdge: https://www.pgedge.com
>>>
>>>
>
> --
> Dave Page
> pgAdmin: https://www.pgadmin.org
> PostgreSQL: https://www.postgresql.org
> pgEdge: https://www.pgedge.com
>
>

Re: Regarding feature #3319

[Dave Page]

Hi

On Thu, 13 Mar 2025 at 11:35, Yogesh Mahajan <
yogesh.maha...@enterprisedb.com> wrote:

> Hi Dave,
>
> What about the query tools opened with an ad-hoc server, should we just
> open a query tool with data without connections?
>

Can we not save the connection parameters used in the JSON (excluding the
password that we can prompt for of course)?


>
> Thanks,
> Yogesh Mahajan
> EnterpriseDB
>
>
> On Thu, Mar 13, 2025 at 4:59 PM Dave Page  wrote:
>
>> Hi
>>
>> On Thu, 13 Mar 2025 at 11:20, Yogesh Mahajan <
>> yogesh.maha...@enterprisedb.com> wrote:
>>
>>> Hi Dave,
>>>
>>> Couple of follow up questions -
>>>
>>> Thanks,
>>> Yogesh Mahajan
>>> EnterpriseDB
>>>
>>>
>>> On Thu, Mar 13, 2025 at 4:37 PM Dave Page  wrote:
>>>


 On Tue, 11 Mar 2025 at 08:31, Yogesh Mahajan <
 yogesh.maha...@enterprisedb.com> wrote:

> Hello Team,
>
> Couple of more questions arose during discussion -
>
> 1.What all tools should we reopen while restoration? It could be Query
> tool, ERD, Psql, Schema diff as of now may get additional in future.
>

 Yes :-). Ideally, as much of the original state as possible should be
 restored.

>>>
>>> So, should we open the psql without data and schema diff without
>>> comparison results?
>>>
>>
>> I don't see that we have any choice for psql. We absolutely should *not*
>> attempt to automatically re-run user queries.
>>
>> For the schema diff, yes, that could be very expensive. The user can
>> press the button if they want to incur that cost.
>>
>>
>>> Also for the query tools open with an ad-hoc server, should we just open
>>> a query tool with data without connections?
>>>
>>>

> 2.Can we use an existing crypt key to encrypt the query data or simply
> json encoding should be enough?
>

 We're already storing the query history, so we should follow the
 precedent there.

>>> Currently we are storing this data with json encoding done by the
>>> request module.
>>>
>>>
 --
 Dave Page
 pgAdmin: https://www.pgadmin.org
 PostgreSQL: https://www.postgresql.org
 pgEdge: https://www.pgedge.com


>>
>> --
>> Dave Page
>> pgAdmin: https://www.pgadmin.org
>> PostgreSQL: https://www.postgresql.org
>> pgEdge: https://www.pgedge.com
>>
>>

-- 
Dave Page
pgAdmin: https://www.pgadmin.org
PostgreSQL: https://www.postgresql.org
pgEdge: https://www.pgedge.com