PostgreSQL初始化配置

一、PostgreSQL安装

dnf install -y postgresql-server
# 初始化pgsql
/usr/bin/postgresql-setup --initdb
# 启用pgsql
systemctl enable postgresql.service
systemctl start postgresql.service

二、PostgreSQL配置远程访问
1、编辑/var/lib/pgsql/16/data/postgresql.conf（通过默认仓库安装路径是：/var/lib/pgsql/data/postgresql.conf），取消注释并修改：

listen_addresses = '*|0.0.0.0'       # 绑定服务到所有IP，默认只在127.0.0.1上绑定服务，如果要侦听多个IP地址，以逗号分隔('localhost,172.17.0.1')
port = 5432                          # 服务默认端口

2、编辑/var/lib/pgsql/16/data/pg_hba.conf（通过默认仓库安装路径是：/var/lib/pgsql/data/pg_hba.conf），按需选择以下配置

# local行仅针对Unix域套接字连接，不涉及网络
# host行针对TCP/IP连接
# 允许本地socket连接访问数据库
local  all     all                      md5
# 允许本地TCP连接（127.0.0.1）使用md5：
host   all     all      127.0.0.1/32    md5
# 允许所有IP访问数据库
host   all     all      0.0.0.0/0       md5
# 允许某网段访问数据库，允许多个网段访问需要写成多行，不支持(192.168.1.0/24,192.168.2.0/24)逗号分隔的方式：
host   all     all      192.168.1.0/24  md5
# 允许admin用户通过127.0.0.1访问数据库
host   all     admin    127.0.0.1/32    md5
# 允许admin用户通过127.0.0.1访问访问testdb库
host   testdb  admin    127.0.0.1/32    md5

3、重启PostgreSQL

systemctl restart postgresql
service postgresql restart

三、PostgreSQL认证方式
1、使用md5/password认证
远程连接认证必需使用md5/password方式，本地TCP/IP连接使用md5或scram-sha-256（scram-sha-256：一般在较新版本中使用，需要客户端库也支持SCRAM，且pg_hba.conf与pgcrypto支持），需要修改pg_hba.conf(pg_hba.conf文件位置：/var/lib/pgsql/data/pg_hba.conf或/etc/postgresql/XX/main/pg_hba.conf)
2、使用Ident/Peer认证
Ident/Peer验证通常在本地连接时通过操作系统用户与数据库用户的映射来工作，如果没有正确映射也会失败。确保你在操作系统是以同名用户运行(系统中也需要有admin用户)，并且pg_hba.conf中的local/host行匹配该使用者。
四、PostgreSQL常用操作

# 查看命令帮助信息
\?
# 查看SQL命令语法帮助
\h SELECT
# 进入psql
sudo -u postgres psql
# 创建数据库
CREATE DATABASE testdb;
# 创建用户
CREATE USER admin WITH PASSWORD 'admin123';
# 授予用户访问testdb数据库权限
GRANT ALL PRIVILEGES ON DATABASE testdb TO admin;
# 修改用户密码
sudo -u postgres psql -c "ALTER USER admin WITH PASSWORD 'test123';"
# 查看所有数据库
\l
# 切换到testdb库
\c testdb
# 查看数据库里的表，带"+"号查看详细信息
\dt+
# 查看表结构，带"+"号查看详细信息
\d+ [表名]
# on：开启不显示表头，只显示数据行，off：关闭
\t on/off
# on：开启扩展格式（类似MySQL`\G`的行格式），off：关闭扩展格式
\x on/off
# 查看当前数据库名称
SELECT current_database();
# 退出
\q
# 远程登陆PostgreSQL
psql -h 127.0.0.1 -U admin -d testdb -W admin123

五、PostgreSQL权限管理

# 针对public模式中的所有现有表，ALL PRIVILEGES在表级权限通常包括SELECT,INSERT,UPDATE,DELETE,REFERENCES,TRIGGER等等，具体取决于PostgreSQL的版本与对象类型
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO admin;
# 如果数据库中还有其他schema，同样执行：
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <schema_name> TO admin;
# 授予admin对给出的schema的对象使用/创建权限：
GRANT USAGE,CREATE ON SCHEMA public TO admin;
# 对序列也同样授予所有权限
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO admin;
# 注意：上面的授权只对历史的一些对象授权，后期增加的对象是没有权限的，需要给个默认权限
# 设置默认表权限，对新创建的表自动授予admin账户所有权限，DEFAULT PRIVILEGES仅在新表创建时生效，对已存在的表需要显式授予相关权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO admin;
# 对已创建的表授予权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT select,insert,update,delete ON TABLES TO admin;
# 对未来新建的序列也自动授予所有权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO admin;
# 授予admin角色创建新对象的权限，确保已对数据库级别赋予CONNECT、CREATE等权限（通常是在数据库层面授予）
GRANT CONNECT ON DATABASE testdb TO admin;
GRANT CREATE ON DATABASE testdb TO admin;
# 查看当前public下所有表的权限
\dp public.*
# 查询默认权限设置或查询pg_default_acl等系统表来确认默认权限策略
\ddp 
select * from pg_default_acl;
# 查看用户角色
\du
select * from pg_roles;
# 查看用户权限
select * from information_schema.table_privileges where grantee='admin';

参考链接：
     https://blog.csdn.net/leblanceAndSherry/article/details/148353373        # PostgreSQL部署
     https://cloud.tencent.com/developer/article/2451224                              # PostgreSQL备份与恢复
     https://www.cnblogs.com/kirin365/articles/16137867.html                      # PostgreSQL远程访问配置与权限控制
     https://cloud.tencent.com/developer/article/2209946                              # PostgreSQL查不到新建表
     https://developer.aliyun.com/article/1401443                                          # postgresql数据库文件系统详解
     https://www.cnblogs.com/shujuyr/p/18819946                                        # PostgreSQL权限详解
     https://www.cnblogs.com/seasonzone/p/15466530.html                        # PostgreSQL用户权限管理
     https://www.kancloud.cn/zzgjb/pgsql/76168                                           # PostgreSQL手册
     https://www.mongodb.com/zh-cn/docs/development/                            # mongodb官方文档