PostgreSQL学习笔记（三）：psql、用户角色、系统表

PostgreSQL学习笔记（三）：psql、用户角色、系统表

1. psql

• psql是PostgreSQL的一个命令行交互式客户端；
• 通过执行内置的元命令或sql语句查看管理数据库对象；
• 字符界面，比图形化管理工具操作更直接方便；能够通过tab键自动补全；

常用命令：

\? #所有命令帮助
\c [database_name] #切换到指定的数据库
\c #显示当前数据库名称和用户
\conninfo #显示客户端的连接信息
\d #列出数据库中所有表
\d+ #列出数据库中所有表和详细信息
\dt #列出数据库中所有表
\d [table_name] #显示指定表的结构
\di #列出数据库中所有 index
\dv #列出数据库中所有 view
\du #显示所有用户
\dn #显示数据库中的schema
\h #sql命令帮助
\pset border 0|1|2 #更改查询结果边框显示风格
\q #退出连接
\set [参数名] [参数值] #修改参数，参数名大小写敏感；不加参数，则显示当前参数；
\timing #显示命令执行消耗的时间
\l #列出所有数据库
\encoding #显示字符集
\i testdb.sql #执行sql文件
\x #扩展展示结果信息，相当于MySQL的\G
\o /tmp/t1.txt #将下一条sql执行结果导入文件中

2. 用户角色

2.1 概述

• 数据库用户和操作系统用户不同，但是可以通过文件做映射；
• PG通过角色role来管理数据库访问权限，role是一系列相关权限的集合；
• role在整个cluster中是全局性的，即同一个服务器的不同数据库中，看到的用户是相同的；
• PG的用户分为两类：
  • 超级用户–postgres
  • 普通用户–根据需要创建

2.2 user、role和group的区别

• user：拥有login登录数据库权限的role；
• role：可以拥有数据库对象，如表、索引，也可以把这些对象上的权限富裕其他橘色，以控制哪些用户对哪些对象拥有哪些权限；
• group：不拥有replication/noreplication、connection limit属性的role；
• 在PG8.1之前，user与group是不同类型的实体，现在被看做是role，任意一个role均可自由地在user与group之间转换；

2.3 创建用户、角色

• CREATE USER|ROLE name [[WITH] option […]]

  option可以是：

  SUPERUSER|NOSUPERUSER

  CREATEDB|NOCREATEDB

  CREATEROLE|NOCREATEROLE

  ​LOGIN|NOLOGIN

  …
  ​ 系统用户表：pg_user
  ​ 系统角色表：pg_roles

select rolname from pg_roles;
select usename from pg_user;

​ 创建后，用户默认是可以登录的，而角色不能登录；
​ 创建用户时，会默认创建同名的角色，而创建角色只会创建角色。而当创建角色时赋予了登录权限时，才会在系统用户表中新增，也代表着可以使用角色名（成为用户）登录了。

• DROP user|role “name” ，删除用户|角色：

  当用户拥有表时，会提示不能删除；转换关系：用户<—登录权限–>角色

2.4 授权

PostgreSQL的不同类型的权限：
SELECT，INSERT，UPDATE，DELETE，TRUNCATE，REFERENCES，TRIGGER，CREATE，CONNECT，TEMPORARY，EXECUTE和USAGE;

1. 使用GRANT命令将权限授予特定的用户或用户组；

GRANT <权限列表> on <数据库对象> to <用户或角色> -- 授权
GRANT <权限列表> on <数据库对象> to <用户或角色> with grant option;  -- 授权+授权权限
-- 任意一列的授权；
GRANT <权限列表>(<数据库对象的属性>) on <数据库对象> to <用户或角色> [ with grant option ]; 

2. 使用 REVOKE 命令可以从某个特定环境中回收用户已经授予的权限;

REVOKE <权限列表> on <数据库对象> FROM <用户或角色>;

3. 系统表、系统函数

​ 服务自动更新维护，不需要人工维护；

-- 系统表、视图
\dS [type]
 -- pg_tables 
 -- pg_views 
 -- pg_indexs
 -- pg_trigger
 -- pg_constraints

查看支持的系统函数：

SELECT
  pg_proc.proname AS "函数名称",
  pg_type.typname AS "返回值数据类型",
  pg_proc.pronargs AS "参数个数"
FROM
  pg_proc
JOIN pg_type ON (pg_proc.prorettype = pg_type.oid);
 "函数名称",
  pg_type.typname AS "返回值数据类型",
  pg_proc.pronargs AS "参数个数"
FROM
  pg_proc
JOIN pg_type ON (pg_proc.prorettype = pg_type.oid);