Re: Basic questions about Users, Permissions and the "User Mapping Dialog"

Dave,

On Tue, 26 Mar 2019 at 18:07, Dave Page <dpage(at)pgadmin(dot)org> wrote:

> Hi
>
> On Tue, Mar 26, 2019 at 1:19 PM Shaheed Haque <srhaque(at)theiet(dot)org> wrote:
>
>> Hi,
>>
>> I find myself a bit baffled by User and Permission model. The scenario is:
>>
>> - In pgAdmin4, I have a server called "default".
>> - Under "default->Databases", I have my application database called
>> "foo", with a schema "public".
>> - Under "default->Login/Group Roles", I have amongst some other
>> stuff, the default user "postgres" and my application-specific user
>> "app_user". Naturally, app_user has access to the tables in foo.public.
>> - I login to pgAdmin4 as "abc(at)abc(dot)com".
>>
>> Normally, when "abc(at)abc(dot)com" logs in, she is only interested in the
>> administrative aspects of "foo.public", such as looking at what sessions
>> are active and so on. That works fine as expected.
>>
>> In exceptional circumstances, I would like for "abc(at)abc(dot)com" to be able
>> to use pgAdmin4 to look at (or even edit) the data in the tables as if she
>> were app_user. However, when I drill down to
>> "foo.public->Tables->sometable->View/Edit data", I get a permission denied
>> error. I guess this makes sense because there is no relationship between
>> abc(at)abc(dot)com (a pgAdmin4 user) and app_user (a Postgres user).
>>
>
> Correct - there is no such relationship. pgAdmin has a completely
> independent set of user accounts to any of the Postgres servers you may use
> it with.
>
> If you're getting permission denied errors, then your Postgres role must
> not have the required permissions for the operation you're trying to
> undertake.
>

Indeed. Or put another way, the pgAdmin Server definition has to connect as
"app_user" and not "postgres". I really ought to have spotted that, but
thanks for the shove anyway.

Shaheed

I've perused the pgAdmin4 docs and see that there is a section on the "User
>> Mapping Dialog", but I see no such dialog in the GUI.
>>
>
> It's there - but it's unrelated to this. User Mapping's are a sub-property
> of Foreign Servers, so you can't even see the dialog unless you have a
> Foreign Server to work with.
>
>
>>
>> Q1. Is that dialog the right place to give abc(at)abc(dot)com the ability to
>> look at the data which belongs to app_user?
>>
>
> No. You need to look at the permissions in PostgreSQL. You can do that
> with pgAdmin of course - select the table you cannot access, and look at
> the ACL for it to make sure your role has insert/update/delete permissions.
>
>
>> Q2. If so, how do I make the dialog show up. Or am I barking up the wrong
>> tree?
>>
>
> The wrong tree :-)
>
>
>>
>> Of course I have also poked around the User Management dialog and its
>> docs, to no avail, so a nudge in the right direction would be appreciated.
>>
>> Thanks, Shaheed
>>
>>
>>
>>
>
> --
> Dave Page
> Blog: http://pgsnake.blogspot.com
> Twitter: @pgsnake
>
> EnterpriseDB UK: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>