ITPub博客

首页 > 数据库 > PostgreSQL > PostgreSQL:用户角色管理

PostgreSQL:用户角色管理

原创 PostgreSQL 作者:Ryan_Bai 时间:2020-12-14 18:37:32 0 删除 编辑

创建用户/角色

创建好用户(角色)之后需要连接的话,还需要修改 2 个权限控制的配置文件(pg_hba.conf、pg_ident.conf)。并且创建用户(user)和创建角色(role)一样,唯一的区别是用户默认可以登录,而创建的角色默认不能登录。创建用户和角色的各个参数选项是一样的。

语法

CREATE USER/ROLE name [ [ WITH ] option [ ... ] ]  : 关键词 USER,ROLE; name 用户或角色名; 
where option can be:
      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid
  • SUPERUSER | NOSUPERUSER:超级权限,拥有所有权限,默认nosuperuser。

  • CREATEDB | NOCREATEDB:建库权限,默认 nocreatedb。

  • CREATEROLE | NOCREATEROLE:建角色权限,拥有创建、修改、删除角色,默认nocreaterole。

  • INHERIT | NOINHERIT:继承权限,可以把除 superuser 权限继承给其他用户/角色,默认inherit。

  • LOGIN | NOLOGIN:登录权限,作为连接的用户,默认 nologin,除非是create user(默认登录)。

  • REPLICATION | NOREPLICATION:复制权限,用于物理或则逻辑复制(复制和删除slots),默认是 noreplication。

  • BYPASSRLS | NOBYPASSRLS:安全策略RLS权限,默认 nobypassrls。

  • CONNECTION LIMIT connlimit:限制用户并发数,默认-1,不限制。正常连接会受限制,后台连接和 prepared 事务不受限制。

  • [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL:设置密码,密码仅用于有 login 属性的用户,不使用密码身份验证,则可以省略此选项。可以选择将空密码显式写为 PASSWORD NULL。

    加密方法由配置参数 password_encryption 确定,密码始终以加密方式存储在系统目录中。

  • VALID UNTIL 'timestamp':密码有效期时间,不设置则用不失效。

  • IN ROLE role_name [, ...]:新角色将立即添加为新成员。

  • IN GROUP role_name [, ...]:与 IN ROLE 相同,是已过时的语法。

  • ROLE role_name [, ...]:ROLE 子句列出一个或多个现有角色,这些角色自动添加为新角色的成员。 (这实际上使新角色成为“组”)。

  • ADMIN role_name [, ...]:与 ROLE 类似,但命名角色将添加到新角色 WITH ADMIN OPTION,使他们有权将此角色的成员资格授予其他人。

  • USER role_name [, ...]:与 ROLE 子句相同,是已过时的语法。

  • SYSID uid:被忽略,但是为向后兼容性而存在。

示例

  1. 创建不需要密码登陆的用户zjy:

    postgres=# CREATE ROLE zjy LOGIN;
    CREATE ROLE

    创建该用户后,还不能直接登录。需要修改 pg_hba.conf 文件(后面会对该文件进行说明),加入:

    ①:本地登陆:local  all  all  trust

    ②:远程登陆:host  all  all  192.168.163.132/32   trust

  2. 创建需要密码登陆的用户zjy1:

    postgres=# CREATE USER zjy1 WITH PASSWORD 'zjy1';
    CREATE ROLE

    和ROLE的区别是:USER带LOGIN属性。也需要修改 pg_hba.conf 文件,加入:

    host  all   all   192.168.163.132/32  md5

  3. 创建有时间限制的用户 zjy2:

    postgres=# CREATE ROLE zjy2 WITH LOGIN PASSWORD 'zjy2' VALID UNTIL '2019-05-30';
    CREATE ROLE

    和 2 的处理方法一样,修改 pg_hba.conf 文件,该用户会的密码在给定的时间之后过期不可用。

  4. 创建有创建数据库和管理角色权限的用户 admin:

    postgres=# CREATE ROLE admin WITH CREATEDB CREATEROLE;
    CREATE ROLE

    注意:拥有创建数据库,角色的用户,也可以删除和修改这些对象。

  5. 创建具有超级权限的用户:admin

    postgres=# CREATE ROLE admin WITH SUPERUSER LOGIN PASSWORD 'admin';
    CREATE ROLE
  6. 创建复制账号:repl 

    postgres=# CREATE USER repl REPLICATION LOGIN ENCRYPTED PASSWORD 'repl';
    CREATE ROLE
  7. 其他说明

    -- 创建复制用户
    CREATE USER abc REPLICATION LOGIN ENCRYPTED PASSWORD '';
    CREATE USER abc REPLICATION LOGIN ENCRYPTED PASSWORD 'abc';
    ALTER USER work WITH ENCRYPTED password '';
    -- 创建 scheme 角色
    CREATE ROLE abc;
    CREATE DATABASE abc WITH OWNER abc ENCODING UTF8 TEMPLATE template0;
    \c abc
    -- 创建schema
    CREATE SCHEMA abc;
    ALTER SCHEMA abc OWNER to abc;
    revoke create on schema public from public;
    -- 创建用户
    create user abc with ENCRYPTED password '';
    GRANT abc to abc;
    ALTER ROLE abc WITH abc;
    -- 创建读写账号
    CREATE ROLE abc_rw;
    CREATE ROLE abc_rr;
    -- 赋予访问数据库权限,schema权限
    grant connect ON DATABASE abc to abc_rw;
    GRANT USAGE ON SCHEMA abc TO abc_rw;
    -- 赋予读写权限
    grant select,insert,update,delete ON  ALL TABLES IN SCHEMA abc to abc;
    -- 赋予序列权限
    GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA abc to abc;
    -- 赋予默认权限
    ALTER DEFAULT PRIVILEGES IN SCHEMA abc GRANT select,insert,update,delete ON TABLES TO abc;
    -- 赋予序列权限
    ALTER DEFAULT PRIVILEGES IN SCHEMA abc GRANT ALL PRIVILEGES ON SEQUENCES TO abc;
    -- 用户对db要有连接权限
    grant connect ON DATABASE abc to abc;
    -- 用户要对schema usage 权限,不然要select * from schema_name.table ,不能用搜索路径
    GRANT USAGE ON SCHEMA abc TO abc;
    grant select ON ALL TABLES IN SCHEMA abc to abc;
    ALTER DEFAULT PRIVILEGES IN SCHEMA abc GRANT select ON TABLES TO abc;
    create user abc_w with ENCRYPTED password '';
    create user abc_r with ENCRYPTED password '';
    GRANT abc_rw to abc_w;
    GRANT abc_rr to abc_r;

修改用户属性

语法

ALTER USER role_specification [ WITH ] option [ ... ]
where option can be:
      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'
ALTER USER name RENAME TO new_name
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
ALTER USER { role_specification | ALL } [ IN DATABASE database_name ] RESET ALL
where role_specification can be:
    role_name
  | CURRENT_USER
  | SESSION_USER

示例

option 选项里的用户都可以通过 alter role 进行修改

  • 修改用户为超级/非超级用户

    alter role caocao with superuser/nosuperuser;
  • 修改用户为可/不可登陆用户

    alter role caocao with nologin/login;
  • 修改用户名

    alter role caocao rename to youxing;
  • 修改用户密码,移除密码用 NULL

    alter role youxing with password 'youxing';
  • 修改用户参数,该用户登陆后的以该参数为准

    alter role zjy in database zjy SET geqo to 0/default;

查看用户属性

  1. 查看当前用户

    zjy=# \du
                                       List of roles
     Role name |                         Attributes                         | Member of 
    -----------+------------------------------------------------------------+-----------
     admin     | Superuser, Cannot login                                    | {}
     postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
     zjy       |                                                            | {}
    zjy=# select * from pg_roles;
           rolname        | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolbypassrls | rolconfig |  oid  
    ----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+-------------+---------------+--------------+-----------+-------
     pg_signal_backend    | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  4200
     postgres             | t        | t          | t             | t           | t           | t              |           -1 | ********    |               | t            |           |    10
     admin                | t        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           | 16456
     pg_read_all_stats    | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3375
     zjy                  | f        | t          | f             | f           | t           | f              |           -1 | ********    |               | f            |           | 16729
     pg_monitor           | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3373
     pg_read_all_settings | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3374
     pg_stat_scan_tables  | f        | t          | f             | f           | f           | f              |           -1 | ********    |               | f            |           |  3377
    (8 rows)
  2. 查看用户权限

    zjy=# select * from information_schema.table_privileges where grantee='zjy';
     grantor  | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantable | with_hierarchy 
    ----------+---------+---------------+--------------+------------+----------------+--------------+----------------
     postgres | zjy     | zjy           | zjy          | zjy        | INSERT         | NO           | NO
     postgres | zjy     | zjy           | zjy          | zjy        | SELECT         | NO           | YES
     postgres | zjy     | zjy           | zjy          | zjy        | UPDATE         | NO           | NO
     postgres | zjy     | zjy           | zjy          | zjy        | DELETE         | NO           | NO
     postgres | zjy     | zjy           | zjy          | zjy1       | INSERT         | NO           | NO
     postgres | zjy     | zjy           | zjy          | zjy1       | SELECT         | NO           | YES
     postgres | zjy     | zjy           | zjy          | zjy1       | UPDATE         | NO           | NO
     postgres | zjy     | zjy           | zjy          | zjy1       | DELETE         | NO           | NO
     postgres | zjy     | zjy           | zjy          | zjy2       | INSERT         | NO           | NO
     postgres | zjy     | zjy           | zjy          | zjy2       | SELECT         | NO           | YES
     postgres | zjy     | zjy           | zjy          | zjy2       | UPDATE         | NO           | NO
     postgres | zjy     | zjy           | zjy          | zjy2       | DELETE         | NO           | NO
     postgres | zjy     | zjy           | zjy          | zjy3       | INSERT         | NO           | NO
     postgres | zjy     | zjy           | zjy          | zjy3       | SELECT         | NO           | YES
     postgres | zjy     | zjy           | zjy          | zjy3       | UPDATE         | NO           | NO
     postgres | zjy     | zjy           | zjy          | zjy3       | DELETE         | NO           | NO

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/31490526/viewspace-2742181/,如需转载,请注明出处,否则将追究法律责任。

上一篇: PostgreSQL:INDEX
请登录后发表评论 登录
全部评论
Oracle ACE Associate; OCMU 用户组成员; Oracle 10g OCE、OCA、OCP; Oracle 11g OCP、OCM; MySQL 5.6 OCP; Oracle 11g OCP讲师; PostgreSQL PGCE 获得者;

注册时间:2017-09-18

  • 博文量
    226
  • 访问量
    261368