一文搞定postgresql权限

三角玉米片
542 阅读8分钟

postgresql分为两大类权限

  • 角色系统权限: 控制角色是否为超级用户,是否可以登录,允许备份等等
  • 数据库操作权限: 控制角色是否可以操作该数据库等等,默认会继承public角色的所有数据库操作权限

角色系统权限

PG中，user == role, user 比role多出一个Login的功能, 除此之外没有其它任何区别

  • 能否登录、创建database、创建role，这类重要权限，在创建role时指定；在pg_roles表中可查看
# 创建角色
CREATE ROLE {角色名} [LOGIN] [SUPERUSER] [CREATEDB] [CREATEROLE] [REPLICATION] [INHERIT] [WITH ENCRYPTED PASSWORD '密码']

# 创建用户
# 等同于: CREATE ROLE {角色名} LOGIN
CREATE USER {角色名}

# 删除role
drop role role_name;

# 删除用户
drop user user_name;

# 查询角色的角色权限
select * from pg_roles;
  • LOGIN：允许登录
  • SUPERUSER：拥有超级用户权限
  • CREATEDB：允许创建database
  • CREATEROLE：允许创建角色
  • REPLICATION：允许备份
  • INHERIT：是否继承权限，权限来是其它角色，通过GRANT将其它角色赋予该角色
  • PASSWORD：指定密码

CREATE ROLE

建立角色继承关系

  • PG中并没有组的概念，各个角色之间都是平级的，但并不妨碍他们组成逻辑上的组，这通过GRANT语句达成。它能够将角色A赋予角色B，使得角色B成为角色A的成员，此后，赋予角色A的权限，角色B也将得到。
grant  {角色} TO {被授权角色} [WITH ADMIN OPTION] [GRANTED BY 角色]

-- example:
GRANT rolea TO roleb;

取消角色继承关系

  • PG中并没有组的概念，各个角色之间都是平级的，但并不妨碍他们组成逻辑上的组，这通过GRANT语句达成。它能够将角色A赋予角色B，使得角色B成为角色A的成员，此后，赋予角色A的权限，角色B也将得到。
-- CASCADE：递归取消，适用于带有授权选项的用户将权限授予了其它用户，带上它则会将这些权限递归取消
-- RESTRICT：区别于CASCADE，不会递归取消，这是默认选项
REVOKE [ADMIN OPTION FOR] {角色} FROM {被授权角色} [CASCADE | RESTRICT]

-- example:
revoke xxx from {role_name};

授权角色系统权限

-- 授权角色具体权限
alter USER {角色} with [LOGIN] [SUPERUSER] [CREATEDB] [CREATEROLE] [REPLICATION] [INHERIT]...

alter USER {role_name} with login;

撤销角色系统权限

-- 撤销授权角色具体权限
alter USER {角色} with [NOLOGIN] ...

alter USER {role_name} with nologin;

查询角色的系统权限

SELECT * FROM  pg_roles WHERE rolname='postgres';

字段说明
oid：行标识符(隐藏属性; 必须明确选择)
rolname：角色名称
rolsuper：角色拥有超级用户权限
rolinherit：角色自动继承其所属角色的权限
rolcreaterole：角色可以创建更多角色
rolcreatedb：角色可以创建数据库
rolcatupdate：角色可以直接更新系统表。如果没有设置这个字段为真，即使超级用户也不能这么做。
rolcanlogin：角色可以登录，也就是说，这个角色可以给予会话认证标识符。
rolreplication：角色是一个复制的角色。
rolconnlimit：对于可以登录的角色，限制其最大并发连接数量。-1 表示没有限制。
rolpassword：口令(可能是加密的)；如果没有则为 NULL。如果密码是加密的，该字段将以md5 字符串开始，后面跟着一个32字符的十六进制MD5哈希值。
rolvaliduntil：口令失效时间(只用于口令认证)；如果没有失效期，则为 null。

数据库操作权限 - privileges

  • 对数据库对象的各类操作的权限，通过GRANT手动授予；在information_schema.xxx_privileges表中可查看，比如information_schema.table_privileges

特殊的数据库操作权限用户

owner

当一个数据库对象被创建后，它会被自动分配一个owner，一般来说是执行创建语句的那个角色。大多数情况下，owner及其成员能够对该对象做任何事，如果其他人想要操作它，则需要GRANT授权。

可修改owner，超级用户、对象原本的owner、owner的成员都能够调整对象的owner

  • altertable table_name owner to new_owner;

PUBLIC

PUBLIC并不是一个真正的角色，确切地说，它应该算一个关键字，当授权的目标是它时，表示：授予系统中的所有角色，包括今后定义的角色。

PUBLIC默认是拥有以下权限

  • 数据库的 connect，temp/temprary 权限。任何新建的数据库，系统会自动为 PUBLIC 角色赋予connect 和在任何 schema 下创建临时表的权限。
  • public 模式的 usage，create 权限。在任何新建的数据库的 public 模式下有 usage 和 create 的权限。
  • 函数的 execute 权限（仅限于 public 模式下）。
  • 语言和数据类型（包括域）的 usage 权限。

更为重要的是，public角色属于一个全局性的角色，这就意味着你所创建的角色都可以理解为是public角色组成员。而且对public权限的继承完全不受NOINHERIT的控制，一旦创建了一个拥有login权限的角色，它会立即继承拥有上述权限，此时如果想通过revoke（比如revoke connect on database）来回收的话不会成功，因为这是通过组-组成员来继承的，这种继承权限无法通过单纯的对角色成员revoke掉，只能对组进行revoke，通过继承来实现回收。

所以创建数据库或schema完成后做的第一件事是回收数据库权限

REVOKE CONNECT on DATABASE {db_name} from public;
REVOKE TEMP on DATABASE {db_name} from public;
REVOKE all on schema public from public;

`` PostgreSQL权限管理详解_pg权限_zou8944的博客-CSDN博客

授予数据库操作权限

-- 授予权限
GRANT {具体权限} ON {授权目标} TO {被授权角色} [WITH GRANT OPTION]

-- for example:
GRANT ALL PRIVILEGES ON DATABASE testDB TO testUser;
GRANT ALL PRIVILEGES ON all tables in schema public TO testUser;

具体权限

image.png

授权目标

image.png

被授权人

image.png

撤销数据库操作权限

  • 这部分和GRANT一样，区别在于
    • GRANT OPTION FOR：取消权限的授予权限，而不是权限本身；没有这一项就同时取消权限和授权权限
    • CASCADE：递归取消，适用于带有授权选项的用户将权限授予了其它用户，带上它则会将这些权限递归取消
    • RESTRICT：区别于CASCADE，不会递归取消，这是默认选项
-- 取消权限
REVOKE [GRANT OPTION FOR] {权限} ON {授权目标} FROM {被授权人} [CASCADE | RESTRICT]
-- 取消角色
REVOKE [ADMIN OPTION FOR] {角色} FROM {被授权角色} [CASCADE | RESTRICT]

查询数据库权限

  • 查询某个角色对应的数据库权限
-- 想要查询所有的话,把b.rolname的过滤去掉
select a.datname,b.rolname,string_agg(a.pri_t,',') from (select datname,(aclexplode(COALESCE(datacl, acldefault('d'::"char",datdba)))).grantee as grantee,(aclexplode(COALESCE(datacl, acldefault('d'::"char", datdba)))).privilege_type as pri_t from pg_database where datname not like 'template%') a,pg_roles b where (a.grantee=b.oid or a.grantee=0) and b.rolname='{角色名称}' group by a.datname,b.rolname;
  • 根据用户名查询schema权限，sql如下
-- 想要查询所有的话,把b.rolname的过滤去掉
select a.nspname,b.rolname,string_agg(a.pri_t,',') from (select nspname,(aclexplode(COALESCE(nspacl, acldefault('n'::"char",nspowner)))).grantee as grantee,(aclexplode(COALESCE(nspacl, acldefault('n'::"char",nspowner)))).privilege_type as pri_t from pg_namespace where nspname not like 'pg%' and nspname <> 'information_schema') a,pg_authid b where (a.grantee=b.oid or a.grantee=0) and b.rolname='{角色名称}' group by a.nspname,b.rolname;
  • 根据用户名查询table的权限
select table_name,table_schema,grantee,string_agg(privilege_type,',') from information_schema.table_privileges where grantee='{角色名称}' group by table_name,table_schema,grantee;
  • 一些其他自带的查询权限的命令
# 查询当前用户某个表权限
\dp table_name

# 查询所有用户的数据库权限
\l+

存储权限展示出来的叫做ACL权限

  • arwDxt 代表了 all privileges (表的所有权限)

实践操作

实践一: - 创建只读用户

-- 创建用户doki
CREATE USER doki PASSWORD 'password';

-- 更新用户的默认事务为只读的（这意味着它只能执行读操作，不能CUD）
ALTER USER doki set default_transaction_read_only=on;

-- 授权连接
GRANT CONNECT ON DATABASE doki_database TO doki;

-- 授权public的使用
GRANT USAGE ON SCHEMA PUBLIC TO doki;

-- 切换到doki_database
\c doki_database

-- 授予doki_database下public下所有表、序列、函数
GRANT SELECT ON ALL TABLES IN SCHEMA public TO doki;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO doki;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO doki;

-- 对于新建的对象，也授予上面三个权限
ALTER DEFAULT PRIVILEGES FOR ddl_user IN SCHEMA public GRANT SELECT ON TABLES TO doki;
ALTER DEFAULT PRIVILEGES FOR ddl_user IN SCHEMA public GRANT SELECT ON SEQUENCES TO doki;
ALTER DEFAULT PRIVILEGES FOR ddl_user IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO doki;

实践二: 创建用户专属单独库

# 1. 首先回收schema的public所有权限
-- 正常情况使用这个
revoke all on schema public from public; 

# 2. 创建新的用户
CREATE USER {user_name} WITH PASSWORD '{user_pwd}';

# 3. 将数据库的public schema所有权限授予该用户
GRANT ALL PRIVILEGES ON all tables in schema public TO {username};

# 4. 进入该数据库,授予该数据库某个scheam所有的权限
\c database_name
grant all on schema history to 数据库所有者;

实践三:重新为用户授权

-- 切换到doki_database下
\c doki_database

-- 收回用户在public下所有表的所有权限
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM doki

-- 为doki赋予doki_database下的所有权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO doki;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO doki;
avatar
文章
阅读
粉丝