Ubuntu 22.04.3 安装 PostgreSQL 15

2023-09-30 本文已影响0人偷油考拉

一、安装 PostgreSQL 15

https://www.postgresql.org/download/linux/ubuntu/

sudo sed -i 's/http:\/\/cn.archive.ubuntu.com/https:\/\/mirrors.aliyun.com/g' /etc/apt/sources.list
sudo sh -c 'echo "deb https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install postgresql-15

二、安装 pgAdmin （客户端）

#
# Setup the repository
#

# Install the public key for the repository (if not done previously):
curl -fsS https://www.pgadmin.org/static/packages_pgadmin_org.pub | sudo gpg --dearmor -o /usr/share/keyrings/packages-pgadmin-org.gpg

# Create the repository configuration file:
sudo sh -c 'echo "deb [signed-by=/usr/share/keyrings/packages-pgadmin-org.gpg] https://ftp.postgresql.org/pub/pgadmin/pgadmin4/apt/$(lsb_release -cs) pgadmin4 main" > /etc/apt/sources.list.d/pgadmin4.list && apt update'

#
# Install pgAdmin
#

# Install for both desktop and web modes:
sudo apt install pgadmin4

# Install for desktop mode only:
sudo apt install pgadmin4-desktop

# Install for web mode only: 
sudo apt install pgadmin4-web 

# Configure the webserver, if you installed pgadmin4-web:
sudo /usr/pgadmin4/bin/setup-web.sh

三、客户端认证配置

https://www.postgresql.org/docs/15/client-authentication.html

pg_hba.conf 范例如下

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   sameuser        all                                     md5
local   all             @admins                                 md5
local   all             +support                                md5
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]

1. TYPE

TYPE	Description
local	该记录匹配使用 `Unix-domain sockets` 的连接。
host	该记录匹配使用 `TCP/IP` 的连接， `SSL`、`non-SSL`、`GSSAPI`、`non-GSSAPI`加密都在匹配范围。
hostssl	该记录匹配使用 `TCP/IP` 并以`SSL`加密的连接。服务器需要开启`SSL`支持。
hostnossl	该记录匹配使用 `TCP/IP` 并没有以`SSL`加密的连接。
hostgssenc	该记录匹配使用 `TCP/IP` 并以`GSSAPI`加密的连接。服务器需要开启`GSSAPI`支持。
hostnogssenc	该记录匹配使用 `TCP/IP` 并没有以`GSSAPI`加密的连接。

2. DATABASE

DATABASE	Description
`all`	匹配所有数据库
`sameuser`	请求的数据库与请求的用户具有相同的名称
`samerole`	请求的用户必须是角色成员，角色名与请求的数据库同名。超级用户不作为角色成员，除非显式指定是某个角色成员。
`samegroup`	`samegroup`是`samehole`的一个过时但仍然被接受的拼写。
`replication`	匹配物理复制连接，不匹配逻辑复制连接。物理连接不指定数据库，逻辑连接指定数据库。或者，匹配特定数据库名称；多个数据库名称用逗号分隔；也可以使用`@`指定包含数据库名的文件。

3. USER

USER	Description
`all`	匹配所有用户
`username`	特定数据库用户名，可以使用逗号分隔多个用户名。
`+groupname`	以 `+` 开始的组名，比如 `+support`。
`@filename`	以 `@` 开始的文件名，比如 `@admins`。文件内含用户名。

在PostgreSQL中，用户和组之间并没有真正的区别；+ 意味着“匹配该角色的任何直接或间接成员”，而没有+标记的名称只匹配该特定角色。
超级用户只有显式的定义为角色成员，才会被视为角色的成员，而不是因为他是超级用户。

4. ADDRESS

ADDRESS	Description
`all`	任意IP地址
`samehost`	与服务器同样的地址
`samenet`	与服务器同样的网段
`hostname`	以句点（.）开头的主机名规范与实际主机名的后缀匹配。因此，`.example.com`将匹配foo.example.com（但不仅是example.com）。在`pg_hba.conf`中指定主机名时，应确保名称解析速度够快，建议设置本地名称解析缓存（如nscd）。如果，希望在日志中查看客户端的主机名，而不是IP地址，需要启用配置参数`log_hostname`。
`IP 地址范围`	172.20.143.89/32 172.20.143.0/24 10.6.0.0/16 ::1/128 fe80::7a31:c1ff:0000:0000/96 0.0.0.0/0 ::0/0

这些字段不适用于 local 记录。

IP-address IP-mask 作为 IP-address/mask-length 的另外一种方式，，比如 192.168.1.0 255.255.255.0

5. METHOD

METHOD	Description
trust	无条件的允许连接。该方式允许以任意期望的用户名连接数据库服务器，而无需密码、认证。
reject	无条件的拒绝连接，常用于组合过滤。比如，某规则禁止特定网络中的部分主机连接，稍后的规则允许剩余的主机连接。
scram-sha-256	使用 SCRAM-SHA-256 认证用户密码。
md5	使用 SCRAM-SHA-256 or MD5 认证用户密码。
password	以未加密密码进行认证。因为密码在网络上以明文传送，不建议用于非信任网络。
gss	使用 GSSAPI 认证，仅适用于 TCP/IP 连接。
sspi	使用 SSPI 认证。
ident	客户端从ident服务器获取操作系统用户名，并检查它是否与请求的数据库用户名匹配。仅适用于 TCP/IP 连接。
peer	检查客户端的操作系统用户名是否与请求的数据库用户名匹配。仅适用于 local 连接。
ldap	连接 LDAP 服务器认证。
radius	连接 RADIUS 服务器认证。
cert	使用 SSL 客户端证书认证。
pam	通过操作系统PAM服务认证。
bsd	通过操作系统 BSD 认证服务认证。

auth-options

在 auth-method 字段之后，可以有name=value形式的字段，用于指定身份验证方法的选项。详情参见官方文档。

robin@robin-ThinkPad-T480s:~/Desktop$ sudo netstat -anpl |grep postgre
[sudo] robin 的密码： 
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      9320/postgres       
unix  2      [ ACC ]     流        LISTENING     91811    9320/postgres        /var/run/postgresql/.s.PGSQL.5432

范例

修改 /etc/postgresql/15/main/pg_hba.conf，增加 Customize Config 部分

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# 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
# Customize Config
host    all             all             0.0.0.0/0               scram-sha-256

修改 /etc/postgresql/15/main/postgresql.conf

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)

重启服务

四、创建用户、数据库

修改默认密码

ALTER USER postgres PASSWORD 'postgres';

创建 role 和数据库

SELECT rolname FROM pg_roles;
CREATE ROLE name;
CREATE ROLE name PASSWORD 'string'
CREATE ROLE name LOGIN;
CREATE USER name;
CREATE USER xxx PASSWORD yyy;

GRANT ALL ON SCHEMA test TO xxx;
GRANT ALL ON ALL TABLES IN SCHEMA test TO xxx;

CREATE DATABASE name;
CREATE DATABASE dbname OWNER rolename;
CREATE TABLESPACE fastspace LOCATION '/ssd1/postgresql/data';
CREATE TABLE foo(i int) TABLESPACE space1;

范例：

postgres=# CREATE USER superset PASSWORD 'superset';
CREATE ROLE
postgres=# CREATE DATABASE superset OWNER superset;
CREATE DATABASE