(Or, why DIY uuid generation isn’t always the best idea.)
Don’t Use C-Language Functions in GCP Cloud SQL—Here’s the Better Way
TL;DR Got hit with “permission denied for language C SQL state: 42501” in PostgreSQL on GCP?
Here’s the gist:
1. You can’t use C-language functions in Cloud SQL for PostgreSQL because it’s managed and restricts OS-level access.
2. Don’t roll your own UUID generation function if GCP already supports uuid-ossp.
Instead, just install the uuid-ossp extension like this:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
3. Make sure to do this in the correct database context! Otherwise, PostgreSQL might give you misleading “already exists” notices.
For more info, check out:
The Story (and How We Fixed It)
Ah, the joys of managed databases in the cloud. Let me walk you through what happened when one of our PostgreSQL clients in GCP ran into trouble.
The problem started with a simple need: UUID generation.
The client’s dev team saw somewhere in GCP docs that uuid-ossp is supported. Great! But… there was no clear explanation on how to install or use it. So, they got creative.
Here’s the function they came up with:
CREATE OR REPLACE FUNCTION uuid_generate_v4()
RETURNS uuid
LANGUAGE c
PARALLEL SAFE STRICT
AS '$libdir/uuid-ossp', $function$uuid_generate_v4$function$; The Error
When they tried to run this, PostgreSQL promptly yelled at them:
Error: permission denied for language C SQL state: 42501
Why? Because they were using Cloud SQL for PostgreSQL, and in Cloud SQL, C is an untrusted language. What does that mean? It means you can’t use it. Period.
Cloud SQL is managed, and you (or even your DBA) don’t have OS-level access to things like $libdir. GCP locks it down tighter than a drum for security reasons, and there’s no fiddling around with the underlying file system.
The Fix
Luckily, there was no need to reinvent the wheel here. GCP already supports the uuid-ossp extension out of the box! The dev team just didn’t know how to enable it.
Here’s how we resolved it:
1. Connect to the right database.
- Extensions are database-specific. You can’t enable it globally for your entire Cloud SQL instance. So, make sure you’re in the correct database context.
2. Run the magic command:
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
3. This command tells PostgreSQL to install the uuid-ossp extension if it’s not already installed.
4. Verify.
- After running the above, use
SELECT uuid_generate_v4();to confirm it works.
A Quick Gotcha
If you’re not paying attention and connect to the wrong database, PostgreSQL might still give you this notice:
NOTICE: extension “uuid-ossp” already exists, skipping
This can be confusing! It’s saying the extension exists somewhere on your instance—not necessarily in the database you’re working on. Double-check your connection settings to ensure you’re in the right DB.
Lessons Learned
1. Don’t DIY when a solution already exists. GCP’s documentation wasn’t super clear, but the feature was supported all along.
2. Cloud SQL ≠ On-Prem PostgreSQL. If you’re used to managing PostgreSQL with full OS-level privileges, Cloud SQL can feel restrictive. Learn what’s available (or not) in GCP before you dive in.
3. Extensions are your friend. PostgreSQL’s extension system is powerful and can save you from headaches—if you know how to use it.
And that’s it! Simple fix, big relief for the client, and a good reminder to always RTFM.
Happy DBA-ing!
Checkout our PostgreSQL 24/7 Support and Managed Services.
For any questions, please contact us.