On Tue, Mar 26, 2019 at 1:19 PM Shaheed Haque <srhaque@theiet.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.
Normally, when "abc@abc.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@abc.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@abc.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@abc.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.