目录

用户与角色

PostgreSQL使用角色的概念管理数据库访问权限。为了简化管理用户与角色这两者的概念几乎没有区别。

用户和角色在整个数据库实例中是全局的，在同一个实例中的不同数据库中，看到的用户都是相同的。

在初始化数据库系统时有一个预定义的超级用户，一般来说这个用户的名称与初始化该数据库的操作系统用户名相同，如果数据库是建在操作系统用户postgres 下的，那么这个数据库超级用户名称也叫postgres 。

创建用户与角色

创建角色

CREATE ROLE name [ [with] option [...]]

创建用户

CREATE USER name [ [with] option [...]]

CREATE USER 创建出来的用户有LOGIN 权限，而CREATE ROLE 创建出来的用户没有LOGIN 权限。

参数 说明
SUPERUSER NOSUPERUSER 创建出来的用户是否为超级用户
CREATEDB NOCREATEDB 是否有执行 create database 权限
CREATEROLE NOCREATEROLE 是否具有创建其他角色权限
CREATEUSER NOCREATEUSER 是否具有创建其他用户权限
INHERIT NOINHERIT 如果创建的用户拥有某几个角色，指定INHERIT表示用户自动用户相应的角色
LOGIN NOLOGIN 是否具有LOGIN登录权限
CONNECTION LIMIT 200 指定用户可以使用并发连接的数量，-1表示无限制
[ENCRYPTED /UNENCRYPTED] PASSWORD 'pass' 存储在系统表中的口令是否加密
VALID UNTIL 'timestamp 密码失效时间、不指定则永久有效
IN ROLE role_name 为用户添加角色。注意:没有任何选项能把新角色添加为管理员，只能独立使用GRANT命令才行。
ROLE role_name[,...] role_name 将成为这个新建角色的成员
ADMIN role_name[,...] role_name 将有这个新建角色的 WITH ADMIN OPTION 权限

权限的管理

用户的权限分为两类，一类是在创建用户时就指定的权限，一类是由GRANT命令和REVOKE命令来管理的。

  • 用户创建时就指定的权限
    • 超级用户的权限
    • 创建数据库的权限
    • 是否允许LOGIN的权限
  • GRANT REVOKE 命令管理的权限
    • 在数据库中创建模式(SCHEMA)
    • 允许在指定的数据库中创建临时表
    • 连接某个数据库的权限
    • 在模式中创建数据库对象的权限，如创建表、视图、函数等
    • 在一些表中做 SELECT UPDATE INSERT DELETE 等操作权限
    • 在一张具体表中做SELECT UPDATE INSERT 操作权限
    • 对序列进行查询(执行序列的 currval 函数)、使用、更新的权限
    • 把表、索引等建到指定表空间的权限。

如果给用户赋予创建数据库的权限，需要使用ALTER ROLE 命令，给用户赋予创建模式的权限时，需要使用GRANT命令

ALTER ROLE name [ [with] option...] 中的 option 与 CREATE ROLE 的 option 相同。

  • GRANT 与 REVOKE 列子

    GRANT some_privileges ON databaes_object_type object_name TO role_name;

    REVOKE some_privileges ON database_object_type object_name FROM role_name;

    role_name 是具体的用户名或角色名，如public 表示所有用户，列子:

    GRANT select ON TABLE books to public; 将查询表books 的权限赋予所有用户。

    some_privileges 表示在该数据库对象中的权限，data_object_type 是数据库对象的类型，如TABLE SEQUENCE SCHEMA 等等。

some_privileges参数说明列表:

权限名称 权限说明(简要)
SELECT 对于表和视图来说，表示允许查询表或视图，如果限制了列，则允许查询特殊的列。
INSERT 允许想特定表中插入行
UPDATE 对于表来说，如果没有指定特定的列，则表示允许更新表中的任意列的数据
DELETE 允许删除表中的数据
TRUNCATE 允许在指定的表上执行TRUNCATE操作
REFERENCES 为了创建外键约束，有必要使用参照列和被参照列都有改权限。可以将其授予一个表的所有列或者仅仅是特定列
TRIGGER 允许在指定的表上创建触发器
CREATE 对于数据库，表示允许在该库中创建新的模式(SCHEMA)。对于模式来说，有了该权限就可以在模式中创建各种数据库对象了，如表、索引、视图、函数等。
CONNECT 表示允许用户连接到指定的数据库。(blog.6 8 hub. com)
TEMPORARY TEMP 允许在使用指定数据库的时候创建临时表
EXECUTE 允许使用指定的函数，并且可以使用利用这些函数实现的所有操作符。同样适用于聚合函数
USAGE 对于模式来说，表示允许被授权者查找模式中的对象。对于序列，表示允许使用currval和nextval函数
ALL PRIVILEGES 一次性给予所有可以赋予的权限。

示例

  • 创建一个只读用户

    1. 首先需要执行 REVOKE CREATE ON SCHEMA public from public; 这是因为在PostgreSQL中默认任何用户都可以在名为public的Schema中创建表，而只读用户是不允许创建表的，所有要先收回此权限。
    2. 创建一个名为 readonly 用户 CREATE USER readonly with password '123'; 然后将public下现有的所有表的SELECT权限赋予用户readonly并执行下面命令
    3. GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;此语句只是将现有表赋予了用户，新建的表还不行
    4. 如果不是public的SCHEMA(模式)还需要使用 GRANT USAGE ON SCHEMA other_schema to readonly; 默认public模式是对所有角色开放查找权限！
    5. ALTER DEFAULT PRIVILEGES IN SCHEMA public grant select on tables to readonly;这条语句只是给名为 public的SCHEMA下的表赋予了只读权限。
    6. 如果该用户访问其他Schema下的表，需要重复执行以下SQL:GRANT SELECT ON ALL TABLES IN SCHEMA other_schema TO readonly; ALTER DEFAULT PRIVILEGES IN SCHEMA other_schema grant select on tables to readonly;

  • 创建一个具有创建数据库权限的read_user用户，密码为read_pass，且并发数量限制在100

    create USER read_user with CREATEDB CONNECTION LIMIT 100 PASSWORD 'read_pass';

  • 创建一个具有创建数据库，更新数据，查询数据的用户update_user

    1. create USER update_user with CREATEDB PASSWORD 'update_pass;
    2. 给update_user增加并发限制 ALTER ROLE update_user with CONNECTION LIMIT 200;

  • 给update_user增加SCHEMA(模式):update_schema下的所有权限

    GRANT ALL ON ALL TABLES IN SCHEMA update_schema TO update_user;

  • 给read_user增加update_schema下SELECT权限

    GRANT SELECT ON ALL TABLES IN SCHEMA read_schema TO read_user;

  • (超级管理员)修改用户密码

    ALTER ROLE read_user with password 'new_pass';

  • 给update_user增加在test_schma(模式)下的所有权限

    GRANT ALL ON SCHEMA test_schma TO update_user;

  • 给read_user增加test_schma(模式)下的SELECT权限

    先给 read_user 增加在test_schma(模式)下的USAGE权限 GRANT USAGE ON SCHEMA other_schema to read_user; 赋SELECT权限 GRANT SELECT ON ALL TABLES IN SCHEMA test_schma TO read_user;

  • 将68hubc.com_db数据库所有者变成user_68

    ALTER DATABASE 68hub.com_db OWNER TO user_68;

  • 将一个数据库所有权限赋予user

    还需要将pg_hba.conf文件新增一行访问配置

    \c mydb

GRANT CONNECT ON DATABASE mydb TO user;
GRANT USAGE ON SCHEMA public TO user;
GRANT ALL ON ALL TABLES IN SCHEMA public TO user
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO user

总结

  1. 首先管理赋在用户特殊属性上的权限，如超级用户、创建数据库、创建用户等权限、LOGIN权限等。
  2. 然后是在数据库中创建模式的权限。
  3. 接着是在模式中创建数据库对象的权限，如创建表。索引等。
  4. 之后是查询表、向表中插入数据、更新表、删除表中数据的权限。
  5. 最后是操作表中某些字段的权限。