基础管理
登录方式
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';
知否？
