Posted Updated Database / SQL DB17 minutes read (About 2484 words)

Postgre SQL笔记

安装

  • 交互式客户端：postgresql
  • 服务器：postgres-server
  • 额外功能：postgresql-contrib
  • 开发工具：postgresql-devel

OpenSuSe

1
2
$ sudo zypper in postgresql postgresql-server \
	postgresql-contrib postgresql-devel

CentOS

1
2
$ sudo yum install postgresql postgresql-server \
	postgresql-contrib postgresql-devel

其他版本

  • 从中选择合适版本下载：Postgres Yum repositories

    1
    $ wget https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm

  • 安装下载的RPM（依赖EPEL repo）

    1
    $ sudo yum install pgdg-centos96-9.6-3.noarch.rpm

  • 更新Yum、安装指定PG版本

    1
    2
    $ sudo yum update
    $ sudo yum install postgresql96-sever postgresql96-contrib
  • 安装的PG带有版本后缀，初始化、启动时注意

配置

  • postgres安装完成后，默认创建Linux用户

    • 用户密码为空，要为其设置密码以切换到其
      1
      2
      $ sudo passwd postgres
      $ su - postgres
    • 用户目录默认是/var/lib/pgsql
    • 很多命令可以切换到用户postgres直接执行
  • 初始化数据库簇后，默认创建数据库角色postgres、数据库 postgres

初始化

  • 创建新PostgreSQL数据库簇

    1
    2
    3
    $ sudo postgresql-setup initdb
    	# 或
    $ sudo inidb -D /var/lib/pgsql/data
    • 默认数据库存储路径为/var/lib/pgsql/data

  • 开启PG密码认证：修改host-based authentication设置

    1
    2
    3
    4
    # /var/lib/pgsql/data/pg_hba.conf
    # TYPE		DATABASE	USER	ADDRESS			MEHTOD
    host		all			all		127.0.0.1/32	md5
    host		all			all		::1/128			md5
    • 替换默认ident为md5开启密码认证
    • 修改之后需要重启PG

  • 修改postgres用户密码，以可以通过密码作为postgres连接 数据库

    1
    2
    3
    $ su - postgres
    $ psql -d template1 -c "ALTER USER postgres with password '<passwd>'"
    	# 也可以在数据库prompt中执行

启动数据库

  • 作为服务：start、enablePG

    1
    2
    $ sudo systemctl start postgresql
    $ sudo systemctl enable postgresql

  • 作为普通程序启动：pg_ctl

    1
    2
    $ su - postgres
    $ pg_ctl start -D /var/lib/pgsql/data

Roles

PG使用概念roles处理认证、权限问题

  • 角色相较于区分明显的用户、组概念更加灵活

  • create user和create role几乎完全相同

    • create user：创建角色默认带LOGIN属性
    • create role：创建角色默认不带LOGIN属性

权限

  • SUPERUSER/NOSUPERUSER：数据库超级用户
  • CREATEDB/NOCREATEDB：创建数据库
  • CREATEUSER/NOCREATEUSER
  • CREATEROLE/NOCREATEROLE：创建、删除普通用户角色
  • INHERIT/INHERIT：角色可以继承所属用户组权限
  • LOGIN/NONLOGIN：作连接数据库初始角色名
  • REPLICATION/NOREPLICATION：流复制时用到
  • CONNECTION LIMIT connlimit
  • [ENCRYPTED/UNENCRYPTED]PASSWORD '<passwd>'
  • VALID UNTIL '<timestamp>'
  • IN ROLE <role_name>[, ...]：角色所属用户组
  • IN GROUP <role_name>[, ...]
  • ROLE <role_name>[, ...]
  • ADMIN <role_name>[, ...]
  • USER <role_name>[, ...]
  • SYSID uid

角色赋权

1
2
3
4
5
psql> create role/user <name> [[with] <option> [...]];
	# 创建角色时直接赋权
psql> alter role/user <name> [[with] <option> [...]];
psql> grant connect on database <db_name> to <name>;
	# 修改已创建角色权限

组赋权

  • 把多个角色归为组，通过给组赋权、撤销权限实现权限管理
  • PG中角色赋权是通过inherit方式实现的
1
2
3
4
5
6
7
psql> create role father login createdb createrole;
	# 创建组角色、赋权
psql> create role son1 inherit;
psql> grant father to son1;
	# 创建有`inherit`权限的用户、赋权
psql> create role son2 inherit in role father;
	# 创建用户时直接赋组权

认证方式

Peer Authentication

peer：从内核中获取操作系统中用户名，作为数据库角色名连接

  • 默认连接同名数据库
  • 信任Linux用户身份（认证），不询问密码
    • 即使-W强制输入密码，也不会检查密码正确性
  • 只有local连接支持此方法

Trust Authentication

trust：允许任何数据库角色名的连接

  • 信任任何连接、不询问密码

    • 只应该在操作系统层面上能提供足够保护下情况下使用
      • 文件系统权限：限制对Linux域套接字文件的访问
    • 适合单用户、本地连接

  • 数据库、用户权限限制仍然存在

Ident Authentication

ident：从ident服务器中获取操作系统中用户名，用于连接数据库

  • 仅在TCP/IP连接情况下支持

    • 若被指定给local连接，将使用peer认证

  • 数据库服务器向客户端ident服务器询问“连接数据库的”用户， 据此判断连

    • 此流程依赖于客户端完整性，若客户端机器不可信，则 攻击者可以在113端口执行任何程序返回任何用户名
    • 故此认证方法只适合封闭网络，所以客户端机器都被严格 控制
    • 有些ident服务器开启非标准选项导致返回的加密用户名， 此选项应该关闭
    • 基本每个类Unix操作系统都带有ident服务器，用于监听 113端口TCP
涉及配置
  • map：运行系统、数据库用户名之间映射

Password Authentication

Password认证：基于密码的认证方式

  • password：明文传输密码验证
  • md5：MD5-hashed传输密码o

  • md5可以避免密码嗅探攻击

  • password总应该尽量避免使用

    • 启用db_user_namespace特性时无法使用md5
    • SSL加密连接下password也能安全使用

  • 每个数据库的密码存储在pg_authid系统表中

    • 若用户没有设置密码，则存储的密码为null，密码验证 也总是失败
    • 使用create user、alter role等SQL语句修改密码

GSSAPI Authentication

GSSAPI：定义在RFC 2743中的安全认证产业界标准协议

  • GSSAPI为支持其的系统自动提供认证
    • 认证本身是安全的，但是通过数据库连接的数据默认没有 加密，除非使用SSL
  • PG中GSSAPI需要编译时启用支持

SSPI Authentication

negotiate：windows的安全认证技术

  • PG将尽可能使用Kerberos，并自动回滚为NTLM
  • 仅服务器、客户端均在windows下或GSSAPI可用的情况下才能 工作
  • 使用Kerberos情况下，SSPI、GSSAPI工作方式相同
涉及配置
  • include_realm
  • map
  • krb_realm

Kerberos Authentication

Kerberos：适合公共网络上分布式计算的产业界标准安全认证系统

  • Kerberos提供不加密的语句、数据安全认证，若需要加密则使用 SSL
  • PG支持Kerberos第5版，需要在build时开启Kerberos支持
涉及配置
  • map
  • include_realm
  • krb_realm
  • krb_server_hostname

LDAP Authentication

LDAP：类似password，只是使用LDAP作为密码认证方法

涉及配置
  • ldapserver
  • ldapport
  • ldaptls
  • ldapprefix
  • ldapsuffix
  • ldapbasedn
  • ldapbinddn
  • ldapbindpasswd
  • ldapsearchattribute

RADIUS Authentication

RADIUS：类似password，只是使用RADIUS作为密码认证方法

涉及配置
  • radiusserver
  • radiussecret
  • radiusport
  • radiusidentifier

Certificate Authentication

Certificate：使用SSL客户多证书进行认证

  • 所以只在SSL连接中可用
  • 服务器要求客户端提供有效证书，不会向客户端传递密码prompt
    • cn属性（common name）将回和目标数据库的用户名 比较
    • 可通过名称映射允许cn属性和数据库用户名不同
涉及配置
  • map：允许系统、数据库用户名之间映射

PAM Authentication

PAM：类似password，只是使用 PAM(Pluggable Anthentication Modules)作为密码认证机制

涉及配置
  • parmservice：PAM服务名
    • 默认postgresql

pg_ctl

pg_ctl：用于控制PostgreSQL服务器的工具，此工具基本需要在 postgres用户下才能使用

  • 查看状态：$ pg_ctl status -D /var/lib/pgsql/data

psql

Shell

连接数据库

1
$ psql [-h <host>] [-p <port>] [-U <user_name>] [[-d] <db_name>]
  • -h：缺省为local类型连接本地数据库
    • local、host连接类型对应不同认证方式
    • -h localhost和缺省对应不同hba.conf条目
  • -p：缺省端口5432
  • -U/--user_name=：缺省linux用户名
  • [-d]/--database=：当前linux用户名
  • -W：密码，peer、trust模式下无价值

Shell命令

  • postgres不仅仅提供psql交互环境作为shell命令，还提供可以 直接在shell中运行的数据库命令

    • 当然前提是当前linux登陆用户在数据库中存在、有权限
1
2
3
4
$ createdb <db_name> [-O <user_name>]
	# 创建数据库，设置所有权为`user_name`
$ dropdb <db_name>
	# 删除数据库

元命令

元命令：反斜杠命令，\开头，由psql自己处理

  • \后：跟命令动词、参数，其间使用空白字符分割

  • 冒号:：不在引号中的冒号开头的参数会被当作psql变量

  • 反点号：参数内容被当作命令传给shell， 输出（除去换行符）作为参数值

  • 单引号'：参数包含空白时需用单引号包o，其中包含的参数 的内容会进行类C的替换

    • \n（换行）、\digits（八进制）
    • 包含单引号需要使用反斜杠转义

  • 双引号\”<\code>

    • 遵循SQL语法规则，双引号保护字符不被强制转换为 小写，且允许其中使用空白
    • 双引号内的双引号使用双双引号""转义

帮助

  • \?

    • [<commands>]：元命令帮助
    • <options>：psql命令行选项帮助
    • <variables>：特殊变量帮助

  • \h [<clauses>]：SQL命令语法帮助（*表示全部）

展示信息

  • \du：查看用户权限
  • \c <db_name> <name>：以身份name访问数据库db_name
  • \l[ist]：查看数据库
  • \dt：展示当前数据库中表

变量

  • \set foo bar：设置变量
    • 可以像php设置“变量 变量”：\set :foo bar
  • \unset foo：重置变量

数据库变量

内部变量

特殊变量

特殊变量：一些选项设置，在运行时可通过改变变量的值、应用的 表现状态改变其，不推荐改变这些变量的用途

  • AUTOCOMMIT：缺省为on，每个SQL命令完成后自行提交，此时 需要输出BEGIN、START TRANSACTION命令推迟提交
  • DBNAMW：当前所连接数据库
  • ENCODING：客户端字符集编码
  • 详情查询手册

环境变量

  • PGDATA：指定数据库簇（存放数据）目录

    1
    $export PGDATA=/var/lib/pgsql/data
    • 默认/var/lib/pgsql/data
    • -D命令行参数指定

Categories

Follow

Recents

Advertisement

follow.it

×