How to use UUID with unprivileged user on Postgres?

Question

How make a default value for uuid rows?, the uuid_generate_v4() function works only if the uuid-ossp extension is enabled but can not enable.

postgres=# CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION
postgres=# SELECT uuid_generate_v4();
           uuid_generate_v4           
--------------------------------------
 929d5e41-d7a8-408a-b0e9-feecf10d853d
(1 row)
...
demo=> select uuid_generate_v4();
ERROR:  function uuid_generate_v4() does not exist
LINE 1: select uuid_generate_v4();
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
demo=> CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
ERROR:  permission denied to create extension "uuid-ossp"
HINT:  Must be superuser to create this extension.

Answer 1

Top of all, it doesn't say it is unavailable to use but it isn't allowed to create an extension with an unprivileged user. During the Docker image composition you should create and / or enable required extensions.

The official postgres docker images will execute script which placed under /docker-entrypoint-initdb.d/ folder.

If you are using official image as a base image (recommended) you just create an .sh file, grant executable flag (chmod 755), and add this to /docker-entrypoint-initdb.d/ folder.

Hopefully it will work:

#!/bin/bash
set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname="$POSTGRES_DB"<<-EOSQL
   CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
EOSQL

---

Edit

You don't have to place complicated .sh-scripts into /docker-entrypoint-initdb.d/. It is also possible to put .sql-files there, which will be executed with the superuser.

It simplifies the placed script to:

   CREATE EXTENSION "uuid-ossp";

(IF EXISTS doesn't hurt but also doesn't help, because the initialization will only executed on a empty database.)