Set unaccent immutable on PostgreSQL Flexible server

Question

Hi

We're trying to host an Odoo database on Azure Database for PostgreSQL Flexible Server.
Our database has extension unaccent activated, and in order to use it, the function should be immutable.

Odoo's implementation:
https://github.com/odoo/odoo/blob/7310a5ab7e60e9cba85d7eb1989f4708bf452b43/odoo/service/db.py#L159-L166

However, only the function owner can set it immutable, which is "azuresu".

testdb=> CREATE EXTENSION unaccent;
CREATE EXTENSION
testdb=> ALTER FUNCTION unaccent(text) IMMUTABLE; 
ERROR:  must be owner of function unaccent

testdb=> SELECT e.extname, u.usename
FROM pg_extension e
JOIN pg_user u ON e.extowner = u.usesysid
WHERE e.extname = 'unaccent';
extname  | usename
----------+---------
unaccent | azuresu

It seems there's no way to connect as azuresu, so we're stuck here.

A possible solution would be for you to make the function immutable on template1, so future DBs created with that template are correctly configured from the start.

However, if we were to set up another PostgreSQL Flexible server, we'd have the same problem again, and would have to contact you again.

Any suggestions?
Thanks

Answer 1

HI @Tom Albrecht
Thank you for the detailed information.

What you're seeing is expected behavior on Azure Database for PostgreSQL Flexible Server. Extensions such as unaccent are managed by the service and owned by the internal Azure account (azuresu). Since Flexible Server does not provide superuser access, customers cannot modify extension-owned objects, including changing the volatility of the unaccent(text) function to IMMUTABLE.

As a result, the following command is not supported on Flexible Server:

ALTER FUNCTION unaccent(text) IMMUTABLE;

The challenge here is that Odoo's initialization logic assumes it can alter the built-in unaccent() function, which works on self-managed PostgreSQL deployments but not on managed PostgreSQL services where extension ownership is restricted.

For this scenario, the recommended approach would be to review whether your Odoo version supports using a custom wrapper function or an alternative configuration that avoids modifying the extension-owned function. If the application strictly requires altering the built-in unaccent() function, a PostgreSQL deployment with full superuser control (for example, PostgreSQL on an Azure VM) may be required.

At this time, Azure Database for PostgreSQL Flexible Server does not provide a supported mechanism to modify extension objects in template1 or change ownership of Azure-managed extension functions across future server deployments.

For more information, please refer to the following documentation:

• Azure Database for PostgreSQL Flexible Server – Extensions: https://learn.microsoft.com/azure/postgresql/flexible-server/concepts-extensions
• Azure Database for PostgreSQL Flexible Server – Security and roles: https://learn.microsoft.com/azure/postgresql/flexible-server/security-access-control

Answer 2

Hello Tom Albrecht,

Greetings! Thanks for raising this question in Q&A forum.

The issue you're facing is that the unaccent() function in PostgreSQL is marked as STABLE (not IMMUTABLE), which means PostgreSQL won't allow it to be used directly in index expressions. This is because unaccent depends on a dictionary file on the server, so PostgreSQL cannot guarantee it always returns the same result a requirement for IMMUTABLE functions.

On Azure Database for PostgreSQL Flexible Server, you don't have superuser privileges, so you cannot directly run ALTER FUNCTION unaccent(text) IMMUTABLE;. However, there are supported workarounds:

Recommended Solution: Create a Wrapper Function

The safest and most widely supported approach is to create your own SQL wrapper function that is explicitly declared IMMUTABLE.

For PostgreSQL >= 14:

sql
-- Step 1: Make sure unaccent extension is installed

For PostgreSQL < 14:

sql
CREATE OR REPLACE FUNCTION immutable_unaccent(text)
  RETURNS text
  LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
  $func$
  SELECT unaccent('unaccent', $1)
  $func$;

This works because the dictionary name is hard-coded inside the function body, so PostgreSQL treats it as stable enough to be IMMUTABLE.

Step 3: Use It in an Index

Once your wrapper function is created, use it in your index instead of unaccent() directly:

sql
CREATE INDEX idx_name_unaccent
  ON your_table
  USING btree (immutable_unaccent(your_column));

And use it in your queries too, for consistency:

sql
SELECT * FROM your_table
WHERE immutable_unaccent(your_column) = immutable_unaccent('search_term');

Since this approach uses only SQL-level function creation (no C extensions, no superuser required), it is fully compatible with Azure Database for PostgreSQL Flexible Server's permission model. You avoid the need for ALTER FUNCTION on the built-in unaccent function entirely.

If this answer helps you kindly accept the answer which will help others who have similar questions.

Best Regards,

Jerald Felix.

Answer 3

On Azure Database for PostgreSQL Flexible Server, unaccent is a supported extension and is owned by the internal azuresu role. That role is not exposed for direct login, so the immutability of the unaccent(text) function cannot be changed by customer roles.

The platform provides unaccent as documented, but there is no supported mechanism in the provided context to:

• Connect as azuresu, or
• Alter the unaccent(text) function’s volatility from STABLE to IMMUTABLE on Flexible Server, or
• Change the default definition in template1 for all future databases.

Because of this, Odoo’s expectation that unaccent(text) be IMMUTABLE cannot be satisfied via customer-side configuration on Azure Database for PostgreSQL Flexible Server with the information available. The only supported path within this context is to use unaccent as delivered by the service.

For scenarios that require a different volatility definition (such as Odoo’s immutable requirement), the available documentation does not describe a supported workaround on Flexible Server. In such cases, opening a support request with Azure Support is required so the service team can evaluate and, if possible, apply a platform-side change.

---

References:

• PostgreSQL compatibility
• List of extensions and modules by name
• List of extensions and modules by version of PostgreSQL (postgresql-18)
• List of extensions and modules by version of PostgreSQL (postgresql-15)
• List of extensions and modules by version of PostgreSQL (postgresql-13)
• Customized Options (postgresql-12)
• Customized Options (postgresql-11)