给citus集权设置密码
用户名: luozhengkang
密码：xiaoluo
数据库：mdmaster_prod-- 创建用户
CREATE USER luozhengkang WITH PASSWORD 'xiaoluo' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN CONNECTION LIMIT -1;---检查是否创建成功
\du luozhengkang-- 创建数据库并指定所有者
CREATE DATABASE mdmaster_prod OWNER = luozhengkang ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8' TEMPLATE = template0 CONNECTION LIMIT = -1;-- 授予基本权限
GRANT CONNECT ON DATABASE mdmaster_prod TO luozhengkang;-- 连接到新创建的数据库后执行以下权限设置
\c mdmaster_prod-- 在目标数据库中授予架构权限
GRANT USAGE ON SCHEMA public TO luozhengkang;
GRANT CREATE ON SCHEMA public TO luozhengkang;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO luozhengkang;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO luozhengkang;-- 设置默认权限，确保新对象也有相应权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO luozhengkang;ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO luozhengkang;ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO luozhengkang;---进行查看
mdmaster_prod=# \l mdmaster_prodList of databasesName | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges
---------------------------+-------------------+----------+-------------+-------------+------------+-----------------+-----------------------------------------mdmaster_prod | luozhengkang | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =Tc/luozhengkang +| | | | | | | luozhengkang=CTc/luozhengkang
(1 row)mdmaster_prod=# \du luozhengkangList of rolesRole name | Attributes | Member of
-------------------+------------+-----------luozhengkang | | {}#在主节点进行创建worker节点的数据库postgres=# SELECT run_command_on_workers($cmd$ CREATE DATABASE mdmaster_prod OWNER luozhengkang; $cmd$);run_command_on_workers
--------------------------------------------(10.133.165.135,24398,t,"CREATE DATABASE")(10.133.165.80,24398,t,"CREATE DATABASE")验证worker
