PostgreSQL 札记初阶

环境： Fedora24

安装并初始化数据库

sudo dnf install postgresql-server postgresql-contrib postgresql-server

sudo postgresql-setup --initdb --unit postgresql

启动数据库 && 开机启动

sudo systemctl start postgresql

sudo systemctl enable postgresql

登录解释器

直接运行psql将尝试使用当前Linux系统的登录用户名来登录，可以指定超级账号postgres登录，并且会默认进入和登录名相同的postgres数据库

psql -U postgres

创建角色

值得注意的是，在 PostgreSQL 中，没有用户与用户组的概念，而通称为角色

（一）通过实用程序

安装 PostgreSQL 会自动安装一些实用程序，手动指定postgres用户来执行这个创建用户的程序，按照提示输入新创建用户的密码

createuser -U postgres wilber -P

这里有关于createuser程序的详细参数说明：createuser

PS: 创建一个和Linux用户名相同的数据库用户可以方便登录解释器和日常使用

（二）使用SQL创建

postgres=# create role wilber2;
CREATE ROLE

进入解释器运行\du查看刚刚创建的用户

[root@bogon ~]# psql -U postgres 
用户 postgres 的口令：
psql (9.5.5)
输入 "help" 来获取帮助信息.

postgres=# \du
                             角色列表
 角色名称  |                    属性                    | 成员属于 
-----------+--------------------------------------------+----------
 postgres  | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS | {}
 wilber    |                                            | {}
 wilber2   | 无法登录                                   | {}

创建可以登录并且使用密码的SQL如下

CREATE ROLE name PASSWORD 'string LOGIN'

实用程序创建的用户已经帮忙添加了登录权限，想要使用wilber用户登录还需要为wilber创建一个数据库

删除角色可以使用frop role name;或者dropuser name

创建数据库

同样，可以使用实用工具createdb或者SQL命令来创建

（一）使用createdb

createdb -U postgres -O rolename dbname

（二）使用SQL创建

CREATE DATABASE dbname OWNER rolename;

使用\l命令可以查看数据库列表

新用户登录

以指定用户连接数据库，如果不指定数据库，那么程序将默认使用和角色名称相同的数据库

# 两种形式都可以
psql dbname username
psql -U username dbname

使用数据库

使用psql -U wilber可以登录解释器并操作数据库

[root@bogon ~]# psql -U wilber
用户 wilber 的口令：
psql (9.5.5)
输入 "help" 来获取帮助信息.

wilber=> create table cities (
wilber(> name varchar(80),
wilber(> location point
wilber(> );
CREATE TABLE
wilber=> insert into cities values ('Harbin', '(123, 456)');
INSERT 0 1
wilber=> select * from cities;
  name  | location 
--------+-----------
 Harbin | (123,456)
(1 行记录)

wilber=>

如果使用应用程序，如上创建的用户及数据库，就可以安全的使用了，因为用户没有权限访问不是它自己的数据库，也没有管理数据库的权限

赋予权限

拿刚刚创建的wilber用户举例

1，没有访问别人数据库的权限

[root@bogon ~]# psql -U wilber postgres
用户 wilber 的口令：
psql (9.5.5)
输入 "help" 来获取帮助信息.

postgres=> \d
                关联列表
 架构模式 |   名称   |  类型  |  拥有者 
----------+----------+--------+----------
 public   | cities   | 数据表 | postgres
(1行记录)

postgres=> select * from cities;
ERROR:  permission denied for relation cities

2，没有创建数据库的权限

[root@bogon ~]# createdb -U wilber -O wilber wilberdb2
口令: 
createdb: 创建数据库失败: ERROR:  permission denied to create database

一个数据库角色可以有一系列属性，这些属性定义他的权限，以及与客户认证系统的交互。可以在创建角色的时候就赋予权限

# 登录权限
CREATE ROLE name LOGIN; 
# 设置密码/登录
CREATE ROLE name PASSWORD 'password' LOGIN; 
# 设置密码/登录/创建数据库
CREATE ROLE name PASSWORD 'password' LOGIN CREATEDB;

修改已经存在的角色权限

# 修改角色拥有创建角色和创建数据库的权限
ALTER ROLE name CREATEROLE CREATEDB;

在 http://www.postgres.cn/docs/9.3/role-attributes.html 中介绍

提示: 创建一个具有CREATEDB和CREATEROLE权限，但是并非超级用户的角色是一个很好的习惯，你可以使用这个角色进行所有日常的数据库和角色管理。这个方法避免了以超级用户操作时，发生误操作导致的严重后果。

可以创建一个名为admin的组，来代替postgres：

CREATE ROLE admin PASSWORD 'password' CREATEROLE CREATEDB;

之前创建过wilber用户只拥有登录权限，将admin组权限赋给wilber就可以使得wilber拥有创建角色和数据库的权限，在平时维护时使用

postgres=# GRANT admin to wilber;
GRANT ROLE
postgres=# \du
                             角色列表
 角色名称  |                    属性                    | 成员属于 
-----------+--------------------------------------------+----------
 admin     | 建立角色, 建立 DB, 无法登录                | {}
 wilber    |                                            | {admin}
 wilber2   | 无法登录                                   | {}

然后这个admin组内的用户就拥有了admin的权限

角色属性LOGIN, SUPERUSER, CREATEDB, CREATEROLE 可以被认为是特殊的权限，但是它们从来不会像数据库对象上的普通权限那样继承。你必须明确地SET ROLE到一个特殊的角色，这个角色应该是拥有这些属性的角色，然后才能利用这些属性。继续上面的例子，我们也可以选择给admin角色赋予 CREATEDB和CREATEROLE权限。然后，以joe 连接的会话不会立即有这些权限，只有在SET ROLE admin之后才有。

所以，使用wilber创建数据库之前先切换为admin权限

SET ROLE admin;
CREATE DATABASE dbname;

# 创建完成切换回自己的角色
RESET ROLE;

使用admin创建的数据库所有者为admin，当wilber连接admin角色创建的数据库后可以创建表，并且表的所有者为wilber，同组的用户不能访问对方创建的表，即使切换到admin角色也没有权限

东东's Blog