PostgreSQL 部署

PostgreSQL 部署

---

安装

sudo apt install postgresql

安装完成后，会自动注册一个 postgres 的用户，用于支持 PostgreSQL 的本机免密登录。

此外，PostgreSQL 的配置文件存储在 /etc/postgresql/15/main 下，其中 15 是版本号。

配置公网访问

由于我将 PostgreSQL 部署在服务器上，因此我需要放行除本机之外的其他 IP 的访问。

本地修改

先把postgresql服务停止。

sudo systemctl stop postgresql

• 进入目录/etc/postgresql/15/main

• 修改文件postgresql.conf

  listen_addresses = '*'		        # what IP address(es) to listen on;

• 修改文件pg_hba.conf

  host    all      all      192.168.88.198/32     md5
  host    all      all      0.0.0.0/0             md5

第一种格式：host all all 192.168.88.198/32 md5

可以设置指定ip连接到数据库服务上。

第二种格式：host all all 0.0.0.0/0 md5

任意外部ip都可访问到数据库服务。

上述方式选择其一即可。

• 重启postgresql服务

  sudo systemctl start postgresql

• 成功解决问题。

配置文件格式解释

host    all      all      192.168.88.198/32     md5
host    all      all      0.0.0.0/0             md5

语法

local <dbname> <user> <auth-method> [auth-options]

host <dbname> <user> <ip/masklen> <auth-method> [auth-options]

服务器放行端口

PostgreSQL 默认使用的端口是 5432，采用TCP协议。配置完成后无需重启服务器。

使用

常用指令

• \l：列出当前所有的数据库。
• \du：列出所有用户及其权限。
• \dt：列出当前数据库中的所有表。
• \dp：列出当前数据库中的所有表的所有权限信息
• \d：列出指定表结构。
• \c：连接到另一个数据库，相当于 MySQL 的 use 。
• \q：退出 PostgreSQL 命令行客户端。
• \?：获取帮助信息，列出可用的 psql 命令及其用法。

登录

默认使用的用户是 postgres ，这里由于默认设置了身份验证要求登录 PostgreSQL 的用户名和当前系统用户名要一致，因此要先切换到该用户：

sudo -i -u postgres
psql

---

或者直接一条命令解决：

psql -U postgres -d 要使用的数据库 -h 服务器IP -p 5432

不指定要使用的数据库也可以

创建用户并授权

参考：PGSQL 数据库用户管理示例

PostreSQL 的默认配置是自动创建一个 postgres 用户，该用户有完全的 superadmin 超管权限，可以管理系统中整个 PostgreSQL 实例。但是这个 postgres 用户仅是用于运行 PostgreSQL 数据库服务的，不能用于登录系统（即没有登录 shell，其登录 shell 被设置成 /bin/false 或 /usr/sbin/nologin，也没有家目录）

示例：创建一个 new_user 用户并授权

1. 以超级管理员 postgres 身份登录数据库

   $ psql -U postgres -h 服务器IP -p 5432

2. 创建新用户

   create user new_user with password '123456';

3. 为新用户授权

   grant connect on database test to new_user; # 连接权限
   grant all privileges on database test to new_user; # 数据库test的所有权限
   grant all privileges on all tables in schema public to new_user; # 数据库test中所有表的操作权限

4. 其他用户管理操作

   alter user new_user password '654321'; # 修改用户密码