基础管理
登录方式
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： 修改将属主为 test 的表分配给 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';
