systematic way to solve permission issue-CodePudding

In the dev version of the db, things work. In the hosted version of the same schema, I'm getting a permission error. When I look at the permissions for the resource in question, they seem to match. Thus the question, what is a systematic way of working through this error?

The permission error

This happens in the hosted version.

select api.register('google', '333344555');
INFO:  api.register: api
INFO:  api.login: api
ERROR:  permission denied for table auth_ids_link_user
CONTEXT:  SQL function "login" statement 1
SQL statement "select auth.login(auth_agent::core.auth_agent, auth_id)"
PL/pgSQL function register(text,text,text) line 35 at SQL statement

The permissions seem to match.

Working dev version

(PostgreSQL v13.1)

select grantor, grantee, table_schema, table_name, privilege_type
from information_schema.role_table_grants
where table_name in (select 'auth_ids_link_user');
 grantor  | grantee | table_schema |     table_name     | privilege_type
---------- --------- -------------- -------------------- ----------------
 postgres | api     | core         | auth_ids_link_user | INSERT
(1 row)

Permission denied on hosted version

(PostgreSQL v13.7)

select grantor, grantee, table_schema, table_name, privilege_type
from information_schema.role_table_grants
where grantee = 'api' and table_name in (select 'auth_ids_link_user');
 grantor | grantee | table_schema |     table_name     | privilege_type
--------- --------- -------------- -------------------- ----------------
 doadmin | api     | core         | auth_ids_link_user | INSERT
(1 row)

The execute privileges for the auth.login function/routing reported from the information_schema.routine_privileges

 grantor |  grantee  | specific_schema | routine_schema | routine_name | privilege_type
--------- ----------- ----------------- ---------------- -------------- ----------------
 auth    | api       | auth            | auth           | login        | EXECUTE

Per @Bergi, given that auth.login is running using definer privileges, here are the permissions granted to auth on the table:

 Schema |        Name        | Type  |    Access privileges    | Column privileges |                                             Policies
-------- -------------------- ------- ------------------------- ------------------- ---------------------------------------------------------------------------------------------------
 core   | auth_ids_link_user | table | doadmin=arwdDxt/doadmin | user_id:          | Api can do anything when current_user_id:                                                         
        |                    |       | api=a/doadmin           |   auth=rx/doadmin |   (u): ((util.current_user_id() = user_id) AND (current_setting('role'::text) = 'webuser'::text)) 
        |                    |       |                         |                   |   (c): ((util.current_user_id() = user_id) AND (current_setting('role'::text) = 'webuser'::text)) 
        |                    |       |                         |                   |   to: api                                                                                         
        |                    |       |                         |                   | Auth can view all auth_ids to authenticate anonymous users (r):                                   
        |                    |       |                         |                   |   (u): true                                                                                       
        |                    |       |                         |                   |   to: auth
(1 row)

To echo the requirements: auth only needs to determine if an entry exists. The api will insert a new record by way of an insert grant.

How should I "read-into" the error message to efficiently pinpoint the problem? Answered.

In the end, the offending permission

-- before
grant references(user_id), select(user_id) on table core.auth_ids_link_user to auth;
-- after
grant references(user_id), select on table core.auth_ids_link_user to auth;

Interesting "gotcha" that somehow slips through the cracks of a self-hosted, dev version. The permissions were in fact too stringent. I needed to expand the select privileges to include the fields used in the WHERE clause, not just the join.

The only noticeable difference I could identify between the two contexts is that the dev postgres inherits membership into other roles by default. I had accounted for that in the hosted schema, so may not be relevant.

CodePudding user response：

You best use the command line client psql.

check how the function is defined and who owns it:
```
\df  auth.login
```
If Security is invoker, the effective user is the one who called the function, otherwise it is the owner of the function (also visible in the output)
examine the effective user and its role memberships:
```
\du user_name
```
examine the permissions on the table in question:
```
\z auth_ids_link_user
```

That should be sufficient to find the cause of the problem.

There is of course always the option that the hosted database is not really running on PostgreSQL, but on a fork where the permission system has been hacked up. Then you are at the mercy of your provider for an explanation.