Postgre数据库初探

一、PostgreSQL介绍

PostgreSQL是以加州大学伯克利分校计算机系开发的POSTGRES， 版本 4.2为基础的对象关系型数据库管理系统（ORDBMS）。POSTGRES 领先的许多概念在很久以后才出现在一些商业数据库系统中。

PostgreSQL是最初的伯克利代码的开源继承者。它支持大部分 SQL 标准并且提供了许多现代特性：

复杂查询
外键
触发器
可更新视图
事务完整性
多版本并发控制

同样，PostgreSQL可以用许多方法扩展，比如， 通过增加新的：

• 数据类型
• 函数
• 操作符
• 聚集函数
• 索引方法
• 过程语言

并且，因为自由宽大的许可证，任何人都可以以任何目的免费使用、修改和分发PostgreSQL， 不管是私用、商用还是学术研究目的。

二、Linux 上安装 PostgreSQL

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

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql14-server
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
sudo systemctl enable postgresql-14
sudo systemctl start postgresql-14

PostgreSQL不推荐使用root管理，在安装成功postgreSQL后，他默认会给你创建一个用户：postgres

玩PGSQL前，先切换到postgres

su postgres

到postgres用户后，直接输入psql即可进入到postgreSQL提供的客户端

# 进入命令行
psql
# 查看有哪些库，如果是新安装的，有三个库，一个是postgres，template0，template1
\l  

添加密码

alter user postgres with password 'postgres';

三、PostgreSQL的配置

要搞两个配置信息，一个关于postgreSQL的远程连接配置以及postgreSQL的日志配置。

PostgreSQL的主要配置放在数据目录下的， postgresql.conf 以及 pg_hba.conf 配置文件

这些配置文件都放在了

# 这个目录下
cd /var/lib/pgsql/14/data

远程连接配置

PostgreSQL默认情况下不支持远程连接的，这个跟MySQL几乎一样

• MySQL给mysql.user追加用户，一般是采用grant的命令去玩。
• PostgreSQL要基于配置文件修改，才能制定用户是否可以远程连接。
  直接去修改pg_hba.conf配置文件

用户以及对应数据库和连接方式的编写模板

# 第一块
local：代表本地连接，host代表可以指定连接的ADDRESS
# 第二块
database编写数据库名，如果写all，代表所有库都可以连接
# 第三块
user编写连接的用户，可以写all，代表所有用户
# 第四块
address代表那些IP地址可以连接
# 第五块
method加密方式，这块不用过多关注，直接md5
# 直接来个痛快的配置吗，允许任意地址的全部用户连接所有数据库
host    all             all             0.0.0.0/0               md5

为了实现远程连接，除了用户级别的这种配置，还要针对服务级别修改一个配置
服务级别的配置在postgresql.conf

vim postgresql.conf
60G
localhost改为*即可开放连接

记得，为了生效，一定要重启

# postgres密码不管，直接root用户
sudo systemctl restart postgresql-14

create user root with SUPERUSER PASSWORD 'root';

编写psql命令尝试去用root用户登录

psql -h 192.168.99.116 -p 5432 -U root -W

发现，光有用户不让登录，得让用户有一个数据库，直接构建一个root库

create database root;

可以在不退出psql的前提下，直接切换数据库

\c root;

四、权限操作

权限操作前，要先掌握一下PGSQL的逻辑结构

在PGSQL中，权限的管理分为很多多层

server、cluster、tablespace级别：这个级别一般是基于pg_hba.conf去配置

database级别：通过命令级别操作，grant

namespace、schema级别：玩的不多……不去多了解这个~~

对象级别：通过grant命令去设置

小任务
构建一个用户（你自己名字）

构建一个数据库

在这个数据库下构建一个schema（数据库默认有一个public的schema）

将这个schema的权限赋予用户

在这个schema下构建一个表

将表的select，update，insert权限赋予用户

完成上述操作

-- 准备用户
create user xcz with password 'xcz';
-- 准备数据库
create database xcz;
-- 切换数据库
\c xcz;
-- 构建schema
create schema xcz;
-- 将schema的拥有者修改为xcz用户
alter schema xcz owner to xcz;
-- 将laozheng库下的laozheng的schema中的表的增，改，查权限赋予给laozheng用户
grant select,insert,update on all tables in schema xcz to xcz;
-- 用postgres用户先构建一张表
create table xcz.test(id int);
-- 切换到laozheng用户。
\c xcz -xcz
-- 报错：
-- 致命错误:  对用户"-xcz"的对等认证失败
-- Previous connection kept
-- 上述方式直接凉凉，原因是匹配连接方式时，基于pg_hba.conf文件去从上往下找
-- 找到的第一个是local，匹配上的。发现连接方式是peer。
-- peer代表用当前系统用户去连接PostgreSQL
-- 当前系统用户只有postgres，没有xcz，无法使用peer连接
-- 想构建xcz用户时，发现postgreSQL的所有文件拥有者和所属组都是postgres，并且能操作的只有拥有者

-- 基于上述问题，不采用本地连接即可。
-- 采用远程连接。
psql -h 192.168.99.116 -p 5432 -U xcz -W
-- 这样依赖，跳过了local链接方式的匹配，直接锁定到后面的host，host的连接方式是md5，md5其实就是密码加密了。
-- 登录后，直接输入
\dn
-- 查看到当前database下有两个schema