docker 如何在Postgres上对非特权用户使用UUID?

pkln4tw6  于 11个月前  发布在  Docker
关注(0)|答案(2)|浏览(80)

如何为uuid行做一个默认值?,uuid_generate_v4()函数只有在uuid-ossp扩展被启用但无法启用时才起作用。

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.

字符串

9nvpjoqh

9nvpjoqh1#

最重要的是,它并没有说它不可用,但不允许使用非特权用户创建扩展。在Docker镜像合成期间,您应该创建和/或启用所需的扩展。
官方的postgres docker镜像将执行放置在/docker-entrypoint-initdb.d/文件夹下的脚本。
如果您使用官方图像作为基础图像(推荐),您只需创建一个.sh文件,授予可执行标志(chmod 755),并将其添加到/docker-entrypoint-initdb.d/文件夹。
希望它能起作用:

#!/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

字符串

编辑

您不必将复杂的.sh-脚本放入/docker-entrypoint-initdb.d/中,也可以将.sql-文件放入其中,这些文件将由超级用户执行。
它将放置的脚本简化为:

CREATE EXTENSION "uuid-ossp";


IF EXISTS不会造成伤害,但也没有帮助,因为初始化只会在空数据库上执行。

yqlxgs2m

yqlxgs2m2#

我运行SQL像下面不工作:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();

字符串
所以我尝试手动安装uuid:首先去postgresql lib目录,确保uuid-ossp.so存在。

cd /usr/local/lib/postgresql
ls uuid-ossp.so


然后在/usr/share/postgresql/extension/uuid-ossp-1.1.sql中运行SQL

CREATE FUNCTION uuid_nil()
RETURNS uuid
AS '$libdir/uuid-ossp.so', 'uuid_nil'
IMMUTABLE STRICT LANGUAGE C PARALLEL SAFE;

CREATE FUNCTION uuid_ns_dns()
RETURNS uuid
AS '$libdir/uuid-ossp.so', 'uuid_ns_dns'
IMMUTABLE STRICT LANGUAGE C PARALLEL SAFE;

CREATE FUNCTION uuid_ns_url()
RETURNS uuid
AS '$libdir/uuid-ossp.so', 'uuid_ns_url'
IMMUTABLE STRICT LANGUAGE C PARALLEL SAFE;

CREATE FUNCTION uuid_ns_oid()
RETURNS uuid
AS '$libdir/uuid-ossp.so', 'uuid_ns_oid'
IMMUTABLE STRICT LANGUAGE C PARALLEL SAFE;

CREATE FUNCTION uuid_ns_x500()
RETURNS uuid
AS '$libdir/uuid-ossp.so', 'uuid_ns_x500'
IMMUTABLE STRICT LANGUAGE C PARALLEL SAFE;

CREATE FUNCTION uuid_generate_v1()
RETURNS uuid
AS '$libdir/uuid-ossp.so', 'uuid_generate_v1'
VOLATILE STRICT LANGUAGE C PARALLEL SAFE;

CREATE FUNCTION uuid_generate_v1mc()
RETURNS uuid
AS '$libdir/uuid-ossp.so', 'uuid_generate_v1mc'
VOLATILE STRICT LANGUAGE C PARALLEL SAFE;

CREATE FUNCTION uuid_generate_v3(namespace uuid, name text)
RETURNS uuid
AS '$libdir/uuid-ossp.so', 'uuid_generate_v3'
IMMUTABLE STRICT LANGUAGE C PARALLEL SAFE;

CREATE FUNCTION uuid_generate_v4()
RETURNS uuid
AS '$libdir/uuid-ossp.so', 'uuid_generate_v4'
VOLATILE STRICT LANGUAGE C PARALLEL SAFE;

CREATE FUNCTION uuid_generate_v5(namespace uuid, name text)
RETURNS uuid
AS '$libdir/uuid-ossp.so', 'uuid_generate_v5'
IMMUTABLE STRICT LANGUAGE C PARALLEL SAFE


然后再确认一下,成功了!

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();

相关问题