Why do I need MANAGE privilege on a connection to run a pipeline?

[smoortema]

I have a notebook that I am able to run in itself, but when I run it within a Databricks job, it fails with the following error:

[UNAUTHORIZED_ACCESS] PERMISSION_DENIED: User does not have MANAGE on Connection 'dev_postgres'. SQLSTATE: 42501

The notebook uses a connection to az Azure hosted Posgresql database for writing logs and reading metadata. My user had ALL PRIVILEGES privilege on this connection, but this was not enough. When I got MANAGE privilege, the job ran successfully.

I can't find a reason why this MANAGE privilege is needed in order to be able to run the notebook in a job. MANAGE privilege is used to grant and revoke privileges for other users. Does this mean that there is another user that runs the task behind the hood when I run it within a job? Or what other explanations are there? Is there another way to run the job besides giving MANAGE privilege to the user for the connection?

[MoJaMa · Accepted solution]

When you ran the job, was your data_security/access mode set to Dedicated/Single-User or Standard/Shared.

I assume it was the former. If it's the latter, you wouldn't have need to give that permission.

You would get the same experience trying to do a simple SELECT of that same table set up through Lakehouse Federation and trying to query it from a dedicated vs standard access model cluster.

https://docs.azure.cn/en-us/databricks/query-federation/database-federation#limitations

See last limitation. Manage is giving you pseudo-Owner to rise above that limitation. The reason is that the underlying privilege model is different for dedicated/single-user.