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=> 
「点点赞赏,手留余香」

    还没有人赞赏,快来当第一个赞赏的人吧!
0 条回复 A 作者 M 管理员
    所有的伟大,都源于一个勇敢的开始!
欢迎您,新朋友,感谢参与互动!欢迎您 {{author}},您在本站有{{commentsCount}}条评论