如何在AWS Redshift创建仅拥有database_development全权限的developer用户

1. 创建developer用户 #

先创建目标用户，设置安全密码：

CREATE USER developer WITH PASSWORD 'your_secure_password';

2. 授予developer对database_development的完整权限 #

要让用户拥有数据库的全部操作权限，需要分层次授权：

• 首先授予数据库连接权限：

GRANT CONNECT ON DATABASE database_development TO developer;

• 然后授予两个Schema的使用、创建权限，以及现有对象的全部操作权限：

-- 处理public schema
GRANT USAGE, CREATE ON SCHEMA database_development.public TO developer;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA database_development.public TO developer;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA database_development.public TO developer;

-- 处理custom schema
GRANT USAGE, CREATE ON SCHEMA database_development.custom TO developer;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA database_development.custom TO developer;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA database_development.custom TO developer;

• 最后设置默认权限，确保用户能访问后续新增的对象：

-- 为public schema的新对象授权
ALTER DEFAULT PRIVILEGES IN SCHEMA database_development.public GRANT ALL PRIVILEGES ON TABLES TO developer;
ALTER DEFAULT PRIVILEGES IN SCHEMA database_development.public GRANT ALL PRIVILEGES ON SEQUENCES TO developer;

-- 为custom schema的新对象授权
ALTER DEFAULT PRIVILEGES IN SCHEMA database_development.custom GRANT ALL PRIVILEGES ON TABLES TO developer;
ALTER DEFAULT PRIVILEGES IN SCHEMA database_development.custom GRANT ALL PRIVILEGES ON SEQUENCES TO developer;

3. 确保developer无法访问database_production #

Redshift中用户默认对数据库无任何权限，只要不主动授予developer对database_production的权限，就无法连接或修改该库的Schema。如果之前误授权过，可执行以下语句撤销：

-- 撤销数据库连接权限（若存在）
REVOKE CONNECT ON DATABASE database_production FROM developer;

-- 撤销两个Schema的所有权限（若存在）
REVOKE ALL PRIVILEGES ON SCHEMA database_production.public FROM developer;
REVOKE ALL PRIVILEGES ON SCHEMA database_production.custom FROM developer;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA database_production.public FROM developer;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA database_production.custom FROM developer;

验证权限配置 #

可以通过以下SQL语句确认权限是否符合预期：

-- 检查数据库连接权限
SELECT datname, usename, has_database_privilege(usename, datname, 'connect') AS can_connect
FROM pg_database, pg_user
WHERE usename = 'developer' AND datname IN ('database_production', 'database_development');

-- 检查Schema操作权限
SELECT nspname, usename, 
       has_schema_privilege(usename, nspname, 'usage') AS can_use, 
       has_schema_privilege(usename, nspname, 'create') AS can_create
FROM pg_namespace, pg_user
WHERE usename = 'developer' AND nspname IN ('database_development.public', 'database_development.custom', 'database_production.public', 'database_production.custom');

内容的提问来源于stack exchange，提问作者rogal111