PostgreSQL基本配置与权限控制

PostgreSQL基本配置与权限控制

修改PgSQL基本配置：

重要文件和目录：

配置文件:          postgresql.conf
登录权限控制文件:   pg_hba.conf
数 据 目 录 ：      base
运行日志目录：      log
归档日志目录：      pg_wal

修改PgSQL侦听地址：

• 在单引号内写入IP地址，如果要侦听多个IP地址，以逗号分隔

[root@postgrssql ~]# vim /var/lib/pgsql/11/data/postgresql.conf

listen_addresses = 'localhost,172.18.1.76'

修改PgSQL侦听端口：

• 默认端口 5432

[root@postgrssql ~]# vim /var/lib/pgsql/11/data/postgresql.conf

port = 5432

修改PgSQL最大连接数：

• 修改连接数需要重启，对比MySQL一般设置 512 ~ 1024

[root@postgrssql ~]# vim /var/lib/pgsql/11/data/postgresql.conf

max_connections = 100

打开PgSQL复制日志：

• 类似于MySQL binlog，复制到从库并应用一遍日志操作就达到数据同步了

# 是否开启 archive_log日志功能
archive_mode = on
# 归档 archive_log日志要执行的命令，当前不准备归档就执行 cd命令
archive_command = 'cd .' 

[root@postgrssql ~]# ll -sh /var/lib/pgsql/11/data/pg_wal
total 16M
16M -rw------- 1 postgres postgres 16M Nov 23 20:47 000000010000000000000001
  0 drwx------ 2 postgres postgres   6 Nov 22 15:21 archive_status

PgSQL用户权限控制：

• 在数据库中创建对象时，都会为其分配所有者。 所有者通常是执行创建语句的用户。 对于大多数类型的对象，初始状态是只有所有者(或超级用户)可以修改或删除对象。 要允许其他角色或用户使用它，必须授予权限或权限。

配置超管用户安全：

• 默认是 all 所有用户都拥有peer对等权限，只修改为postgrs用户拥有peer权限

[root@postgrssql ~]# sed -i '80d' /var/lib/pgsql/11/data/pg_hba.conf
[root@postgrssql ~]# sed -i '79a local   all             postgres                                peer' /var/lib/pgsql/11/data/pg_hba.conf

创建用户：

# 创建 olda 用户
postgres=# create user olda with password '123456';
CREATE ROLE
# 查看所有用户
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 olda      |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

用户授权权限：

# 切换到 course 数据库
postgres=# \c course
You are now connected to database "course" as user "postgres".
# 在 course 数据库授权所有表的所有权限到 olda用户
course=# grant all on all tables in schema public to olda;
GRANT
# 查看 course 数据库所有表的权限表信息
course=# \dp+
                                  Access privileges
 Schema |   Name   | Type  |     Access privileges     | Column privileges | Policies 
--------+----------+-------+---------------------------+-------------------+----------
 public | students | table | postgres=arwdDxt/postgres+|                   | 
        |          |       | olda=arwdDxt/postgres     |                   | 
(1 row)

配置用户连接：

• 在PgSQL内创建用户后也是不可以登录的，需要在pg_hba.conf文件中配置连接源信息，否则不允许远程登录。
• 加入 olda用户，local本地可以登录所有库，登录密码验证方式为md5

[root@postgrssql ~]# vim /var/lib/pgsql/11/data/pg_hba.conf

# "local" is for Unix domain socket connections only
local   all             postgres                                peer
local   all             olda            0.0.0.0/0               md5

# 重载 PostgreSQL
systemctl reload postgresql-11.service

# 测试 olda用户连接 PgSQL的 course库
-bash-4.2$ psql -U olda -W  course
Password: 
psql (11.6)
Type "help" for help.

course=> \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 course    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

配置默认权限：

• 意思为 postgres用户创建的所有表都默认对 olda用户重新授予权限

postgres=# alter default privileges for role postgres grant all on tables to olda;
ALTER DEFAULT PRIVILEGES

配置用户远程连接：

• 默认在pg_hba.conf文件中加入密码验证方式还是不可以远程访问，因为是 local方式。
• 加入 olda用户，host方式，所有来源IP都可以登录所有库，登录密码验证方式为md5

-bash-4.2$ psql -U olda -W -h 172.18.1.76 -p 5432 course
Password: 
psql: FATAL:  no pg_hba.conf entry for host "172.18.1.76", user "olda", database "course", SSL off
[root@postgrssql ~]# vim /var/lib/pgsql/11/data/pg_hba.conf

# "local" is for Unix domain socket connections only
local   all             postgres                                peer
local   all             olda                                    md5
host    all             olda            0.0.0.0/0               md5

# 重载 PostgreSQL
[root@postgrssql ~]# systemctl reload postgresql-11.service

# 测试是否可以远程连接到 PostgreSQL
-bash-4.2$ psql -U olda -W -h 172.18.1.76 -p 5432 course
Password: 
psql (11.6)
Type "help" for help.

course=>