服务端安装

参考官方安装说明 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

登录验证
访问验证机制

    pg的验证和权限是分开的, 一个用户首先要能通过验证进行访问, 然后才是根据授权访问对应的数据库
    pg的访问验证设置, 默认的配置文件位于 /etc/postgresql/[版本号]/main/pg_hba.conf, 版本号可能是14, 15等
    只有当这个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范围, 数据库名, 用户名, 验证方式.
    匹配的第一个记录(匹配连接类型+客户端地址+数据库+用户名)将用于验证
    没有缺省或再次验证, 只要一个记录被选中, 那么验证就只用这个记录处理, 如果没有命中的记录, 就返回拒绝.

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

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

    因为local ... peer是本地socket, 优先级最高, 用户不是postgres就是all, 如果在命令行运行psql, 会基于用户的linux用户名, 用这些规则进行验证
    linux下的postgres用户, 因为在pg中的同名user/role存在, 因此可以通过验证
    如果用其它用户访问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