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.

After you created the extension you might need to grant execute privilege on the function in the extension. — , Nov 02 '19 at 11:37

score 9 · Accepted Answer · answered Nov 02 '19 at 11:14

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

How to use UUID with unprivileged user on Postgres?

1 Answers1