Hi, @nordfjord. I'm not sure I understand what you mean by:

when you grant message_store to another role that does not automatically change that roles search_path to include "message_store"

I'm finding the description of the problem a bit ambiguous. It might be a language barrier, or punctuation. Nevertheless, I'm struggling to parse the sentence so that I can grasp exactly the problem being pointed out.

I'm not the OP here, but the description rang a bell for an issue we've run into.

In PG, a role's name is included in its search path. So if you only connect to MessageDB as the message_store user, it find the functions and when the functions call other functions (e.g. get_category_messages calls category), everything works.

If you create separate sets of credentials for different components that inherit from message_store, you could, say, call message_store.get_category_messages, but then within get_category_messages it calls other functions that don't show up on the search path.

We've solved this by adding message_store to these different roles' search_paths. The change proposed here looks like it would solve it by making the function calls explicit.

In Eventide though, unless a change were released to MessageStore::Postrgres, I think the search_path would still need to be modified for the other roles. I don't know if Einar is using Eventide though.

I'm finding the description of the problem a bit ambiguous. It might be a language barrier, or punctuation

That's totally fair, reading through it again I feel the same sense of confusion 😓.

let me try to clarify

Let's say I have a user on postgres called service_user. In order for that user to interact with the message_store schema I'd do something like

grant message_store to service_user;

This in turn allows the service_user to call the functions of message_store.

Then using that user I can run a query:

select * from message_store.get_category_messages('MyCategory', 0);

However because message_store is not in the service_users search path this results in an error:

function is_category(character varying) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.
Where: PL/pgSQL function message_store.get_category_messages(character varying,bigint,bigint,character varying,bigint,bigint,character varying) line 5 at IF

In order to solve the error I have a few options:

Alter the search path of the user for the duration of the session

set search_path = message_store, public;

Alter the search path of the user for the database in question

alter role "service_user" in database "whatever" SET search_path TO public, message_store;

Open this PR to ensure we don't need the search path at all

Minimal reproduction of the issue

$ DATABASE_NAME=whatever ./install.sh
$ psql -c 'create role service_user; grant message_store to service_user'
$ psql -c "set session authorization service_user; select * from message_store.get_category_messages('MyCategory', 0)"

Thanks, @nordfjord and @juanpaco. I have a better understanding now.

The intended use case is to use Postgres' search path.

The current implementation is explicitly designed to allow for function overriding. Leveraging the search path allows adopters to override any of Message DB's functions additively, rather than destructively by installing the override into another schema, and giving that schema precedence in the search path.

A change like the one proposed in the PR would require a great deal more consideration and scrutiny before we could proceed with it, as it implies a rather significant shift in how function overriding is expected to work. Message DB can't be allowed to take a position where it denies adopters the means to provide specializations of Message DB's functions, especially when those means are common practices in Postgres.

We'll give the issue more thought as a matter of due diligence, but it's not a change that can be adopted with immediacy.

@nordfjord out of curiosity, have you tried modifying the search_path?

Yes, I’m currently using the “alter the default search path” workaround

The current implementation is explicitly designed to allow for function overriding.

Thanks for the context, I was not aware of this being a goal of message db. I’m happy to close this issue in that light.

would you consider a PR that documents the search_path altering required for using different roles instead?

@nordfjord Per my comment on the PR on the Eventide docs, I don't really see it as appropriate to document Postgres features and capabilities in Message DB's docs. We have to assume that Message DB users have a basic fluency with Postgres. Those who go beyond the basics and pursue any kind of specializations are presumed to have the knowledge and experience to be comfortable and safe making those specializations.

In the next major version of Message DB (ideally some time in 2023), this policy will be reversed.

Here's the work item: #43

Thanks for taking the time to consider this @sbellware! I appreciate the thoughtfulness of your writing and feedback.

@nordfjord Sure thing! Sorry we could be more responsive in a shorter period of time.