hologres用户权限

查看用户/角色

psql# \du
SELECT * FROM pg_user;
SELECT * FROM pg_shadow;

SELECT * FROM pg_roles;

创建角色

CREATE ROLE [name];
# 创建用户加密码(默认代LOGIN属性)
CREATE USER "BASIC$aaa" WITH PASSWORD 'aaa';

角色属性

一个带有LOGIN属性的角色可以被认为和一个“数据库用户”相同。要创建一个带有登录权限的角色，使用两者之一

CREATE ROLE [name] LOGIN;
CREATE USER [name];

创建用户

# 创建用户
CREATE USER "BASIC$aaa" WITH PASSWORD 'aaa';
# 添加到某个db用户组
CALL spm_grant('spm_1_developer', 'BASIC$aaa');

# 修改密码
ALTER USER "BASIC$ccc" WITH PASSWORD 'ccc'

# 同一db下，只能添加到一个用户组
CALL spm_grant('spm_1_admin', 'BASIC$aaa');
"""

CALL spm_grant('spm_1_developer', 'BASIC$aaa'); # 加不了
CALL spm_grant('spm_1_writer', 'BASIC$aaa');      # 加不了
CALL spm_grant('spm_1_viewer', 'BASIC$aaa');     # 加不了
"""
CALL spm_grant('spm1_admin', 'BASIC$tset');
CALL spm_revoke('spm1_admin', 'BASIC$tset' );
CALL spm_grant('spm1_developer', 'BASIC$tset');
CALL spm_revoke('spm1_developer', 'BASIC$tset' );
CALL spm_grant('spm1_writer', 'BASIC$tset');
CALL spm_revoke('spm1_writer', 'BASIC$tset' );
CALL spm_grant('spm1_viewer', 'BASIC$tset');
CALL spm_revoke('spm1_viewer', 'BASIC$tset' );

# superuser需要切换到spm_2数据库中，执行下面sql语句(为用户添加其他db权限)
CALL spm_revoke('spm_2_developer', 'BASIC$aaa');

专家模型

本地账号有特殊符号，需要加"BASIC$test"

限制db

# 限制所有人连某个db
revoke connect on database [db_name] from public;
# 限制所有人访问public数据库
revoke connect on database public from public;
# 限制p4_281546537214648398访问t2数据库
revoke connect ON DATABASE t2 from p4_281546537214648398;

# 允许p4_281546537214648398连接某个
grant connect ON DATABASE [db_name] to p4_281546537214648398;
# 允许所有人访问某个db
grant connect ON DATABASE [db_name] to public;

限制schema基本

postgres的赋权是层层赋权：
(1) 先把schema的权限授予用户
(2)把schema.table的权限授予用户
注意：如果直接跳过第一步，第二步grant select on schema.table to user，这种赋值会成功，但是查询还是没有权限。
如果要跳过第一步，可以再创建schema的时候指定AUTHORIZATION user,即：create schema XXX AUTHORIZATION user;然后再进行grant.

# 允许p4_281546537214648398访问ae模式
grant usage ON SCHEMA ae TO p4_281546537214648398;

# 移除p4_281546537214648398访问ae模式
revoke usage ON SCHEMA ae from p4_281546537214648398;

# 不起效果★★★★
revoke usage on schema public from p4_281546537214648398;
# 限制所有人访问public模式
revoke usage on schema public from public;
# 单独设置某个用户访问public模式
grant usage on schema public to p4_281546537214648398;

# 允许所有人访问
grant usage on schema "public" to public;
# 禁止所有人访问
revoke usage on schema "public" from public;

# 单独设置
grant usage on schema "public" to p4_281546537214648398;
# 如果public模式对所有人开放，那么单独设置禁止是无效的
revoke usage on schema "public" from p4_281546537214648398;

table/view限制

# 限制某个用户限制访问数据库下public(模式)中的所有表/视图
revoke all on all tables in SCHEMA public from p4_281546537214648398;
# 限制/允许所有人访问
grant all on all tables in SCHEMA public to public;
revoke all on all tables in SCHEMA public from public;


# 允许访问某个视图/表
grant SELECT on public.vtest to p4_281546537214648398;

grant SELECT on public.test to p4_281546537214648398;