PostgreSQL(01): Ubuntu20.04/22.04 PostgreSQL 安装配置记录

目录

• PostgreSQL(01): Ubuntu20.04/22.04 PostgreSQL 安装配置记录
• PostgreSQL(02): PostgreSQL常用命令

PostgreSQL 名称来源

It was originally named POSTGRES, referring to its origins as a successor to the Ingres database developed at the University of California, Berkeley.

In 1996, the project was renamed to PostgreSQL to reflect its support for SQL.

PostgreSQL 的发音为 [ˈpəʊsɡreˈsɪkl], 中间部分类似于 progress 的发音

服务端安装

参考官方安装说明 https://www.postgresql.org/download/linux/ubuntu/

# 创建软件源
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# 添加key
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# 更新仓库
sudo apt update
# 安装之前可以看一下有哪些版本
apt-cache search postgresql
# 安装, 20.04默认是postgresql-14, 22.40是postgresql-15
sudo apt install postgresql
# 检查
sudo systemctl status postgresql
# 查看端口
sudo netstat -lnp

主配置

对应的配置文件在 /etc/postgresql//main, 当前的版本是14, 路径是 /etc/postgresql/14/main/postgresql.conf

sudo vi /etc/postgresql/14/main/postgresql.conf 

主配置文件说明 https://www.postgresql.org/docs/14/runtime-config-connection.html

服务IP listen_addresses

# 监听所有地址
listen_addresses = '*'
# 监听指定地址
listen_addresses = '192.168.10.20'

服务端口 port

port = 5432

密码加密方式 password_encryption

password_encryption = scram-sha-256    # scram-sha-256 or md5

用户名命名空间 db_user_namespace, 如果设置为on, 用户创建时可以使用 username@dbname 这样的格式, 用于与数据库绑定. 这时候依然可以创建全局用户, 但是连接时客户端必须加上 @

db_user_namespace = off

客户端安装

Windows下可以直接使用Navicat, pg自己的图形客户端是 pgAdmin, 基于python和javascript.

pgAdmin 4 is a complete rewrite of pgAdmin, built using Python and Javascript/jQuery. A desktop runtime written in NWjs allows it to run standalone for individual users

Ubuntu20.04, pgAdmin4

sudo vi /etc/apt/sources.list.d/pgadmin4.list 
# 写入以下内容
deb https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/focal pgadmin4 main
# 更新并安装
sudo apt update
sudo apt install pgadmin4-desktop

登录验证

访问验证机制

1. pg的验证和权限是分开的, 一个用户首先要能通过验证进行访问, 然后才是根据授权访问对应的数据库
2. pg的访问验证设置, 默认的配置文件位于 /etc/postgresql/[版本号]/main/pg_hba.conf, 版本号可能是14, 15等
3. 只有当这个role/user在pg中存在, 才能对pg进行访问. pg安装后, 默认的role为postgres, 是一个superuser

验证配置文件说明

配置文件 pg_hba.conf, 配置说明 https://www.postgresql.org/docs/14/auth-pg-hba-conf.html

客户端认证由配置文件控制, 通常为名为 pg_hba.conf 的文件, 存储在集群的数据目录(HBA 代表 host-based authentication 的缩写). 当数据目录初始化时, 会生成一个默认的 pg_hba.conf 文件. 可以通过修改主配置文件, 将文件放到其他路径.

pg_hba.conf 文件通常的格式是按行组织的文本记录

• 使用#号标识注释
• 如果一行未结束需要换行, 使用\符号.
• 每行记录由一些空格或tab分隔的字段组成. 如果字段包含空格, 需要用双引号包围.
• 每行记录指定了: 连接类型, 客户端IP范围, 数据库名, 用户名, 验证方式.
• 匹配的第一个记录(匹配连接类型+客户端地址+数据库+用户名)将用于验证
• 没有缺省或再次验证, 只要一个记录被选中, 那么验证就只用这个记录处理, 如果没有命中的记录, 就返回拒绝.

注意, 验证只要匹配了一个记录(方式), 就用这个方式处理, 不会有多次匹配.

初始配置示例

这是默认的配置, 注意几点

1. 因为local ... peer是本地socket, 优先级最高, 用户不是postgres就是all, 如果在命令行运行psql, 会基于用户的linux用户名, 用这些规则进行验证
2. linux下的postgres用户, 因为在pg中的同名user/role存在, 因此可以通过验证
3. 如果用其它用户访问psql, 例如ubuntu用户运行psql -l, 会报failed: FATAL: role "ubuntu" does not exist错误, 通过sudo -u postgres createuser --interactive创建一个名为ubuntu的user之后, 就可以运行psql -l了.

# "local" is for Unix domain socket connections only
# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             postgres                                peer
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             0.0.0.0/0               trust
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

记录格式

local         database  user                           auth-method  [auth-options]
host          database  user  address                  auth-method  [auth-options]
hostssl       database  user  address                  auth-method  [auth-options]
hostnossl     database  user  address                  auth-method  [auth-options]
hostgssenc    database  user  address                  auth-method  [auth-options]
hostnogssenc  database  user  address                  auth-method  [auth-options]
host          database  user  IP-address  IP-mask      auth-method  [auth-options]
hostssl       database  user  IP-address  IP-mask      auth-method  [auth-options]
hostnossl     database  user  IP-address  IP-mask      auth-method  [auth-options]
hostgssenc    database  user  IP-address  IP-mask      auth-method  [auth-options]
hostnogssenc  database  user  IP-address  IP-mask      auth-method  [auth-options]

连接方式

• local 使用本机Unix-domain sockets, 如果没有local开头的记录, 则不允许用Unix-domain sockets连接
• host 使用TCP/IP连接, 包含SSL和GSSAPI方式
• hostssl TCP/IP + 使用SSL
• hostnossl TCP/IP + 不使用SSL
• hostgssenc TCP/IP + GSSAPI 加密
• hostnogssenc TCP/IP + 不使用 GSSAPI 加密

数据库, 指定匹配的数据库

• 数据库名 指定数据库, 多个数据库使用逗号连接
• all 匹配所有
• sameuser 与此数据库同名的用户, 必须是这个用户
• samerole 与此数据库同名的role, 用户必须属于这个role
• samegroup 以废弃
• replication
• @ 可以用@号指定文件

用户, 指定匹配的用户

• 用户名 指定的用户, 多个用户用+号连接
• all 所有用户
• @ 可以用@号指定文件

客户端地址

• 172.20.143.89/32 IPv4地址或范围
• 172.20.1.1/255.0.0.0 IPv4地址范围的另一种格式
• fe80::7a31:c1ff:0000:0000/96 IPv6地址或范围
• all 所有地址
• samenet 同一子网的地址
• samehost 当前主机的所有地址
• .example.com 域名通配

验证方式

• trust 无条件通过
• reject 直接拒绝
• scram-sha-256 使用SCRAM-SHA-256验证
• md5 使用 Perform SCRAM-SHA-256 或 MD5 验证
• password 使用未加密的密码验证, 注意这种方式下, 密码在网络中是明文传输
• gss 使用 GSSAPI 验证, 仅适用于 TCP/IP 连接.
• sspi 使用 SSPI 验证, 仅适用于 Windows
• ident 通过ident服务器, 获取当前客户端操作系统用户名, 并与请求的数据库用户名进行校验, 仅适用于 TCP/IP 连接.
• peer 从操作系统获取用户名, 仅适用于 local 方式的连接
• ldap Authenticate using an LDAP server.
• radius Authenticate using a RADIUS server
• cert 使用 SSL 客户端证书进行验证
• pam 使用操作系统提供的 Pluggable Authentication Modules (PAM) 服务进行验证
• bsd 使用操作系统提供的 BSD Authentication service 进行验证

验证选项

• 根据不同的验证方式提供的选项

开放所有用户从所有网络, 用加密口令访问所有数据库

在 pg_hba.conf 添加下面这行 It allows access to all databases for all users with an encrypted password:

# TYPE DATABASE USER CIDR-ADDRESS  METHOD
host  all  all 0.0.0.0/0 scram-sha-256

默认的 postgres 用户

安装后, Ubuntu系统中会增加一个 postgres 用户

$ more /etc/passwd
...
postgres:x:113:121:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash

因为pg中也存在 postgres 这个superuser, 这个用户可以直接访问 postgresql

$ sudo su postgres
[sudo] password for milton: 
postgres@ubuntu:/home/milton$ psql
psql (14.2 (Ubuntu 14.2-1.pgdg20.04+1))
Type "help" for help.

postgres=# 
postgres-# \q
postgres@ubuntu:/home/milton$

配置

查看数据库列表

sudo -u postgres psql -l 

创建用户

创建用户, 这个 createuser 命令只是一个perl脚本, 专门用于 postgresql 创建用户, 准确的说是对应 local peer的用户, 因为里面不设置密码

sudo -u postgres createuser --interactive
[sudo] password for milton: 
Enter name of role to add: dbuser      
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y

在命令行下执行psql或postgres时, postgresql 会用linux用户名进行验证, 所以如果创建了用户为 milton, 如果再创建一个名为 milton 的 linux 用户就可以直接访问. 如果不对应linux用户, 则只能通过host方式登录

User, Role, Group

在 PostgreSQL 中, user, group, role这三者可以认为是等价的, role和user唯一的区别在于user默认可以登录数据库. 在其他数据库例如Oracle, role只用于授权和分组不能用于登录, 而在 PostgreSQL 中, role 可以用于登录. 在 PostgreSQL 中, CREATE USER 和 CREATE GROUP 其实是 CREATE ROLE 的别名.

CREATE USER = CREATE ROLE + LOGIN PERMISSION

也可以使用 CREATE USER 创建一个 PostgreSQL 用户

CREATE USER myuser WITH PASSWORD 'secret_passwd';

也可以使用以下等价的 CREATE ROLE 命令

CREATE ROLE myuser WITH LOGIN PASSWORD 'secret_passwd';

这两个语句创建的用户是完全一样的, 这样新创建的用户, 除了有public role的权限以外没有任何其他权限. 所有的users和roles的权限, 都继承自public role

创建数据库

# 使用 postgres 用户
createdb milton
# 或者 sudo
sudo -u postgres createdb milton
# 指定用户
sudo -u postgres createdb testdb -O postgres

数据类型

数值

• 整数 smallint, integer, bigint
• 自增整数 serial, bigserial
• 金额浮点 decimal, numeric

字符

• character(n) 定长字符串
• character varying(n) 相当于mysql的varchar, 有长度限制
• text 不定长,不限长字符串

日期

• timestamp, 这个相当于mysql的datetime

其它

pg还支持坐标, uuid, xml, json等字段格式

命令行操作

连接db

psql -h localhost -p 5432 -U postgres runoobdb

Compare To MySQL

• Pg 支持多个表从同一个序列中取 id
• Pg 支持 OVER 子句, OVER 子句能简单的解决 "每组取 top 5" 这类问题
• Pg 不存在需要 utf8mb4 显示 emoji 的问题

参考

• 管理user和role https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/
• MySQL PostgreSQL Difference
  • https://www.cnblogs.com/geekmao/p/8541817.html
  • https://www.cnblogs.com/sbj-dawn/p/8053549.html