用户，认证，远程登录
http://peopleyun.com/wiki/client-authentication
第11章：用户认证
当一个客户端应用与数据库服务器进行联接时，它声明它将以哪个 YunTable 数据库用户名称进行联接，就象我们登录一台 Unix 计算机一样。在 SQL 环境里， 活跃的数据库用户名决定数据库对象的各种访问权限 — 参阅 第10章 获取更多信息。因此， 实际上我们要限制的是用户可以联接的数据库。
注意: 如 第10章 里解释的那样，YunTable 实际上用"角色"的概念管理权限。在本章里， 我们用数据库用户表示"带有LOGIN权限的角色"。
认证 是数据库服务器建立客户端的标识， 然后通过一些手段判断是否允许此客户端应用（或者运行这个客户端应用的用户）与它所要求的数据库用户名进行联接的过程。
YunTable 提供多种不同的客户端认证方式。 认证某个特定客户端联接所使用的方法可以通过基于（客户端）的主机地址，数据库和用户的方式进行选择； 一些认证方法还允许你通过用户名进行限制。
YunTable 数据库用户名在逻辑上是和服务器运行的操作系统用户名相互独立的。 如果某个服务器的所有用户在那台服务器机器上也有帐号， 那么给数据库用户赋与操作系统用户名是有意义的。不过， 一个接收远程访问的服务器很有可能有许多没有本地操作系统帐号的用户， 因而在这种情况下数据库用户和操作系统用户名之间不必有任何联系。
11.1.pg_hba.conf 文件
客户端认证是由一个配置文件控制的，通常其文件名是 pg_hba.conf， 存放在数据库集群的数据目录里。（HBA 的意思是 host-based authentication：基于主机的认证。） 在yinitdb初始化数据目录的时候，它会安装一个缺省的文件。
文件 pg_hba.conf 的常用格式是一套记录， 每行一条。空白行行被忽略，井号（ # ）开头的注释也被忽略。 一条记录是由若干用空格和/或 tab 分隔的字段组成。 如果字段用引号包围，那么它可以包含空白。记录不能跨行存在。
每条记录声明一种联接类型，一个客户端 IP 地址范围（如果和联接类型相关的话），一个数据库名，一个用户名字， 以及对匹配这些参数的联接使用的认证方法。 第一条匹配联接类型，客户端地址和联接企图请求的数据库名和用户名的记录将用于执行认证。 这个处理过程没有"跨越"或者"回头"的说法：如果选择了一条记录而且认证失败， 那么将不考虑后面的记录。如果没有匹配的记录，那么访问将被拒绝。
每条记录可以下面七种格式之一
local database user auth-method [auth-option]
host database user CIDR-address auth-method [auth-option]
hostssl database user CIDR-address auth-method [auth-option]
hostnossl database user CIDR-address auth-method [auth-option]
host database user IP-address IP-mask auth-method [auth-option]
hostssl database user IP-address IP-mask auth-method [auth-option]
hostnossl database user IP-address IP-mask auth-method [auth-option]
各个字段的含义如下：
local
- 这条记录匹配通过 Unix 域套接字进行的联接企图。 没有这种类型的记录，就不允许 Unix 域套接字的联接。
host
- 这条记录匹配通过 TCP/IP 进行的联接尝试。 host 记录匹配 SSL 和非 SSL 的连接请求。
注意: 除非服务器带着合适的 listen_addresses 配置参数值启动，否则将不可能进行远程的 TCP/IP 连接， 因为缺省的行为是只监听本地自环地址 localhost 的连接。
hostssl
- 这条记录匹配使用 TCP/IP 的 SSL 联接企图。 但必须是使用 SSL 加密的联接。
要使用这个选项，制作服务器的时候必须打开 SSL 支持。而且在服务器启动的时候， 必须SSL选项通过配置选项ssl打开。
hostnossl
- 这个记录与 hostssl 有着正相反的逻辑： 它只匹配那些在 TCP/IP 上不使用 SSL 的连接请求。
database
- 声明记录所匹配的数据库名称。值 all 表明该记录匹配所有数据库， 值 sameuser表示如果被请求的数据库和请求的用户同名，则匹配。 samerole 表示请求的用户必须是一个与数据库同名的组中的成员。 （samegroup 是一个已经废弃了的，但仍然被接受的 samerole 的同义词。） 在其他情况里，这就是一个特定的 YunTable 的名字。 我们可以通过用逗号分隔的方法声明多个数据库。 可以通过对该文件前缀 @ 来声明一个独立的包含数据库名的文件。
user
- 为这条记录声明所匹配的数据库用户。值 all 表明它匹配于所有用户。 否则，它就是特定数据库用户的名字。或者是一个前缀 + 的组名称。 （请注意，在 YunTable 里，用户和组没有真正的区别， + 实际上只是意味着"匹配任何直接或者间接属于这个角色的成员"， 而没有 + 记号的名字只匹配指定的角色。） 多个用户名可以通过用逗号分隔的方法声明。 一个包含用户名的文件可以通过在文件名前面前缀 @ 来声明。
CIDR-address
- 声明这条记录匹配的客户端机器的 IP 地址范围。它包含一个标准的点分十进制的 IP 地址和一个 CIDR 掩码长度。 （IP 地址只能用数值声明，不能用域或者主机名。） 掩码长度表示客户端 IP 地址必须匹配的高位二进制位数。 在给出的 IP 地址里，这个长度的右边的二进制位必须为零。 在 IP 地址，/，和 CIDR 掩码长度之间不能有空白。
典型的 CIDR 地址是 172.20.143.89/32，这表明一个主机， 或者 172.20.143.0/24 表示一个网络。 要声明单个主机，给 IPv4 地址声明 CIDR 掩码 32，给 IPv6 地址声明 128。
以 IPv4 格式给出的 IP 地址会匹配那些拥有对应地址的 IPv6 连接，比如 127.0.0.1 将匹配 IPv6 地址 ::ffff:127.0.0.1。 一个以 IPv6 格式给出的记录将只匹配 IPv6 连接，即使对应的地址在 IPv4-in-IPv6 范围内。请注意如果系统的 C 库不支持 IPv6 地址，那么 IPv6 的格式将被拒绝。
这个域只适用于 host，hostssl 和 hostnossl 记录。
IP-address
IP-mask
- 这些方法可以用于作为 CIDR-address 表示法的替补。 它不是声明掩码的长度，而是在另外一个字段里声明实际的掩码。 比如，255.0.0.0 表示 IPv4 CIDR 掩码长度 8，而 255.255.255.255 表示 CIDR 掩码长度 32。 同样的匹配逻辑将用于一个点分的 IP-mask。
这些字段只适用于 host， hostssl， 和 hostnossl 记录。
auth-method（认证方法）
- 声明通过这条记录联接的时候使用的认证方法。可能的选择在下面简介
trust 无条件地允许联接。
reject 联接无条件拒绝。常用于从一个组中"过滤"某些主机。
md5 要求客户端提供一个 MD5 加密的口令进行认证。
password 要求客户端提供一个未加密的口令进行认证。
ident 获取客户的操作系统名（对于 TCP/IP 联接，用户的身份是通过与运行在客户端上的 ident 服务器联接进行判断的，对于本地联接，它是从操作系统获取的。） 然后检查一下，看看用户是否允许以要求的数据库用户进行联接， 方法是参照在 ident 关键字后面声明的映射。
pam 使用操作系统提供的可插入的认证模块服务 （Pluggable Authentication Modules） （PAM）来认证。
auth-option
- 这个可选的字段的含义取决于选择的认证方法。
用 @ 构造包含的文件是当作一列名字读取的， 这些名字可以用空白或者逗号分隔。注释用 # 引入， 就像在 pg_hba.conf 里那样，允许嵌套 @ 构造。 除非跟在 @ 后面的文件名是一个绝对路径，否则被当作与包含该文件的目录相对的路径。
因为认证时系统是为每个联接请求顺序检查 pg_hba.conf 里的记录的，所以这些记录的顺序是非常关键的。 通常，靠前的记录有比较严的联接匹配参数和比较弱的 认证方法，而靠后的记录有比较松的匹配参数和比较严的认证方法。 比如，我们一般都希望对本地 TCP/IP 联接使用 trust 认证， 而对远端的 TCP/IP 联接要求口令。在这种情况下我们将 trust 认证方法用于来自 127.0.0.1 的联接，这条记录将出现在允许更广泛的客户端 IP 地址的使用口令认证的记录前面。
在启动和服务器进程（ sqlserver ）收到SIGHUP 信号的时候， 系统都会重新装载 pg_hba.conf 文件。 如果你在活跃的系统上编辑了该文件，你就需要用 kill 向 sqlserver 发一个 SIGHUP信号，好让它重新读取该文件。
在 Example 11-1 里是 pg_hba.conf 记录的一些例子。 阅读下文理解不同认证方法的细节
- Example 11-1. pg_hba.conf 记录的例子
# 允许在本机上的任何用户使用 Unix 域套接字（本地连接的缺省）
# 以任何数据库用户身份联接任何数据库
#
#
# TYPE DATABASE USER CIDR-ADDRESS METHOD
local all all trust# 和上面相同，但是使用的是自环的（loopback）TCP/IP 连接
#
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host all all 127.0.0.1/32 trust# 和上面一行相同，但是用的是独立的掩码字段
#
# TYPE DATABASE USER IP-ADDRESS METHOD
host all all 127.0.0.1 255.255.255.255 trust# 允许 IP 地址为 192.168.93.x 的任何主机与数据库
# "yuntable" 相连，用与他们在自己的主机上相同 ident 的用户名标识他自己
# （通常是他的 Unix 用户名）
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host yuntable all 192.168.93.0/24 ident sameuser# 允许来自主机 192.168.12.10 的用户与 "yuntable" 数据库联接，
# 只要该用户提供了在正确的口令。
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host yuntable all 192.168.12.10/32 md5# 如果前面没有其它 "host" 行，那么下面两行将拒绝所有来自
# 192.168.54.1 的联接请求 (因为前面的记录先匹配),
# 但是允许来自互联网上其它任何地方的有效的 Kerberos 5 认证的联接
# 零掩码表示不考虑主机 IP 的任何位。因此它匹配任何主机：
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host all all 192.168.54.1/32 reject
host all all 0.0.0.0/0 krb5# 允许来自 192.168.x.x 的任何用户与任意数据库联接，只要他们通过 ident 检查
# 但如果 ident 说该用户是 "bryanh" 而他要求以 YunTable 用户 "guest1" 联接，
# 那么只有在 `pg_ident.conf' 里有 "omicron" 的映射，说 "bryanh" 允许以
# "guest1" 进行联接时才真正可以进行联接。
#
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host all all 192.168.0.0/16 ident omicron# 如果下面是用于本地联接的仅有的三行，那么它们将允许本地用户
# 只和它们自己的数据库联接(数据库名和数据库用户名同名)，
# 只有管理员和角色 "support" 里的成员例外，他们可以联接到任何数据库。
# 文件 $PGDATA/admins 列出了那些允许与所有数据库联接的用户名。
# 在所有情况下都需要口令。
#
# TYPE DATABASE USER CIDR-ADDRESS METHOD
local sameuser all md5
local all @admins md5
local all +support md5# 上面最后两行可以合起来写成一行
local all @admins,+support md5# 数据库字段也可以使用列表和文件名：
local db1,db2,@demodbs all md5
11.2.认证方法
下面更详细地描述认证方法
信任认证
如果声明了 trust （信任）认证模式， YunTable 就假设任何可以联接到服务器的人都可以以任何他声明的数据库用户名（包括超级用户）连接。 当然，在 database 和 user 字段里面的限制仍然适用。 这个方法应该用于那些在联接到服务器已经有足够操作系统层次保护的环境里。
trust 认证对于单用户工作站的本地联接是非常合适和方便的。 通常它本身并不适用于多用户环境的机器。 不过，即使在多用户的机器上，你也可以使用 trust， 只要你利用文件系统权限限制了对服务器的 Unix 域套接字文件的访问。
设置文件系统权限只能帮助 Unix 套接字联接。它不会限制本地 TCP/IP 联接； 因此，如果你想利用文件系统权限来控制本地安全，那么删除 pg_hba.conf 里的 host … 127.0.0.1 … 行，或者把它改为一个非 trust 的认证方法。
trust 认证模式只适合 TCP/IP 连接，只有在你信任那些在 pg_hba.conf 里声明为 trust，允许联接到服务器的行上的所有机器上面的所有用户的时候才是合适的。 很少有理由使用 trust 作为任何除来自localhost（127.0.0.1）以外的 TCP/IP 联接的认证方式。
口令认证
以口令为基础的认证方法包括 md5 和 password。这些方法操作上非常类似，只不过口令通过联接传送的方法不同： 分别是，MD5散列，明文。
如果你担心口令被窃听("sniffing")， 那么 md5 比较合适。如果我们在开放的互联网上使用， 应该尽可能避免使用 password（除非你在联接上使用了 SSL， SSH，或者其他通讯安全的联接封装。）
YunTable 数据库口令与任何操作系统用户口令无关。 各个数据库用户的口令是存储在pg_authid系统表里面的。 口令可以用 SQL 语言命令 CREATE USER 和 ALTER USER 等管理，也就是说， CREATE USER foo WITH PASSWORD 'secret';。缺省时，如果没有明确地设置口令，存储的口令是空并且该用户的口令认证总会失败。
要限制允许访问某数据库的用户集， 可以在 pg_hba.conf 中的 user 字段列出这些用户，就像前面解释的那样。
基于 Ident 的认证
身份（ident）认证方法的运做模式是通过获取客户端的操作系统用户名， 然后使用一个列出许可的系统用户和对应的数据库用户对应关系的映射文件， 判断对应许可的数据库用户名之方法来认证。 判断客户端的用户名是非常关键的安全点，根据连接类型的不同，它的实现方法也略有不同。
- 透过 TCP/IP 的身份认证
- 实际上每个类Unix的操作系统都带着一个缺省时侦听113端口的身份服务器。 身份服务器的基本功能是回答类似这样的问题： "是什么用户从你的端口X初始化出来联接到我的端口Y上来了？"。 因为在建立起物理联接后，YunTable 既知道 X 也知道 Y， 因此它可以询问运行尝试联接的客户端的主机，并且理论上可以用这个方法判断发起联接的操作系统用户。
这样做的缺点是它取决于客户端的完整性：如果客户端不可信或者被攻击者攻破， 而且它们可以在113端口上运行任何程序并且返回他们选择的任何用户的话，就无法认证了。 因此这个认证方法只适用于封闭的网络， 这样的网络里的每台客户机都处于严密的控制下并且数据库和操作系统管理员可以比较方便地联系上。 换句话说，你必须信任运行身份(ident)服务的机器。所以有此警告：身份标识协议并不适用于认证或者访问控制协议。
有些身份服务器有一个非标准的选项，导致返回的用户名是加密的， 使用的是只有原机器的管理员知道的一个密钥。在与 YunTable 配合使用身份认证的时候， 你一定不能使用这个选项，因为 YunTable 没有任何方法对返回的字串进行解密以获取实际的用户名。
- 透过本地套接字的身份认证
- 在支持用于 Unix 域套接字的SO_PEERCRED请求的系统上， （目前是 Linux， FreeBSD， NetBSD， OpenBSD和 BSD/OS）， 身份认证也可以用于局部联接。这个时候，使用身份认证不会增加安全风险； 实际上这也是在这种系统上使用本地联接时的优选方法。
在没有 SO_PEERCRED 请求的系统上，身份认证只能通过TCP/IP连接获取。 如果需要绕开这个限制，我们可以声明 localhost 地址 127.0.0.1，然后让连接指向这个地址。 这个方法适用于你相信本机身份认证服务器的场合。
- Ident 映射
- 当使用以身份为基础的认证时，在判断了初始化联接的操作系统用户的名字后， YunTable 判断他是否可以以他所请求的数据库用户的身份联接。 这个判断是由跟在pg_hba.conf 文件里的 ident 关键字后面的身份映射控制的。 有一个预定义的身份映射是sameuser，表示任何操作系统用户都可以以同名数据库用户进行联接 （如果后者存在的话）。其他映射必须手工创建。
非 sameuser 的身份映射定义在身份映射文件里， 其名字缺省是 pg_ident.conf， 并且缺省存放在集群的数据目录里。 （不过，我们可以把映射文件放在其它地方，参阅 ident_file 配置参数。） 身份映射文件包含下面通用的格式：
map-name ident-username database-username
- 注释和空白和 pg_hba.conf 文件里的一样处理。map-name 是将用于在pg_hba.conf里引用这个映射的任意名称。 另外两个域声明某个操作系统用户被允许以哪个数据库用户的身份进行联接。 同一个map-name 可以重复用于在一个映射里声明更多的用户映射。 对一个操作系统用户可以映射为多少个数据库用户没有限制，反之亦然。
在系统启动和主服务器（ ysqlserver ）收到一个 SIGHUP 信号的时候会读取 pg_ident.conf 文件。如果你在一台活跃的系统上编辑该文件， 那么你需要给 ysqlserver 发信号（用 pg_ctl reload 或者 kill -HUP 令其重新读取该文件。）
Example 11-2里是一个可以和在 Example 11-1 里面演示的pg_hba.conf文件配合使用的 pg_ident.conf 文件。 在这个例子的设置里，任何登录到 192.168 网络里的机器的用户，如果用户名不是 bryanh，ann，或 robert就不能获准访问。 Unix 用户robert只有在试图以yuntable用户 bob身份联接时才允许访问，而不能是 robert 或其他什么身份。 ann 将只允许以ann的身份联接。 用户bryanh允许以他自己的 bryanh 身份或者做为 guest1 进行联接。 - Example 11-2. 一个 pg_ident.conf 文件例子
# MAPNAME IDENT-USERNAME PG-USERNAME
omicron bryanh bryanh
omicron ann ann
# bob 在这台机器上的用户名是 robert
omicron robert bob
# bryanh 也可以以 guest1 身份连接
omicron bryanh guest1
PAM 认证
这个认证方法操作起来类似 password， 只不过它使用 PAM（Pluggable Authentication Modules）作为认证机制。你可以在文件 pg_hba.conf 的 pam 关键字后面提供自己的可选服务名。 PAM 只用于验证用户名/口令对。因此，在使用 PAM 进行认证之前，用户必须已经存在于数据库里。
11.3.认证问题
真正的认证失败以及相关的问题通常由类似下面的错误信息表白自身。
FATAL: no pg_hba.conf entry for host "123.123.123.123", user "andym", database "testdb"
这条信息出现的最大可能是你已经联接了服务器，但她不愿意和你说话。 就象信息自己表示的那样，服务器拒绝了联接请求，因为她没有在她的 pg_hba.conf 配置文件里找到匹配的记录。
Password authentication failed for user 'andym'
这样的信息表示你联接了服务器，并且她也愿意和你交谈， 但是你必须通过pg_hba.conf 文件里声明的认证方法。 检查你提交的口令，或者如果错误信息提到这些 Kerberos 或 IDENT 认证类型时检查你的这些软件。
FATAL 1: user "andym" does not exist
这是表示此用户不存在的另一方法
FATAL 1: Database "testdb" does not exist
你试图联接的数据库不存在。请注意如果你没有声明数据库名，缺省是数据库用户名，这可能正确也可能不正确。
请注意服务器日志可能包含比报告给客户端的更多的有关认证失败的信息。如果你被失败的原因搞糊涂了，那么请检查日志。
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 角色组。
七、参考
- PostgreSQL 官方资料：http://www.postgresql.org/docs/9.2/static/user-manag.html
http://yp.oss.org.cn/software/show_resource.php?resource_id=1018
安装PostgreSQL数据库之后，默认是只接受本地访问连接。如果想在其他主机上访问PostgreSQL数据库服务器，就需要进行相 应的配置。配置远程连接PostgreSQL数据库的步骤很简单，只需要修改data目录下的pg_hba.conf和postgresql.conf, 其中pg_hba.conf是用来配置对数据库的访问权限，postgresql.conf文件用来配置PostgreSQL数据库服务器的相应的参数。 下面介绍配置的步骤：
1.修改pg_hba.conf文件，配置用户的访问权限：
# TYPE DATABASE USER CIDR-ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all trust
# IPv4 local connections:
host all all 127.0.0.1/32 trust
host all all 192.168.1.0/24 md5
# IPv6 local connections:
host all all ::1/128 trust
其中红色标识的内容为新添加的内容，表示允许网段192.168.1.0上的所有主机使用所有合法的数据库用户名访问数据库，并提供加密的密码验 证。在我们的环境中，我们需要在主机192.168.1.5上使用postgres用户访问192.168.1.9上的PostgreSQL数据库。
2.修改postgresql.conf文件，将数据库服务器的监听模式修改为监听所有主机发出的连接请求。
定位到#listen_addresses='localhost'。PostgreSQL安装完成后，默认是只接受来在本机localhost的连接请 求，通过将改行内容修改为listen_addresses='*'来允许数据库服务器监听来自任何主机的连接请求：
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
# (change requires restart)
修改之后，保存并退出，然后重起数据库，就可以在在远程机器上访问PostgreSQL数据库了。
另外，数据库管理小贴士：
matrix数据库使用的是PostgreSQL数据库。你可以安装一个类似phpmyadmin的管理 软件，叫phppgadmin。 主页：http://phppgadmin.sourceforge.net/doku.php?id=start 另外它还有一个windows下的远程管理软件叫：pgadmin 主页：http://www.pgadmin.org/ 终端的管控命令叫: pgsql
http://my.oschina.net/dddpeter/blog/116153
1.创建用户
|
1
2
3
4
5
|
sudo -s -u postgrespsqlpostgres# CREATE USER xxxx1 WITH PASSWORD 'xxxx';postgres# CREATE DATABASE xxxx2;postgres# GRANT ALL PRIVILEGES ON DATABASE xxxx2 to xxxx1; |
2.修改密码
alter user postgres with password 'foobar';
3.创建数据库
|
1
|
createdb--encoding=UTF8 --owner=foo --template=template_postgis -Ufoo |
参数： --encoding=UTF8 设置数据库的字符集
--owner=foo 设置数据库的所有者
--tmplate=template_postgis 设置建库的模板，该模板支持空间数据操作
--Ufoo 用foo用户身份建立数据库
http://www.ruanyifeng.com/blog/2013/12/getting_started_with_postgresql.html
日期： 2013年12月22日
自从MySQL被Oracle收购以后，PostgreSQL逐渐成为开源关系型数据库的首选。
本文介绍PostgreSQL的安装和基本用法，供初次使用者上手。以下内容基于Debian操作系统，其他操作系统实在没有精力兼顾，但是大部分内容应该普遍适用。
一、安装
首先，安装PostgreSQL客户端。
sudo apt-get install postgresql-client
然后，安装PostgreSQL服务器。
sudo apt-get install postgresql
正常情况下，安装完成后，PostgreSQL服务器会自动在本机的5432端口开启。
如果还想安装图形管理界面，可以运行下面命令，但是本文不涉及这方面内容。
sudo apt-get install pgadmin3
二、添加新用户和新数据库
初次安装后，默认生成一个名为postgres的数据库和一个名为postgres的数据库用户。这里需要注意的是，同时还生成了一个名为postgres的Linux系统用户。
下面，我们使用postgres用户，来生成其他用户和新数据库。好几种方法可以达到这个目的，这里介绍两种。
第一种方法，使用PostgreSQL控制台。
首先，新建一个Linux新用户，可以取你想要的名字，这里为dbuser。
sudo adduser dbuser
然后，切换到postgres用户。
sudo su - postgres
下一步，使用psql命令登录PostgreSQL控制台。
psql
这时相当于系统用户postgres以同名数据库用户的身份，登录数据库，这是不用输入密码的。如果一切正常，系统提示符会变为"postgres=#"，表示这时已经进入了数据库控制台。以下的命令都在控制台内完成。
第一件事是使用\password命令，为postgres用户设置一个密码。
\password postgres
第二件事是创建数据库用户dbuser（刚才创建的是Linux系统用户），并设置密码。
CREATE USER dbuser WITH PASSWORD 'password';
第三件事是创建用户数据库，这里为exampledb，并指定所有者为dbuser。
CREATE DATABASE exampledb OWNER dbuser;
第四件事是将exampledb数据库的所有权限都赋予dbuser，否则dbuser只能登录控制台，没有任何数据库操作权限。
GRANT ALL PRIVILEGES ON DATABASE exampledb to dbuser;
最后，使用\q命令退出控制台（也可以直接按ctrl+D）。
\q
第二种方法，使用shell命令行。
添加新用户和新数据库，除了在PostgreSQL控制台内，还可以在shell命令行下完成。这是因为PostgreSQL提供了命令行程序createuser和createdb。还是以新建用户dbuser和数据库exampledb为例。
首先，创建数据库用户dbuser，并指定其为超级用户。
sudo -u postgres createuser --superuser dbuser
然后，登录数据库控制台，设置dbuser用户的密码，完成后退出控制台。
sudo -u postgres psql
\password dbuser
\q
接着，在shell命令行下，创建数据库exampledb，并指定所有者为dbuser。
sudo -u postgres createdb -O dbuser exampledb
三、登录数据库
添加新用户和新数据库以后，就要以新用户的名义登录数据库，这时使用的是psql命令。
psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432
上面命令的参数含义如下：-U指定用户，-d指定数据库，-h指定服务器，-p指定端口。
输入上面命令以后，系统会提示输入dbuser用户的密码。输入正确，就可以登录控制台了。
psql命令存在简写形式。如果当前Linux系统用户，同时也是PostgreSQL用户，则可以省略用户名（-U参数的部分）。举例来说，我的Linux系统用户名为ruanyf，且PostgreSQL数据库存在同名用户，则我以ruanyf身份登录Linux系统后，可以直接使用下面的命令登录数据库，且不需要密码。
psql exampledb
此时，如果PostgreSQL内部还存在与当前系统用户同名的数据库，则连数据库名都可以省略。比如，假定存在一个叫做ruanyf的数据库，则直接键入psql就可以登录该数据库。
psql
另外，如果要恢复外部数据，可以使用下面的命令。
psql exampledb < exampledb.sql
四、控制台命令
除了前面已经用到的\password命令（设置密码）和\q命令（退出）以外，控制台还提供一系列其他命令。
- \h：查看SQL命令的解释，比如\h select。
- \?：查看psql命令列表。
- \l：列出所有数据库。
- \c [database_name]：连接其他数据库。
- \d：列出当前数据库的所有表格。
- \d [table_name]：列出某一张表格的结构。
- \du：列出所有用户。
- \e：打开文本编辑器。
- \conninfo：列出当前数据库和连接的信息。
五、数据库操作
基本的数据库操作，就是使用一般的SQL语言。
# 创建新表
CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);# 插入数据
INSERT INTO user_tbl(name, signup_date) VALUES('张三', '2013-12-22');# 选择记录
SELECT * FROM user_tbl;# 更新数据
UPDATE user_tbl set name = '李四' WHERE name = '张三';# 删除记录
DELETE FROM user_tbl WHERE name = '李四' ;# 添加栏位
ALTER TABLE user_tbl ADD email VARCHAR(40);# 更新结构
ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;# 更名栏位
ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;# 删除栏位
ALTER TABLE user_tbl DROP COLUMN email;# 表格更名
ALTER TABLE user_tbl RENAME TO backup_tbl;# 删除表格
DROP TABLE IF EXISTS backup_tbl;
（完）
一、安装
centos6.4服务器IP：192.168.220.131
window7客户端IP：192.168.199.218
在centos官网http://www.postgresql.org/download/linux/redhat/，通过如下指令安装postgresql
yum install postgresql-server service postgresql initdb chkconfig postgresql on
貌似已经安装过了，版本是8.4，因为看到update字样。不管。
二、本地建立数据库用户和DB，测试连接
su postgres //切换到默认建立的postgres用户 psql //进入postgresql命令控制台 CREATE USER root WITH PASSWORD 'root'; CREATE DATABASE rootdb WITH OWNER = root TEMPLATE = template0 ENCODING = 'UNICODE'; \q //退出postgresql命令控制台 exit //退出postgres用户到root用户 /usr/bin/psql -Uroot rootdb //测试本地环境用户登录
三、windows7客户端连接测试
1）关闭防火墙 service iptables stop
2）修改/var/lib/pgsql/data/postgresql.conf（修改内容listen_addresses = '*'）后，提示错误
FATAL: no pg_hba.conf entry for host "192.168.220.1", user "root", database "rootdb", SSL off
2.1）重启命令 service iptables restart
2.2）可能会提示失败，查看log
2.2.1）确定log文件位置，通过查看postgresql.conf文件
# This is used when logging to stderr:
logging_collector = on # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
# (change requires restart)
# These are only used if logging_collector is on:
log_directory = 'pg_log' # directory where log files are written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%a.log' # log file name pattern,
# can include strftime() escapes
2.2.2）查看log（tail -f /var/lib/pgsql/data/pg_log/postgresql-Sat.log ）
LOG: database system was shut down at 2014-09-06 10:09:32 CST LOG: database system is ready to accept connections LOG: autovacuum launcher started
3）修改/var/lib/pgsql/data/pg_hba.conf ，如下
# TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all all ident # IPv4 local connections: host all all 127.0.0.1/32 md5 host all all 192.168.0.0/32 md5 host all all 0.0.0.0/0 md5 # IPv6 local connections: host all all ::1/128 ident
最后添加了host all all 0.0.0.0/0 md5这么一段，成功连接。
http://blog.csdn.net/yapingxin/article/details/6325673
环境：Debian 6.0.0 + Postgresql 8.4.7
Postgresql 是用 dpkg -i 命令默认安装的，其余安装方式的文件路径可能会不同。如果找不到配置文件请用 find / -name "..." 查找。
一、编辑 pg_hba.conf，配置用户的访问权限
- vi /etc/postgresql/8.4/main/pg_hba.conf
如下：
# "local" is for Unix domain socket connections only
local all all ident
# IPv4 local connections:
host all all 127.0.0.1/32 md5
host all all 172.16.230.0/24 md5
host all all 192.168.98.0/24 md5
上面用黑体标出的两行是我加的，这两行允许这两个网段的用户可以访问到数据库。
二、编辑 postgresql.conf 使数据库服务器监听所有主机发出的连接请求
- vi /etc/postgresql/8.4/main/postgresql.conf
如下：
找到 listen_addresses = 'localhost' 这一行，将它改为：
listen_addresses = '*'
通常这一行会被注释起来。将注释符号“#”去掉令这一行生效。
重起数据库服务后即可用 pgAdmin3 之类的 client 远程访问数据库了。
http://blog.csdn.net/ll136078/article/details/12747403
安 装PostgreSQL数据库之后，默认是只接受本地访问连接。如果想在其他主机上访问PostgreSQL数据库服务器，就需要进行相应的配置。
配置远 程连接PostgreSQL数据库的步骤很简单，只需要修改data目录下的pg_hba.conf和postgresql.conf。
pg_hba.conf：配置对数据库的访问权限，
postgresql.conf：配置PostgreSQL数据库服务器的相应的参数。
下面 介绍配置的步骤：
1.修改pg_hba.conf文件，配置用户的访问权限（#开头的行是注释内容）：
- # TYPE DATABASE USER CIDR-ADDRESS METHOD
- # "local" is for Unix domain socket connections only
- local all all trust
- # IPv4 local connections:
- host all all 127.0.0.1/32 trust
- host all all 192.168.1.0/24 md5
- # IPv6 local connections:
- host all all ::1/128 trust
其中，第7条是新添加的内容，表示允许网段192.168.1.0上的所有主机使用所有合法的数据库用户名访问数据库，并提供加密的密码验证。
其中，数字24是子网掩码，表示允许192.168.1.0--192.168.1.255的计算机访问！
2.修改postgresql.conf文件，将数据库服务器的监听模式修改为监听所有主机发出的连接请求。
定位到#listen_addresses=’localhost’。PostgreSQL安装完成后，默认是只接受来在本机localhost的连接请 求。
将行开头都#去掉，将行内容修改为listen_addresses=’*'来允许数据库服务器监听来自任何主机的连接请求
浙公网安备 33010602011771号