postgresql离线安装
pgsql
postgresql离线安装
pgsql

postgresql离线安装

内容 隐藏
postgresql
一、 安装说明
二、数据库postgresql安装
1、资源准备
2、安装操作
3、数据库postgresql配置
4、登录数据库
三、 数据库postgresql简单使用
四、 插件安装（pgsql用户执行）
五、 系统修改
六、 配置修改
七、 具体现场环境的改动

postgresql

一、 安装说明

不能和greenplum安装在一台机器。

二、数据库postgresql安装

1、资源准备

安装过程在linux操作系统的服务器上安装postgresql数据库，安装环境准备如下：

1、linux操作系统版本：

img

2、获取postgresql数据库安装时必须的安装包

  • 查看是否安装了某个软件的命令：rpm -qa | grep 软件名。

  • which命令可查看某个软件的安装路径。

  • 挂载镜像后，使用yum install 包名 -y来安装软件包。

需要的安装包如下：

yum install -y gcc
yum install -y gcc-c++
yum install -y libicu-devel
yum install -y perl-ExtUtils-Embed
yum install -y readline
yum install -y readline-devel
yum install -y zlib
yum install -y zlib-devel
yum install -y openssl
yum install -y openssl-devel
yum install -y pam-devel
yum install -y libxml2-devel
yum install -y libxslt-devel
yum install -y openldap-devel
yum install -y systemd-devel
yum install -y tcl-devel
yum install -y python-devel
yum install -y bzip2-devel

注：yum源操作见附录。

3、postgresql数据库版本

制品库路径如下：

bigdata-version-release/01 基础安装包/001 基础组件包/PostgreSQL/postgresql-11.10_all.tar.gz

4、创建pg数据的管理用户

安装postgresql数据库时，需要使用到root用户，并且需要新建一个pg的管理用户，用于起停postgresql数据库，比如可以创建为pgsql用户，使用该用户用于数据库登录等数据库日常管理。

su - root
#创建管理用户
pgsqluseradd pgsql
#设置管理用户密码
passwd pgsql

2、安装操作

1、上传postgresql数据库安装包（postgresql-11.10_all.tar.gz）至服务器的/home/pgsql用户目录下。并执行解压操作。

su - pgsqlcd /home/pgsqltar -zxvf postgresql-11.10_all.tar.gz

2、创建安装目录

cd /home/pgsqlmkdir pgsql

3、生成数据库配置文件

#进入postgresql解压目录

cd /home/pgsql/postgresql-11.10

tar zxvf postgresql-11.10.tar.gz

cd postgresql-11.10

#生成数据库配置文件（prefix 为数据库的安装目录）

./configure --with-openssl --prefix=/home/pgsql/pgsql

最后没有出现异常，则代表检查通过。

img

4、数据库postgresql的安装

执行make命令

[pgsql@rhino007 postgresql-11.10]$ make

等待一小会，若没有出错，最后出现“All of PostgreSQL successfully made. Ready to install.”则代表编译成功。

img

再执行make install命令

[pgsqlr@rhino007 postgresql-11.10]$ make install

若没有出错，最后出现“PostgreSQL installation complete.”则代表安装成功。

img

3、数据库postgresql配置

1、data目录创建

#postgresql 使用root用户无法启动，需要创建切换到pgsql来初始化启动。

#设置data目录，可以自行设置到其它盘，以data01盘为例

mkdir /data01/pgdata

chown -R pgsql:pgsql /data01/pgdata

2、postgres环境变量配置

su - pgsqlcd ~vi .bash_profile 
export PGDATA=/data01/pgdata 
export LANG=en_US.utf8 
export PGHOME=/home/pgsql/pgsql 
export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH export DATE=`date +"%Y%m%d%H%M"` 
export MANPATH=$PGHOME/share/man:$MANPATH 
export PGUSER=pgsql 
export PGHOST=10.45.150.1 
export PGPORT=5432 
export PGDATABASE=postgres PATH=$PATH:$HOME/.local/bin:$HOME/bin 
export PATH=$PGHOME/bin:$PATH

使环境变量生效（务必执行）

source .bash_profile

3、初始化数据库

#初始化数据库
/home/pgsql/pgsql/bin/initdb --encoding=utf8 -D /data01/pgdata

出现如下截图代表初始化成功

img

  • 成功后修改端口及ip：
vim  /data01/pgdata/postgresql.conf

port = 5432listen_addresses = '10.45.150.1'
  • 添加白名单：
vim  /data01/pgdata/pg_hba.conf

host   all       all       10.45.150.1/32       trust

4、启动数据库。

#启动数据库脚本
/home/pgsql/pgsql/bin/pg_ctl -D /data01/pgdata -l logfile start
#关闭数据库脚本
/home/pgsql/pgsql/bin/pg_ctl -D /data01/pgdata -l logfile stop
#重启数据库脚本
/home/pgsql/pgsql/bin/pg_ctl -D /data01/pgdata -l logfile restart
#查看数据库状态脚本
/home/pgsql/pgsql/bin/pg_ctl -D /data01/pgdata -l logfile status

4、登录数据库 

su - pgsqlpsql
# 或者进入bin目录下执行登录操作：
cd /home/pgsql/pgsql/bin
./psql

img

至此，postgressql数据库安装完成。以下操作根据实际需求执行。

第四~第七章节主要是针对pg数据库的安全配置修改。

三、 数据库postgresql简单使用

1、设置默认用户postgres的密码

#输入psql登录。

[postgres@rhino104 ~]$ psql

psql (11.3)

Type "help" for help.

#修改默认用户postgres用户的密码（postgres）

postgres=# \password

Enter new password: 

Enter it again:

 postgres=#

2、创建新用户hyptest，新库hyptestdb，并且赋予权限

postgres=#create user "hyptest" with password 'hyptest';
postgres=#create database "hyptestdb" with owner="hyptest";
postgres=#grant all privileges on database hyptestdb to hyptest ;
#退出当前登录
postgres=# \q

3、使用新用户登录系统

#登录到新建的用户hyptest的hyptestdb数据库中。
psql -U hyptest -W hyptestdb

回车，输入密码：hyptest，回车登录。

img

4、创建表test，并插入数据测试数据库是否可以正常使用

create table test(  
    id int ,    
    age int,    
    date timestamp default now() 
);
#插入一条数据
insert into test values (1,1);
#查询数据库结果
select * from test ;

查询出如下的的结果即为安装成功。

img

四、 插件安装（pgsql用户执行）

1、拷贝插件

拷贝pgaudit-1.3.2.tar.gz、pg_permission-master.zip、set_user-master.zip、pgbackrest-master.zip到安装包到/home/pgsql/目录下，并解压：

tar -zxvf pgaudit-1.3.2.tar.gz
unzip pg_permission-master.zip
unzip set_user-master.zip
unzip pgbackrest-master.zip

2、安装pgAudit

将解压出来的+目录移入/home/pgsql/postgresql-11.10/contrib目录下，

mv pgaudit-1.3.2 /home/pgsql/postgresql-11.10/contrib

cd /home/pgsql/postgresql-11.10/contrib/pgaudit-1.3.2

执行make

img

并再次执行make install

img

打开配置文件：vi /data01/data/postgresql.conf

增加配置：

shared_preload_libraries = 'pgaudit'pgaudit.log='ddl,write'

然后使用/home/pgsql/pgsql/bin/pg_ctl -D /data01/data -l logfile restart来重启pg数据库。

使用psql命令登录pg，查询验证插件是否可用：

img

3、安装set_user-master

将解压出来的set_user-master目录移入/home/pgsql/postgresql-11.10/contrib目录下：

mv set_user-master /home/pgsql/postgresql-11.10/contrib

cd /home/pgsql/postgresql-11.10/contrib/set_user-master

执行make

img

执行make install

img

使用psql命令登录pg，查询插件是否可用：

img

4、安装pg_permission-master

将解压出来的pg_permission-master目录移入/home/pgsql/postgresql-11.10/contrib目录下：

mv pg_permission-master /home/pgsql/postgresql-11.10/contrib

cd /home/pgsql/postgresql-11.10/contrib/pg_permission-master

执行make install

img

使用psql命令登录pg，查询插件是否可用：

img

5、安装pgcrypto

cd /home/pgsql/postgresql-11.10/contrib/pgcrypto

执行make

img

再执行make install

img

使用psql命令登录pg，查询插件是否可用：

img

5、安装passwordcheck

cd /home/pgsql/postgresql-11.10/contrib/passwordcheck

执行make

img

再执行make install

img

打开配置文件：vi /data01/data/postgresql.conf

修改配置：

shared_preload_libraries = 'pgaudit,passwordcheck'

然后使用/home/pgsql/pgsql/bin/pg_ctl -D /data01/data -l logfile restart来重启pg数据库

使用psql命令登录pg，验证插件是否可用：

创建用户时密码过短或者简单都不会创建成功。

img

6、安装pgbackrest-master.zip

cd /home/pgsql/pgbackrest-master/src

执行./configure进行配置：

img

执行make命令：

img

执行make install

img

若出现如上异常，则切换到root用户来安装：

img

其中，pgbackrest插件不会在pg数据库pg_available_extensions表中存在，会在命令行有pgbackset命令可用

五、 系统修改

1、创建postgresql.service

切换root用户：su - root

cd /usr/lib/systemd/system，创建postgresql.service

示例模板文件：

img

vi postgresql.service，其中标黄处根据实际配置修改。

[Unit]
Description=PostgreSQL database server
After=network.target

[Service]
Type=forking

User=pgsql

Group=pgsql

# Port number for server to listen on
Environment=PGPORT=65432

# Location of database directory
Environment=PGDATA=/data01/data

# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog

# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
#ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA}
ExecStart=/home/pgsql/pgsql/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
ExecStop=/home/pgsql/pgsql/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/home/pgsql/pgsql/bin/pg_ctl reload -D ${PGDATA} -s

# Give a reasonable amount of time for the server to start up/shut down
TimeoutSec=300

[Install]
WantedBy=multi-user.target

修改完成后，保存。

执行命令：systemctl enable postgresql.service使之生效。

然后就可以执行systemctl start/stop/status postgresql.service来控制pg的启停和状态查看。

2、确保使用FIPS 140-2 OpenSSL加密（暂不执行）

yum -y install dracut-fips

dracut -f

修改/etc/default/grub，给GRUB_CMDLINE_LINUX增加上fips=1

如果系统从BIOS启动，则使用：

grub2-mkconfig -o /boot/grub2/grub.cfg

如果系统从EFI启动，则使用:

grub2-mkconfig -o /boot/efi/EFI/centos/grub.cfg

若不确定，则两个命令都执行。

使用reboot重启操作系统。

Ps: 虚拟机增加fips=1重启时，系统启动会有问题，暂不知如何解决，因此未修改。

2、确保SSL已启用并配置正确

su - rootcd /data01/data输入命令：openssl req -new -text -out server.req

其中，标黄处可按照实际需求修改

image-20220909173824193

使用命令：

openssl rsa -in privkey.pem -out server.key && rm privkey.pem

Enter pass phrase for privkey.pem:123456writing RSA key

使用命令：

openssl req -x509 -in server.req -text -key server.key -out server.crt

chmod og-rwx server.key

修改postgresql.conf文件，增加如下内容：

ssl = onssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH'ssl_cert_file = 'server.crt'ssl_key_file = 'server.key'password_encryption = scram-sha-256

修改用户：

chown pgsql:pgsql server*

重启pg：

systemctl restart postgresql.service

假如重启失败，报如下异常：

img

则使用chmod 0600 /data01/data/server*，然后再次重启。

重启完成，登录客户端，使用show ssl;来查看ssl是否启动。

重启后切换到pgsql登录需要重新source环境变量：su - pgsqlcd ~source ./.bash_profile

img

确定文件 /data01/data/server.key存在

确定文件 /data01/data/server.crt存在

4、禁用postgresql用户远程登录（不执行）

编辑/etc/passwd文件，将pg的管理用户（pgsql）所在行改为/sbin/nologin或者/bin/false

Ps:修改完成后将不能使用管理用户（pgsql）远程登录

六、 配置修改

1、日志配置修改

确保日志采集器已启用

使用psql命令进入pg的操作终端，执行语句：

alter system set logging_collector = 'on';

alter system set log_truncate_on_rotation = 'on';

alter system set log_connections = 'on';

alter system set log_disconnections = 'on';

alter system set log_statement='ddl';

alter system set log_timezone = 'GMT';

alter system set log_directory='logs';

alter system set log_filename='postgresql-%Y%m%d.log';

select pg_reload_conf();

然后重启pg：

systemctl restart postgresql.service（root用户执行）

查看是否生效：

systemctl status postgresql|grep 'ago$'

img

2、确保创建一个仅复制的用户并将其用于流式复制

Ps：pg无主备可以不操作该项

使用psql命令登录pg客户端，执行：

create user replication_user REPLICATION encrypted password 'XXX';

select rolname from pg_roles where rolreplication is true;

3、修改权限

1）执行下列命令：

su pgsql

cd ~

ls -ld .{bash_profile,bashrc}

echo "umask 077" >> .bash_profile

echo "umask 077" >> .bash_rc

source .bash_profile

source .bash_rc

umask

2）确保PostgreSQL pg_wheel组成员正确

切换到root用户，执行命令：

groupadd pg_wheel && getent group pg_wheel

gpasswd -a pgsql pg_wheel 其中pgsql为pg的管理用户

awk -F':' '/pg_wheel/{print $4}' /etc/group

3）文件目录权限修改

修改pg的目录权限

chown pgsql:pgsql /home/pgsql/pgsql

chmod 0700 /home/pgsql/pgsql

修改pg的日志权限

chown pgsql:pgsql /data01/data/logs

chmod 0700 /data01/data/logs

修改data目录权限

chmod 700 /data01/data

chown pgsql:pgsql /data01/data

修改备份目录的权限

Ps：pg无主备可以不操作该项

chown pgsql:pgsql /home/pgsql/pgsql/backups

chmod 0700 /home/pgsql/pgsql/backups

修改完成后重启pg查看是否能正常登入。

4、修改pg_hba.conf

1）修改pg_hba.conf中本地连接配置为peer方式校验

local all all peer

2）信任项修改

vi /data01/data/pg_hba.conf中修改剩下所有项配置为非trust方法，配置为SCRAM-SHA-256

img

登录pg客户端执行命令：set password_encryption to 'scram-sha-256';

修改用户密码：alter user pgsql with password '***';

并使用命令：select * from pg_authid where rolname='pgsql';来查看用户的加密方式。整个操作如下图所示：

img

最后命令行执行pg_ctl reload命令重新加载pg的配置文件。

3）对于host或者hostssl或者hostnossl开头的记录，ip除了127.0.0.1/32，不允许设置其他ip。

4）在/data01/data/pg_hba.conf中增加：

hostssl replication replication_user 0.0.0.0/0 md5

在使用pg_basebackup进行备份和恢复时使用创建的这个复制用户和密码。

Ps：pg无主备可以不操作该项

修改完成使用/home/pgsql/pgsql/bin/pg_ctl -D /data01/data -l logfile restart命令重启

5、登录pg客户端修改

pg客户端登录后执行：

REVOKE CREATE ON SCHEMA PUBLIC FROM public;

REVOKE ALL ON pg_settings FROM PUBLIC;

七、 具体现场环境的改动

以下条目均是针对具体现场的需求来改动。

1、确保撤销过多的管理权限

对于应用用户（非超级用户）来讲，不应该赋予额外的管理权限。（黄色部分根据实际情况修改）

l 可通过\du查看所有用户

执行：

psql -c "ALTER ROLE appuser NOSUPERUSER;"

psql -c "ALTER ROLE appuser NOCREATEROLE;"

psql -c "ALTER ROLE appuser NOCREATEDB;"

psql -c "ALTER ROLE appuser NOREPLICATION;"

psql -c "ALTER ROLE appuser NOBYPASSRLS;"

psql -c "ALTER ROLE appuser NOINHERIT;"

Ps:需要根据现场具体情况移除对应的用户多余的管理权限

2、确保行级别安全性（RLS）配置正确

该项是指现场创建的表要符合该策略，并非gp的某个配置或者插件，而是实际创建的某张表的策略分配。

示例，比如创建测试表:

create table test_row(id serial primary key, username text, log_event text);

Insert into test_row(username, log_event) values('user1','user1:aaa');

Insert into test_row(username, log_event) values('user1','user1:bbb');

Insert into test_row(username, log_event) values('user2','user2:aaa');

Insert into test_row(username, log_event) values('user2','user2:bb');

Insert into test_row(username, log_event) values('user3','user3:aaa');

Insert into test_row(username, log_event) values('user3','user3:aaaaaa');

Insert into test_row(username, log_event) values('user4','user4:aaa');

然后创建测试用户user1，user2，user3.

create role user1 with login;

create role user2 with login;

create role user3 with login;

grant select on test_row to user1, user2, user3;

grant usage on schema public to user1, user2, user3;

然后psql命令默认用户登陆后，使用select * from public.test_row;查看查询出来的数据。

img

再创建策略,限制数据库登陆用户只能查看当前用户的日志记录：

create policy policy_test_row on test_row for select to public using (username=current_user);

select relname,relrowsecurity from pg_class where relname = 'test_row';

alter table test_row enable row level security;

select relname,relrowsecurity from pg_class where relname = 'test_row';

测试：

\c postgres user1

img

\c postgres user2

img

3、默认用户名应删除-“postgres用户不存在”

删除postgres用户名，使用其他用户名替代。

l 查看所有用户：\du

img

l 删除postgres用户。

drop user postgres;

l 假如现场存在postgres用户，可以创建另外一个超级用户代替。

create user super;alter user super with Superuser Createrole CreateDB Replication BypassRLS;

4、用户角色不应具有过期的密码

对于拥有过期密码的用户可以删除，使用DROP ROLE 来删除。

使用select * from pg_user;来查看当前环境的所有用户，valuntil展示的为密码过期时间。

img

5、数据库不允许无限连接

配置指定具体数据库连接的个数限制，需要现场根据需求评估修改。

登录pg客户端，执行命令：

UPDATE pg_database SET datconnlimit= WHERE datname=’’

img

标黄部分需要根据具体现场需求修改：

-------连接个数限制

---------数据库名称

6、数据库模板设置

根据现场具体的模板数据库名称来设置，配置为false，则没有人能连接到这个数据库，比如template0就默认设置为false用来保护模板数据库template0不被修改。

登录pg客户端，执行命令：

UPDATE pg_database SET datallowconn =false WHERE datname=;

标黄部分需要根据具体现场需求修改：

---------数据库名称

7、pg_hba.conf中删除所有数据库、用户、地址为all的项

需要根据现场需求配置修改，将不同的连接地址配置指定具体的用户和数据库，如下图，标红的DATABASE、USER、ADDRESS不能均为all。

![img](https://lemon-guess.oss-cn-hangzhou.aliyuncs.com/img/202209091739213.jpg)

8、限制密码寿命

根据现场具体需求修改。修改值在要求范围内，如有需求修改，

登录pg客户端，执行修改语句：:

alter role test valid until '2021-01-30 17:00:00';

标黄的用户和时间需要根据具体情况配置。

也可以使用如下命令来查看所有用户的密码过期时间（valuntil列）。

![img](https://lemon-guess.oss-cn-hangzhou.aliyuncs.com/img/202209091739524.jpg)

# 附录

**一、yum源操作**

1、挂载镜像

#新建挂载目录：mkdir -p /mnt/cdrom #挂载操作系统iso镜像文件：mount -o loop /root/centos7/CentOS-7-x86_64-Everything-1804.iso /mnt/cdrom

注：iso文件必须和实际操作系统版本保持一致！可执行cat /etc/redhat-release查看。

2、备份并删除其它源文件

cd /etc/yum.repos.d/tar -zcvf repo.tar.gz ./*rm -rf CentOS*

3、修改yum源

| vim /etc/yum.repos.d/cdrom.repo |
| ------------------------------------------------------------ |
| 修改文件内容：[cdrom]name=cdrombaseurl=file:///mnt/cdromenabled=1gpgcheck=0 |

发表回复

您的电子邮箱地址不会被公开。