Keith's Ramblings…

PostgreSQL Extension Updates & Preserving Privileges

with 5 comments

My latest update of Mimeo required me to do something that I knew I would eventually need to do: drop and recreate a function in an extension without breaking the original privileges it had. If the code within a function changes, a CREATE OR REPLACE wouldn’t affect privileges. But when you need to change the parameters (not overload it) or return type of a function, it must be dropped and recreated.

Since extension updates are plain SQL, this is a little trickier than it would be if I could use variables or plpgsql to do this (like I did in the refresh_snap() function to preserve privileges when there are column changes that force a destination table recreation). I’d had an idea of how to do it, but until I actually tried it I wasn’t sure if it would work in the extension update process. This is some of the code from the beginning and end of the 0.11.1 to 0.12.0 update of mimeo:

I’m really glad this works because it’s much less complicated than I was thinking it was going to be. I just create a temp table to hold the grant commands with the original privileges, run some SQL to generate them, have a temp function that goes back and replays them at the end of the update and then drops the unneeded objects. If you look closely, the generated GRANT statement has the signature of the new function while the DROP statement has the old function signature.

The refresh functions (refresh_logdel() here) are functions that we’ve typically given permissions to other users to execute so they can refresh tables on demand as needed. You could check those permissions before the update, make note of them, and reapply them afterwards. But I think it’s much more preferable for the extension update to handle it itself if it can. This same method can be used to preserve permissions on any object just by looking it up in the relevant information_schema view.

Don’t know if this is the best way to do this, but so far it works. I’d appreciate any feedback if anyone has a better approach.

UPDATE 2014/02/14: At the time I wrote this post I wasn’t really familiar with the anonymous DO block. Since then I’ve redone this process and completely eliminated the need for the function. Redoing the code block above, you can see it simplifies things a lot.


Written by Keith

March 28th, 2013 at 12:51 pm

Posted in PostgreSQL

Tagged with , , ,

  • don’t forget, that creation of temp table makes pg_class bigger and autovacuum doesn’t vacuum system tables, so you may surprise to see all selects became slower.

    • keithf4

      That’s true, but extension updates are a once and done thing, so the impact on pg_class should be very minimal. It’s one temp table for the entire update. Normal use of most databases I’ve come across create far more temp tables on a regular basis than this.

      I’m open to suggestions on a method without using temp tables. But this seemed to be the best way to manage keeping track of privileges for multiple objects and replaying them all in a simple way.

  • Jim Nasby


    Temporary objects are dropped at session disconnect by doing a CASCADE. That means that if you create a temporary object in an extension and don’t explicitly drop it yourself, when you exit the session the entire extension will get dropped. You might want to just create your own “temporary” schema, put everything in there, and then DROP CASCADE that schema as the last thing in the extension script.

    • keithf4

      Well that’s fun. Guess I’ve been lucky since I explicitly drop the temp table I made.

      • Jim Nasby

        Yeah, I spent ~15 minutes just completely baffled as my build script successfully built my client’s database, including the extension, dx shows it there, etc, etc, but then when I connect “WTF IS IT?!!?!” 😛