PostgreSQL基础管理知识总结

2016.06.26发布于笔记暂无评论/目录

简要总结下PostgreSQL的一些基础知识和命令,以下内容和例子均基于Debian 8上安装的PostgreSQL 9.4。

PostgreSQL介绍

PostgreSQL和MySQL一样,都是开源关系数据库,虽然流行度差些,但是功能丝毫不弱。

类似于MySQL的mysql命令,PostgreSQL也有一个名为psql的命令行工具来管理数据库和执行SQL命令,下文的SQL命令均通过psql来执行,不再赘述。

管理角色

官方文档参见Chapter 20. Database Roles

PostgreSQL使用角色来管理数据库的访问权限,类似MySQL里的用户。PostgreSQL安装好后默认会创建一个叫postgres的管理员角色,类似Linux系统的root用户。

使用管理员角色创建/删除一个新角色,先连接到数据库:

sudo -u postgres psql -p 5432

PostgreSQL默认监听5432端口,这里默认连接到postgres数据库。这里使用操作系统的同名用户postgres在本地登录,所以不需要输入密码,具体原因见下文的配置文件部分。

CREATE ROLE tom;
DROP ROLE tom;

PostgreSQL的角色都存储在pg_roles表中,也可以直接使用\du命令查看。新建的tom用户不能登录数据库,也不能执行其他的访问操作。

CREATE ROLE tom LOGIN CREATEDB ENCRYPTED PASSWORD 'stupidpass';

这条命令创建的tom角色可以连接数据库(LOGIN)和创建数据库(CREATEDB),密码是stupidpass(加密存储),必须用单引号引用,详见CREATE ROLE文档

有的时候,多个角色会有相同的权限,这时可以通过分组来方便角色管理。

CREATE ROLE tom;
CREATE ROLE alice;
CREATE ROLE joe NOINHERIT;
CREATE ROLE dbadmin LOGIN CREATEDB CREATEROLE;
GRANT dbadmin to tom, alice, joe;

新建的tom和alice角色都是数据库管理员,为了方便管理,只需要先创建一个专门的数据库管理员组角色,然后用GRANT命令把tom和alice添加到这个组里即可。

创建tom和alice的时候,默认会赋予两个角色INHERIT属性(可以继承所在组的所有权限),这样tom和alice就能拥有dbadmin的所有权限,而joe因为配置了NOINHERIT属性,所以就算在dbadmin组里,也没有dbadmin的一系列权限。另外,如果角色本身的权限和继承的权限有冲突,以角色本身的属性为准。

如果需要废除某人的dbadmin相关的权限,只需要

REVOKE dbadmin FROM tom;

需要注意的是,这里的组角色只是为了方便理解才如此称呼,在PostgreSQL里组角色和普通的角色并没有区别。

如果要更改角色,可以使用ALTER ROLE命令,例如:

ALTER ROLE alice NOLOGIN;

可以使用\dg命令查看所有的角色。

管理数据库

官方文档参见Chapter 21. Managing Databases

PostgreSQL安装好后,会先创建postgres,template0和template1三个空数据库。postgresql是第一个数据库,用作登录时的默认数据库,template0和template1是模板数据库,用于创建其他数据库。数据库的信息存储在pg_database表中,也可以使用\list命令查看。

CREATE ROLE tom;
CREATE DATABASE testdb OWNER tom;

这里创建了一个testdb,并设置所有者为tom,tom拥有testdb的一切权限,包括删除testdb。

对于不是所有者的用户,可以使用GRANT命令为其分配访问权限。

\c testdb;
CREATE TABLE no1 (num integer);
CREATE ROLE alice LOGIN;
GRANT ALL on ALL TABLES IN SCHEMA public TO alice;

需要注意的是,PostgreSQL里schema的概念和MySQL完全不同,在MySQL里schema和database是一个含义,但是PostgreSQL里一个database可以包含多个schema,一个schema又可以包含多个表,逻辑上一个表的完整路径为database.schema.table。PostgreSQL里每个数据库默认会有一个名为public的schema,如果创建表的时候没有指明schema,那就默认schema为public。

上面的命令里,GRANT之前需要先连接到testdb数据库,然后再把当前public下所有表的所有访问权限分配给alice。需要注意的是,GRANT ALL on ALL TABLES命令只对已存在的表有效,如果之后再建新表,需要重新执行GRANT命令。如果想省事的话,可以使用ALTER DEFAULT PRIVILEGES命令修改public的默认权限:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL on TABLES to alice;

可以使用\ddp命令查看当前数据库的默认权限,使用\dp查看当前数据库已配置的访问权限。

\dp

输出如下:

                              Access privileges
 Schema | Name | Type  |     Access privileges     | Column access privileges
--------+------+-------+---------------------------+--------------------------
 public | no1  | table | postgres=arwdDxt/postgres+|
        |      |       | alice=arwdDxt/postgres    |
(1 row)

访问权限列的含义如下:

rolename=xxxx -- privileges granted to a role
        =xxxx -- privileges granted to PUBLIC

            r -- SELECT ("read")
            w -- UPDATE ("write")
            a -- INSERT ("append")
            d -- DELETE
            D -- TRUNCATE
            x -- REFERENCES
            t -- TRIGGER
            X -- EXECUTE
            U -- USAGE
            C -- CREATE
            c -- CONNECT
            T -- TEMPORARY
      arwdDxt -- ALL PRIVILEGES (for tables, varies for other objects)
            * -- grant option for preceding privilege

        /yyyy -- role that granted this privilege

所以alice=arwdDxt/postgres可以理解为postgres用户给alice分配了public.no1表的所有访问权限。

配置文件

在Debian上,PostgreSQL的配置文件位于/etc/postgresql/9.4/main/目录下。

登录认证

pg_hba.con是登录认证配置文件,官方文档见Chapter 19. Client Authentication。这个配置文件定义了客户端登录服务器的规则。

例如:

local all  postgres                  peer
host  all  all       127.0.0.1/32    md5
  1. 第一列是连接的方式,可以是local,host或hostssl等值,local表示使用unix domain socket连接,host表示使用TCP socket连接,hostssl表示使用TCP + SSL连接。
  2. 第二列是数据库的名称,all代表所有的数据库。
  3. 第三列是角色(有LOGIN权限)名称,如果角色名前有+符号,表示该角色是组角色,该条规则对组角色直接或间接包含的所有角色都适用。all代表所有的角色。
  4. 第四列是客户端的地址,如果第一列是local则不需要配置这一列,如果使用CIDR记法只需要一列,如果使用IP地址和子网掩码,则需要分两列写。
  5. 倒数第二列是认证方式,常用的有如下几种:
    • trust 不需要任何认证即可登录。
    • reject 不允许登录。
    • md5 密码登录,密码使用md5加密传输。
    • password 密码登录,密码使用明文传输。
    • peer 如果角色名和操作系统的当前用户名相同,则通过认证。
  6. 最后一列是认证选项,可选。

默认配置里,允许postgres管理员在本地使用peer方式进行认证,也就是说使用操作系统的postgres用户登录时不需要密码。

sudo -u postgres psql -p 5432 testdb

PostgreSQL默认只允许本地登录服务器,如果想启用远程密码登录,可以如下配置(为安全考虑,一般不推荐这么做)。

hostssl   all    all    0.0.0.0/0    md5

然后,可以在其他机器上登录服务器:

psql -h <server ip> -p 5432 -U alice -W testdb

参考资料

#database#postgresql#总结

评论