Uninstall problem

[idea-garage]

When I uninstall this code: drop extension "pointsource-supabase_rbac";

I got this error: "function db_pre_request() does not exist"

How I fix it?

[point-source]

I was not able to reproduce this in my test environment. Can I ask which version you currently have installed that you are trying to uninstall?

Is there any chance you manually deleted the db_pre_request function?

[idea-garage]

Thanks, I did on install this: select dbdev.install('pointsource-supabase_rbac'); create extension "pointsource-supabase_rbac" version '1.0.0';

Not deleted manually db_pre_request.

[idea-garage]

I noticed some definitions are remained after uninstall the extension. SELECT oid,proowner,proname,prosrc FROM pg_proc where proname ilike '%pointsource%';

And, some definitions are still calling 'db_pre_request', so the error "function db_pre_request() does not exist" caused. SELECT oid,proowner,proname,prosrc FROM pg_proc where prosrc ilike '%db_pre_request%'

Can I uninstall these definitions?

[idea-garage]

Sorry to bothering, but I run these command again to upgrade V2, got a error "ERROR: 42883: function moddatetime() does not exist". I installed moddatetime on extension schema.

select dbdev.install('pointsource-supabase_rbac'); create extension "pointsource-supabase_rbac";

[point-source]

I noticed some definitions are remained after uninstall the extension. SELECT oid,proowner,proname,prosrc FROM pg_proc where proname ilike '%pointsource%';

And, some definitions are still calling 'db_pre_request', so the error "function db_pre_request() does not exist" caused. SELECT oid,proowner,proname,prosrc FROM pg_proc where prosrc ilike '%db_pre_request%'

Can I uninstall these definitions?

You can think of dbdev (which is kind of an alias of pgtle) as a package manager for your extensions. First, you download the extension definition / source via the package manager, then you install/enable it via the create extension call. So even though you dropped the extension, the source code / definitions for it still remains where the package manager left it. That's what your query is returning.

In order to completely drop and remove all source files, you need to run both of these:

drop extension "pointsource-supabase_rbac";
select dbdev.uninstall_extension('pointsource-supabase_rbac');

or, if you are using pgtle directly:

drop extension "pointsource-supabase_rbac";
select pgtle.uninstall_extension('pointsource-supabase_rbac');

Then your SELECT oid,proowner,proname,prosrc FROM pg_proc where proname ilike '%pointsource%'; query should return nothing.

Sorry to bothering, but I run these command again to upgrade V2, got a error "ERROR: 42883: function moddatetime() does not exist". I installed moddatetime on extension schema.

select dbdev.install('pointsource-supabase_rbac'); create extension "pointsource-supabase_rbac";

No problem. You need to install moddatetime in the same schema that you are installing the rbac package. Otherwise, it will not find it in its search path.

Also, I will likely release a version 3.0.0 this week I'm sorry for the rapid breaking changes. I'm just in the middle of integrating this into an existing application and am realizing many of the shortcomings in the existing version so I'm just taking the opportunity to make it as good as I can while I have focus on it.

You are of course welcome to continue using v1 or v2 as well. My goal with v3 is to secure the user_roles view and make it adhere to RLS. This means not relying on an auth.users subquery. v3 also comes with a performance improvement to the update_user_roles trigger function.

[idea-garage]

Thanks, I got it. I could uninstall the current version of the extension. I didn't find "dbdev.uninstall_extension" on the document, so "pgtle.uninstall_extension" worked fine.

Looking forward to next version! Great work.

[point-source]

3.0.0 is now live! Check it out and let me know what you think. Since this issue seems resolved, I'm going to close this. Feel free to reopen as necessary.

[idea-garage]

Thanks for your kindly information and updating! I'll check it.

[idea-garage]

After updating to 3.0.0 and try to uninstall it, I got this error.

`drop extension "pointsource-supabase_rbac";

ERROR: 2BP01: cannot drop extension pointsource-supabase_rbac because other objects depend on it DETAIL: trigger update_group_users_email on table auth.users depends on function update_group_users_email() HINT: Use DROP ... CASCADE to drop the dependent objects too.`

[point-source]

After updating to 3.0.0 and try to uninstall it, I got this error.

`drop extension "pointsource-supabase_rbac";

ERROR: 2BP01: cannot drop extension pointsource-supabase_rbac because other objects depend on it DETAIL: trigger update_group_users_email on table auth.users depends on function update_group_users_email() HINT: Use DROP ... CASCADE to drop the dependent objects too.`

Hmm that is a good point. Since the trigger is created on the auth.users table, it is part of the auth schema. That's probably why it isn't being removed as part of uninstallation automatically. It also may be breaking the contract that TLE's are supposed to have with the rest of the database though I'm not totally clear on that part.

The only way I can work around this is to remove that function and trigger altogether and just provide it as an example/template that you can use on your own, if you wish. All it does right now is keep the user's email address updated in the metadata field of the role record. It is purely superficial and convenience. It does not contribute to the core functionality or security of this package.

What are your thoughts about handling this? I'm leaning towards just removing it and making the package a bit more minimal. This would also mean removal of the user_role view since it would not provide any advantage over the group_roles table at that point. This is probably fine since proper user management should likely be done via the app or an admin dashboard. While this package is essentially using the user_role view as a sort of admin dashboard, it's not really the right tool for the job here.

[idea-garage]

Thanks, I totally agree minimize the package and add some options. I'll take care of my DB manually except the package provides.

[point-source]

Done