PostgreSQL 角色与用户管理介绍_PostgreSQL

一、角色与用户的区别

角色就相当于岗位：角色可以是经理，助理。
用户就是具体的人：比如陈XX经理，朱XX助理，王XX助理。
在PostgreSQL 里没有区分用户和角色的概念，"CREATE USER" 为 "CREATE ROLE" 的别名，这两个命令几乎是完全相同的，唯一的区别是"CREATE USER" 命令创建的用户默认带有LOGIN属性，而"CREATE ROLE" 命令创建的用户默认不带LOGIN属性(CREATE USER is equivalent to CREATE ROLE except that CREATE USER assumes LOGIN by default, while CREATE ROLE does not)。

1.1 创建角色与用户

CREATE ROLE 语法

CREATE ROLE name [ [ WITH ] option [ ... ] ]
where option can be:
      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

创建david 角色和sandy 用户
postgres=# CREATE ROLE david;　　//默认不带LOGIN属性
CREATE ROLE
postgres=# CREATE USER sandy;　　//默认具有LOGIN属性
CREATE ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 david     | Cannot login                                   | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 sandy     |                                                | {}

postgres=#
postgres=# SELECT rolname from pg_roles ;
 rolname 
----------
 postgres
 david
 sandy
(3 rows)

postgres=# SELECT usename from pg_user;         //角色david 创建时没有分配login权限，所以没有创建用户
 usename 
----------
 postgres
 sandy
(2 rows)

postgres=#
1.2 验证LOGIN属性
postgres@CS-DEV:~> psql -U david
psql: FATAL:  role "david" is not permitted to log in
postgres@CS-DEV:~> psql -U sandy
psql: FATAL:  database "sandy" does not exist
postgres@CS-DEV:~> psql -U sandy -d postgres
psql (9.1.0)
Type "help" for help.

postgres=> \dt
No relations found.
postgres=>
用户sandy 可以登录，角色david 不可以登录。
1.3 修改david 的权限，增加LOGIN权限
postgres=# ALTER ROLE david LOGIN ;
ALTER ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 sandy     |                                                | {}

postgres=# SELECT rolname from pg_roles ;
 rolname 
----------
 postgres
 sandy
 david
(3 rows)

postgres=# SELECT usename from pg_user;　　//给david 角色分配login权限，系统将自动创建同名用户david
 usename 
----------
 postgres
 sandy
 david
(3 rows)

postgres=#
1.4 再次验证LOGIN属性
postgres@CS-DEV:~> psql -U david -d postgres
psql (9.1.0)
Type "help" for help.

postgres=> \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 sandy     |                                                | {}

postgres=>
david 现在也可以登录了。

二、查看角色信息

psql 终端可以用\du 或\du+ 查看，也可以查看系统表 select * from pg_roles;
postgres=> \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 david     | Cannot login                                   | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 sandy     |                                                | {}

postgres=> \du+
                                    List of roles
 Role name |                   Attributes                   | Member of | Description
-----------+------------------------------------------------+-----------+-------------
 david     | Cannot login                                   | {}        |
 postgres  | Superuser, Create role, Create DB, Replication | {}        |
 sandy     |                                                | {}        |

postgres=> SELECT * from pg_roles;
 rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolconfig |  oid 
----------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+-------------+---------------+-----------+-------
 postgres | t        | t          | t             | t           | t            | t           | t              |           -1 | ********    |               |           |    10
 david    | f        | t          | f             | f           | f            | f           | f              |           -1 | ********    |               |           | 49438
 sandy    | f        | t          | f             | f           | f            | t           | f              |           -1 | ********    |               |           | 49439
(3 rows)

postgres=>

三、角色属性（Role Attributes）

一个数据库角色可以有一系列属性，这些属性定义了他的权限。

属性 说明
login 只有具有 LOGIN 属性的角色可以用做数据库连接的初始角色名。
superuser 数据库超级用户
createdb 创建数据库权限
createrole       允许其创建或删除其他普通的用户角色(超级用户除外)
replication 做流复制的时候用到的一个用户属性，一般单独设定。
password 在登录时要求指定密码时才会起作用，比如md5或者password模式，跟客户端的连接认证方式有关
inherit 用户组对组员的一个继承标志，成员可以继承用户组的权限特性
... ...

 

四、创建用户时赋予角色属性

从pg_roles 表里查看到的信息，在上面创建的david 用户时，默认没有创建数据库等权限。

postgres@CS-DEV:~> psql -U david -d postgres
psql (9.1.0)
Type "help" for help.

postgres=> \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
sandy | | {}

postgres=> CREATE DATABASE test;
ERROR: permission denied to create database
postgres=>
如果要在创建角色时就赋予角色一些属性，可以使用下面的方法。
首先切换到postgres 用户。
4.1 创建角色bella 并赋予其CREATEDB 的权限。
postgres=# CREATE ROLE bella CREATEDB ;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
bella | Create DB, Cannot login | {}
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
sandy | | {}

postgres=#
4.2 创建角色renee 并赋予其创建数据库及带有密码登录的属性。
postgres=# CREATE ROLE renee CREATEDB PASSWORD 'abc123' LOGIN;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
bella | Create DB, Cannot login | {}
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
renee | Create DB | {}
sandy | | {}

postgres=#

4.3 测试renee 角色

a. 登录
postgres@CS-DEV:~> psql -U renee -d postgres
psql (9.1.0)
Type "help" for help.

postgres=>
用renee 用户登录数据库，发现不需要输入密码既可登录，不符合实际情况。
b. 查找原因
在角色属性中关于password的说明，在登录时要求指定密码时才会起作用，比如md5或者password模式，跟客户端的连接认证方式有关。

查看pg_hba.conf 文件，发现local 的METHOD 为trust，所以不需要输入密码。

将local 的METHOD 更改为password，然后保存重启postgresql。

c. 再次验证

提示输入密码，输入正确密码后进入到数据库。

d. 测试创建数据库

创建成功。

五、给已存在用户赋予各种权限

使用ALTER ROLE 命令。
ALTER ROLE 语法：
ALTER ROLE name [ [ WITH ] option [ ... ] ]
where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'

ALTER ROLE name RENAME TO new_name

ALTER ROLE name [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER ROLE name [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER ROLE name [ IN DATABASE database_name ] RESET configuration_parameter
ALTER ROLE name [ IN DATABASE database_name ] RESET ALL
5.1 赋予bella 登录权限
a. 查看现在的角色属性
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 bella     | Create DB, Cannot login                        | {}
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 renee     | Create DB                                      | {}
 sandy     |                                                | {}

postgres=#
b. 赋予登录权限
postgres=# ALTER ROLE bella WITH LOGIN;
ALTER ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 bella     | Create DB                                      | {}
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 renee     | Create DB                                      | {}
 sandy     |                                                | {}

postgres=#
5.2 赋予renee 创建角色的权限
postgres=# ALTER ROLE renee WITH CREATEROLE;
ALTER ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 bella     | Create DB                                      | {}
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 renee     | Create role, Create DB                         | {}
 sandy     |                                                | {}

postgres=#
5.3 赋予david 带密码登录权限
postgres=# ALTER ROLE david WITH PASSWORD 'ufo456';
ALTER ROLE
postgres=#
5.4 设置sandy 角色的有效期
postgres=# ALTER ROLE sandy VALID UNTIL '2014-04-24';
ALTER ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 bella     | Create DB                                      | {}
 david     |                                                | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 renee     | Create role, Create DB                         | {}
 sandy     |                                                | {}

postgres=# SELECT * from pg_roles ;
 rolname  | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword |     rolvaliduntil      | rolconfig |  oid 
----------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+-------------+------------------------+-----------+-------
 postgres | t        | t          | t             | t           | t            | t           | t              |           -1 | ********    |                        |           |    10
 bella    | f        | t          | f             | t           | f            | t           | f              |           -1 | ********    |                        |           | 49440
 renee    | f        | t          | t             | t           | f            | t           | f              |           -1 | ********    |                        |           | 49442
 david    | f        | t          | f             | f           | f            | t           | f              |           -1 | ********    |                        |           | 49438
 sandy    | f        | t          | f             | f           | f            | t           | f              |           -1 | ********    | 2014-04-24 00:00:00+08 |           | 49439
(5 rows)

postgres=#

六、角色赋权/角色成员

在系统的角色管理中，通常会把多个角色赋予一个组，这样在设置权限时只需给该组设置即可，撤销权限时也是从该组撤销。在PostgreSQL中，首先需要创建一个代表组的角色，之后再将该角色的membership 权限赋给独立的角色即可。
6.1 创建组角色
postgres=# CREATE ROLE father login nosuperuser nocreatedb nocreaterole noinherit encrypted password 'abc123';
CREATE ROLE
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 bella     | Create DB                                      | {}
 david     |                                                | {}
 father    | No inheritance                                 | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 renee     | Create role, Create DB                         | {}
 sandy     |                                                | {}

postgres=#
6.2 给father 角色赋予数据库test 连接权限和相关表的查询权限。
postgres=# GRANT CONNECT ON DATABASE test to father;
GRANT
postgres=# \c test renee
You are now connected to database "test" as user "renee".
test=> \dt
No relations found.
test=> CREATE TABLE emp (
test(> id serial,
test(> name text);
NOTICE:  CREATE TABLE will create implicit sequence "emp_id_seq" for serial column "emp.id"
CREATE TABLE
test=> INSERT INTO emp (name) VALUES ('david'); 
INSERT 0 1
test=> INSERT INTO emp (name) VALUES ('sandy');
INSERT 0 1
test=> SELECT * from emp;
 id | name 
----+-------
  1 | david
  2 | sandy
(2 rows)

test=> \dt
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | emp  | table | renee
(1 row)

test=> GRANT USAGE ON SCHEMA public to father;
WARNING:  no privileges were granted for "public"
GRANT
test=> GRANT SELECT on public.emp to father;
GRANT
test=>
6.3 创建成员角色
test=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# CREATE ROLE son1 login nosuperuser nocreatedb nocreaterole inherit encrypted password 'abc123';
CREATE ROLE
postgres=#
这里创建了son1 角色，并开启inherit 属性。PostgreSQL 里的角色赋权是通过角色继承（INHERIT）的方式实现的。
6.4 将father 角色赋给son1
postgres=# GRANT father to son1;
GRANT ROLE
postgres=#
还有另一种方法，就是在创建用户的时候赋予角色权限。
postgres=# CREATE ROLE son2 login nosuperuser nocreatedb nocreaterole inherit encrypted password 'abc123' in role father;
CREATE ROLE
postgres=#
6.5 测试son1 角色
postgres=# \c test son1
You are now connected to database "test" as user "son1".
test=> \dt
       List of relations
 Schema | Name | Type  | Owner
--------+------+-------+-------
 public | emp  | table | renee
(1 row)

test=> SELECT * from emp;
 id | name 
----+-------
  1 | david
  2 | sandy
(2 rows)

test=>
用renee 角色新创建一张表，再次测试
test=> \c test renee
You are now connected to database "test" as user "renee".
test=> CREATE TABLE dept (
test(> deptid integer,
test(> deptname text);
CREATE TABLE
test=> INSERT INTO dept (deptid, deptname) values(1, 'ts');
INSERT 0 1
test=> \c test son1
You are now connected to database "test" as user "son1".
test=> SELECT * from dept ;
ERROR:  permission denied for relation dept
test=>
son1 角色只能查询emp 表的数据，而不能查询dept 表的数据，测试成功。
6.6 查询角色组信息
test=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=#
postgres=# \du
                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 bella     | Create DB                                      | {}
 david     |                                                | {}
 father    | No inheritance                                 | {}
 postgres  | Superuser, Create role, Create DB, Replication | {}
 renee     | Create role, Create DB                         | {}
 sandy     |                                                | {}
 son1      |                                                | {father}
 son2      |                                                | {father}

postgres=#
“ Member of ” 项表示son1 和son2 角色属于father 角色组。

 

时间： 2024-10-31 00:16:28

PostgreSQL 角色与用户管理介绍_PostgreSQL的相关文章

PostgreSQL教程(十二)：角色和权限管理介绍_PostgreSQL

PostgreSQL是通过角色来管理数据库访问权限的,我们可以将一个角色看成是一个数据库用户,或者一组数据库用户.角色可以拥有数据库对象,如表.索引,也可以把这些对象上的权限赋予其它角色,以控制哪些用户对哪些对象拥有哪些权限.     一.数据库角色:     1. 创建角色:   复制代码 代码如下:     CREATE ROLE role_name;       2. 删除角色:   复制代码 代码如下:     DROP ROLE role_name;         3. 查询角色:

ESFramework介绍之（18）―― Tcp用户管理器组件

    当我们的应用中客户端与AS之间是通过Tcp进行通信的时候,通常,应用也要求管理所有在线的用户.这种管理至少包含以下几点:(1) 当用户上线时,记录上线时间(2) 当用户请求服务时,记录请求服务的时间.服务的类型.本次服务下载的数据量(3) 当用户下线时,记录下线时间.并把本次用户登录.请求服务过程中的所有信息持久化保存(如记录到数据库)     在ESFramework中,实现这种管理的是ITcpUserManager组件,通常,该组件由AS使用(因为在AS.FS.IRAS中只有AS是必

PostgreSQL 逻辑结构 和 权限体系 介绍

本文旨在帮助用户理解PostgreSQL的逻辑结构和权限体系,帮助用户快速的理解和管理数据库的权限. 逻辑结构 最上层是实例,实例中允许创建多个数据库,每个数据库中可以创建多个schema,每个schema下面可以创建多个对象.对象包括表.物化视图.操作符.索引.视图.序列.函数.... 等等. 在数据库中所有的权限都和角色(用户)挂钩,public是一个特殊角色,代表所有人.超级用户是有允许任意操作对象的,普通用户只能操作自己创建的对象.另外有一些对象是有赋予给public角色默认权限的,所以

一天学会PostgreSQL应用开发与管理 - 8 PostgreSQL 管理

本章大纲 一.权限体系 1 逻辑结构 2 权限体系 3 schema使用 , 特别注意 4 用户 5 public 6 如何查看和解读一个对象的当前权限状态 二.索引介绍 1 索引有什么用? 2 索引的类型 3 索引合并扫描 4 表膨胀检查 5 检查膨胀 6 索引维护 三.系统配置 1 存储.文件系统规划 2 网络规划 3 CPU评估 4 内核配置 5 资源限制 6 防火墙配置 四.数据库初始化 1 initdb 介绍 2 postgresql.conf参数配置 3 pg_hba.conf数据库

MySQL用户管理（转）

mysql|用户管理 MySQL用户管理[晏子]  http://clyan.hongnet.com/index.html MySQL管理员应该知道如何设置MySQL用户账号,指出哪个用户可以连接服务器,从哪里连接,连接后能做什么.MySQL 3.22.11开始引入两条语句使得这项工作更容易做:GRANT语句创建MySQL用户并指定其权限,而REVOKE语句删除权限.两条语句扮演了mysql数据库的前端角色,并提供与直接操作这些表的内容不同的另一种方法.CREATE和REVOKE语句影响4个表:

MySQL用户管理

mysql|用户管理 MySQL管理员应该知道如何设置MySQL用户账号,指出哪个用户可以连接服务器,从哪里连接,连接后能做什么.MySQL 3.22.11开始引入两条语句使得这项工作更容易做:GRANT语句创建MySQL用户并指定其权限,而REVOKE语句删除权限.两条语句扮演了mysql数据库的前端角色,并提供与直接操作这些表的内容不同的另一种方法.CREATE和REVOKE语句影响4个表:授权表 内容 user 能连接服务器的用户以及他们拥有的任何全局权限 db 数据库级权限 tables

SYBASE数据库用户管理基本概念(howto)

在SYBASE数据库中,用户管理是系统管理的一个重要组成部分,但其重要性却往往被人忽视.许多时候,操作数据库的命令不成功常与用户管理有关,而许多误操作命令的发生也可以通过用户管理来避免.因此,弄清用户管理的一些基本概念,对成功进行数据库系统管理有重要的作用. 下面我们将对SYBASE数据库用户的基本概念加以解释说明. 一.SQL SERVER注册用户和数据库用户 刚接触到这两个概念的时候,我们可能会有所混淆,但只要了解SYBASE SQL SERVER的结构后,就会清楚.SYBASE SQL S

RabbitMQ管理（2）——用户管理

在RabbitMQ中,用户是访问控制(Access Control)的基本单元,且单个用户可以跨越多个vhost进行授权.针对一至多个vhost,用户可以被赋予不同级别的访问权限,并使用标准的用户名和密码来认证用户. 创建用户的命令为:rabbitmqctl add_user {username} {password}.其中username表示要创建的用户名称:password表示创建用户登录的密码.具体创建一个用户名为root.密码为root123的用户: [root@node1 ~]# ra

Oracle 用户管理与权限分配

用户与模式的关系 Oracle数据库的安全保护流程可以分为3个步骤.首先,用户向数据库提供身份识别信息,即提供一个数据库账号.接下来用户还需要证明他们所给出的身份识别信息是有效的,这是通过输入密码来实现的,用户输入的密码经过数据库的核对确认用户提供的密码是否正确.最后,假设密码是正确的,那么数据库认为身份识别信息是可信赖的.此时,数据库将会在基于身份识别信息的基础上确定用户所拥有的权限,即用户可以对数据库执行什么操作.因此,为了确保数据库的安全,首要的问题就是对用户进行管理.  这里所有的用户并