PostgresSQL 使用实践

1： 常见的系统表和系统视图

（1）显示所有的系统表

\dt pg_*

表名字 用途

pg_class    表、索引、序列、视图(“关系”)
pg_database    本集群内的数据库
pg_index    附加的索引信息
pg_proc    函数和过程
pg_trigger    触发器
pg_type    数据类型

(2) 显示所有的系统视图

\dv pg_*

视图名    用途
pg_cursors    打开的游标
pg_locks    当前持有的锁
pg_roles    数据库角色
pg_settings    参数设置
pg_tables    表
pg_user    数据库用户
pg_views    视图

2: 数据库的对象

PostgreSql数据库对象主要有数据库、表、用户、视图、索引、schema、函数、触发器等。

(1)：查询数据库表（系统表或用户创建的表）

[1]：select * from pg_catalog.pg_tables; 或者 select * from information_schema.tables;

[2]: select * from pg_catalog.pg_tables where tablename = ‘ XX’; 或者  select * from information_schema.tables where table_name = 'XX';

[3]: \d+ XX

(2) : 查询当前schema

select current_schema();

返回数据库中所有schema:

select * from information_schema.schemata;

select * from pg_catalog.pg_namespace;

(3):查询当前的数据库： select current_database();

列出所有数据库：\l  或 \l+

(4) 查看数据库中的视图 select * from pg_views where viewname = 'XXXX';

查看数据库中的视图 \dv XXXX

(5) 查看表空间

\db

(6)查看用户信息

\dn (得到schema 和 owner信息)

(7) \d: 列出当前数据库的所有表格

\d [table_name]: 列出某一张表格的表结构

(8) 查看当前数据库用户：select * from current_user; / select current_user;

3： PG的权限体系

（1）数据库权限

• 允许连接数据库 ：默认情况下，数据库在创建后，允许public角色连接，即允许任何人连接

       (1) create user user_name password 'XXXX';

        (2) \du （查看创建的user）

        (3) 可以用这个user_name + password来连接数据库

        (4) 可以创建对象，但是都是创建到public shema上面，例如 create table test_t(a int); 可以用\d+ [test_name]来查在哪个schema、owner下。

• 允许创建数据库和调整数据库

        (1) 查看默认创建的用户权限

            select * from information_schema.table_privileges where grantee = 'default_user_name';

            或者 \du

        (2) 创建数据库 \h create database  （查看创建数据库语法）

                create databse db_name owner='user_name' ; (这个user是具有创建database的权限的)

        (3) 超级用户进行用户的授权，之后

                create user user_name password 'XXXX'; (创建一个普通用户)

                alter uname createdb; (超级用户给用户user_name赋予创建数据库的权限)

                \du 查看

                create databse db_name owner='user_name' ;

                使用创建用户登录数据库 \c  db_name db_name

• 允许数据中创建schema

        默认情况下，数据库在创建后，不允许除了超级用户和owner之外的任何人在数据库中创建schema。

        默认情况下，数据库在创建后，会自动创建名为public 的schema，这个schema的all权限已经赋予给public角色，即允许任何人在里面创建对象。

(2) 用户权限

角色/用户 在CREATE USER创建时赋予权限，ALTER USER方式变更权限。

SUPERUSER	数据库的超级用户拥有该数据库的所有权限
CREATEDB	角色要想创建数据库，必须明确赋予创建数据库的属性(包含变更与删除权限)
CREATEROLE	一个角色要想创建更多角色，必须明确给予创建角色的属性
LOGIN	登录的权限
REPLICATION	专门用于执行复制动作的权限

角色/用户在GRANT方式赋予权限，REVOKE方式撤销权限

SELECT	允许从指定表，视图或序列的任何列或列出的特定列进行SELECT。
INSERT	允许将新行INSERT到指定的表中。如果指定特定列，那么其它列将以默认值写入。
UPDATE	允许更新指定表的任何列或列出的特定列，需要SELECT权限。
DELETE	允许删除指定表中的行，需要SELECT权限。
TRUNCATE	允许在指定的表上创建触发器。
REFERENCES	允许创建引用指定表或表的指定列的外键约束。
TRIGGER	允许在指定的表上创建触发器。
CREATE	对于数据库，允许在数据库中创建新的schema、table、index。
CONNECT	允许用户连接到指定的数据库。在连接启动时检查此权限。
TEMPORARY	允许在使用指定数据库时创建临时表。
EXECUTE	允许使用指定的函数或过程以及在函数。
USAGE	对于schema，允许访问指定模式中包含的对象；对于sequence，允许使用currval和nextval函数。对于类型和域，允许在创建表，函数和其他模式对象时使用类型或域。
ALL PRIVILEGES	一次授予所有可用权限。
SAME PRIVILEGES

（3）pg_user、pg_authid、pg_roles、pg_auth_members区别

\d pg_authid

\d pg_user

\d pg_auth_members

（3）schema权限

​         一个数据库包含一个或多个已命名的模式，模式又包含表。模式还可以包含其它对象，包括数据类型、函数、操作符等。同一个对象名可以在不同的模式里使用而不会导致冲突；比如，schema1和myschema都可以包含一个名为mytable的表。和数据库不同，模式不是严格分离的：只要有权限，一个用户可以访问他所连接的数据库中的任意模式中的对象。

        schema级别的权限，包括允许查看schema中的对象，允许在schema中创建对象。
​ 默认情况下新建的schema的权限不会赋予给public角色，因此除了超级用户和owner，任何人都没有权限查看schema中的对象或者在schema中新建对象。

schema的特点

• schema概念像命名空间
• schema下不能再有schema嵌套
• 各个对象比如表,函数等存放在各个schema下
• 同一个schema下不能有重复的对象名字,但在不同schema下可以重复

使用schema的作用

• 方便管理多个用户共享一个数据库,但是又可以互相独立.
• 方便管理众多对象,更有逻辑性
• 方便兼容某些第三方应用程序,创建对象时是有schema的

查看schema： \dn 或 \dnS+

创建schema: create schema shcema_name;

在schema上撞见表对象：create table schema_name.table_name(a int); 这样创建在 shcema_name上。要是不指定的话，默认是创建在public上的。对于指定的创建，他的查询、删除也要指schema。

删除schema: drop schema schema_name

drop schema schema_name cascade; (这样就会把这个schema下面的表也给删了)

其他相关的请查看链接：【PG管理维护】-Postgresql权限体系(万字总结测试) - 墨天轮