【玩转PGSQL】基础管理&防火墙及用户权限管理-知否知否

基础管理

登录方式

psql -d postgres -h 127.0.0.1  -p 5432 -U postgre

-d 库
-h IP
-p 端口
-U 用户

基础sql

-- 查看所有库
\l

-- 创建库
create database test;

-- 进入库
\c test

-- 查看所有表
\d

-- 查看表结构
\d t1;

-- 查看所有用户
\du

-- 列显示
\x

-- 显示当前库下schema信息
\dn

-- 创建表
create table t1(id int);

-- 创建t2表复制t1表
create table  t2 (like t1);

pg防火墙介绍

此配置是按照从上到下的顺序读取的，应用顺序也是从上到下，当匹配到对应规则将不往下读取，注意编辑顺序！！！

cat >/pgdata/12/data/pg_hba.conf<<'EOF'
# TYPE  DATABASE        USER            ADDRESS                 METHOD 可设置/reject/md5/trust
# 配置自己对应的user/host 
host    all             all             0.0.0.0/0               md5
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# 允许具有复制权限的用户从本地主机进行复制连接
local   replication     all                                     md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
EOF


vim /pgdata/12/data/postgresql.conf
# 放开所有地址
listen_addresses = '*'  

# 重启服务生效
pg_ctl  reload

# 不重启重载配置文件
select pg_reload_conf();

用户管理

连接管理实例的的数据库对象

• 用户定义语法

create user  # 创建用户 默认有登录权限
create role  # 创建角色 默认无登录权限
drop user    # 删除用户
alter user   # 修改用户
\du          # 查看所有用户

\h create user  # 查看帮助

• 示例：

-- 创建角色 test1  带超级权限 登录权限 配置密码为 dyh666
create role test1  with SUPERUSER LOGIN password 'dyh666';

-- 普通用户
create role test2  with  LOGIN password 'dyh666';

-- 创建复制用户
create role test3  with REPLICATION  LOGIN password 'dyh666';

-- 修改用户
alter role test1 with NOSUPERUSER login password 'dyh666';

-- 删除用户
drop user test1;

删除用户时不能存在属于这个用户的表

-- 方法 1： 修改将属主为 test1 用户 的表分配给 test2 用户
reassign owned by test1 to test2;

-- 方法 2：删除属于 test1 的表 
-- 需要在每个有 test1 表的数据库运行 
-- 需要手动删除数据库或表空间
drop owned by test1;

-- 删除用户
drop user test1;

权限管理

• 权限简介

cluster权限：实例权限通过pg_hba.conf配置。

database权限：数据库权限通过grant和revoke操作schema配置。

TBS权限：表空间权限通过grant和revoke操作表、物化视图、索引、临时表配置。

schema权限：模式权限通过grant和revoke操作模式下的对象配置。

object权限：对象权限通过grant和revoke配置。

• 权限定义

  • database 权限

    -- 授权test1用户对test库拥有create权限
    grant create on database test to test1;
    

  • schema 权限

    -- 创建schema 默认为当前用户
    create schema  test_schema;
    
    -- 创建schema test2 授权给 test1 用户
    create schema test2 authorization test1;
    
    -- 进入schema
    set search_path to  test_schema;
    
    -- 查看一个database下有几个schema
    select * from information_schema.schemata;
    
    -- 将test_schema的拥有者设置为test用户
    alter schema test_schema  owner to test;
    
    -- 进入test 库
    \c test
    
    -- 授权 test 用户 test_chema 下的所有表，增删改查权限
    grant select,insert,update,delete on all tables in schema test_schema  to test;
    
    -- 删除模式
    drop schema test1;
    
    -- 删除模式及其包含的对象
    drop schema test2 cascade;
    

  • object 权限

    使用角色组管理用户权限

    -- 创建角色组
    create role select_group;
    
    -- 授权 t1 表查询权限
    grant select on t1 to select_group;
    
    -- 创建可登录用户
    create role user_test with login;
    
    -- 授权 select_group 组给 user_test 用户
    grant select_group to user_test;
    

业务案例

需求：开发部门需要新上线一个业务，这个业务需要新建一个 test_app 库 , 创建一个schema，并创建一个 test_app_user 用户作为业务用户，对所有表拥有增删改的权限。

--创建库
create database test_app;

--!!! 必须进库，不然会在当前库下创建
\c test_app

--创建schema
create schema test_app_schema;

--进入schmea
set search_path to  test_app_schema;

--创建用户
create user test_app_user with password '123456';

--授权用户为 schema 属主
alter schema test_app_schema owner to test_app_user;

--授权用户操作 schema下所有表的操作权限
grant  insert,select,update  on all tables in schema test_app_schema to test_app_user;

# 验证
-- 进库
\c test_app

-- 查看schema
select * from information_schema.schemata;

--要想删除用户必须回收所有权限 或 授权给其他用户
revoke all on schema test_app_schema from test_app_user;
revoke all on all tables in schema test_app_schema  from test_app_user;
revoke all on database test_app   from test_app_user;
revoke connect on database test_app  from test_app_user;
drop user test_app_user;

# 查看xl_weibo_user用户的表权限信息
select * from information_schema.table_privileges where grantee='test_app_user';

知否？