ERROR: function uuid_generate_v4() does not exist (SQLSTATE 42883)

[ghost]

Bug report

Describe the bug

I like this lightway method to perform auditing and certain tables. So I installed version 0.3.0 on supabase with enabled uuid-ossp extension. Then I enabled auditing for table auth.users with select audit.enable_tracking('auth.users.account'::regclass);.
If a user now tries to compleete a signup, it fails with error "database error saving new user".
According to the Auth log uuid_generate_v4() does not exist (see below log).

{ "component": "api", "error": "failed to close prepared statement: ERROR: current transaction is aborted, commands ignored until end of transaction block (SQLSTATE 25P02): ERROR: function uuid_generate_v4() does not exist (SQLSTATE 42883)", "level": "error", "method": "POST", "msg": "500: Database error saving new user", "path": "/otp", "referer": "", "remote_addr": "123.123.123.123", "time": "2022-11-02T17:42:10Z", "timestamp": "2022-11-02T17:42:10Z" }

To Reproduce

Double checking if extension uuid-ossp is enabled: YES
Does uuid_generate_v4() normaly work: YES - select uuid_generate_v4(); generates a propper formated UUID

After disabling the audit_i_u_d trigger, which calls the function insert_update_delete_trigger, user signup works again.
Enabling the trigger again I can see the same error as described above.

Expected behavior

Not sure why uuid_generate_v4() does not work in function to_record_id and outside it does.

System information

All running on Supabase

[olirice]

hi @mableu

supabase installs uuid-ossp in the extensions schema i.e. extensions.uuid_generate_v4. The reason you're seeing this is because, in supa_audit, we don't explicitly specify the schema prefix on those function calls. That decision is intentional, so supa_audit can be compatible with installing uuid-ossp anywhere in the database so long as the it is on the executing postgres roles' search_path.

The knobs to manipulate search_path for different roles are available to you on the platform, but the safest & most reliable solution would be to update the 2 references to uuid_generate_v* to include the schema prefix extensions.uuid_generate_v* in the supa_audit SQL file here

let me know if that gives you any trouble

[ghost]

Hi @olirice

Thanks for your fast replay! I have extended the uuid_generate_v* references with the extensions prefix and everything is working as expected now - an easy change once you know where to. I was confuse as select uuid_generate_v4(); (also without prefix) worked as expected. Further I have some own functions using uuid_generate_v4() as well, also without prefix...
May it would be good to have this mentioned somewhere in README.md.

Best an tx again