postgresql建表空间、建库、建模式、建用户的规范

一、官方概念说明

1、表空间（表空间位置不应该位于数据目录内）

性能优化：您可以将高I/O的表和索引放在高速磁盘上，而将不经常访问的表放在普通的磁盘上。
管理磁盘空间：当数据库存储空间不足时，可以通过表空间在其他磁盘上创建更多的存储空间。

如果没有上面2个诉求，可以使用默认表空间。

2、模式

官方推荐：在管理员创建一个具体数据库后，应该为所有可以连接到该数据库的用户分别创建一个与用户名相同的模式，然后，将search_path设置为"$user"(即缺省模式为与用户名相同的模式)，这样，任何当某个用户连接上来后，会默认将查找或者定义的对象都定位到与之同名的模式中。这是一个好的设计架构。

二、数据库创建

1、表空间管理

创建表空间

create tablespace dynamic owner postgres location 'E:\programs\PostgreSQL\14\data\space_dynamic';

删除表空间

drop tablespace dynamic

2、用户管理

创建用户：

CREATE USER test WITH PASSWORD 'abcd@1234';

修改用户密码：

ALTER USER postgres WITH PASSWORD 'new_password';

删除用户：

DROP USER username;

用户授权：

GRANT SELECT ON sales_db TO sales_user;--赋予用户sales_db这个数据库的读取权限

给单个表赋所有权限

GRANT ALL PRIVILEGES ON TABLE tablename TO username;

用户授权：给所有表的增删改查权限，（他可以建表，可以删他自己的表，不能删别人建的表）

grant ALL PRIVILEGES on schema dynamic to dynamic;

3、数据库管理

建数据库：

CREATE DATABASE test WITH OWNER=user ENCODING='UTF-8';

删除数据库：

DROP DATABASE sales_db;

4、模式管理

创建模式：

CREATE SCHEMA sales_schema;

删除模式：

DROP SCHEMA sales_schema;

让用户默认查询的就是该指定模式下的表数据

show search_path;
ALTER USER dynamic SET search_path to "dynamic";

最后附件：用户赋权限示例，来自chatGPT。

最后给一套完整建库sql语句标准模板：

--创建表空间
create tablespace dynamic owner postgres location 'E:\programs\PostgreSQL\14\data\space_dynamic';
--创建用户
CREATE USER dynamic_user WITH PASSWORD 'xtkfqHbgydx@20230824566232738.xtHbj';
--创建数据库
CREATE DATABASE dynamic WITH TABLESPACE dynamic  OWNER=dynamic_user ENCODING='UTF-8';
--创建模式
CREATE SCHEMA dynamic AUTHORIZATION dynamic_user


--设置用户的默认模式
show search_path;
ALTER USER dynamic_user SET search_path to "dynamic";
--给用户赋予权限
grant ALL PRIVILEGES on schema dynamic to dynamic_user;
--给dynamic_user用户在dynamic模式所有表的增删改查权限，他可以建表，删他自己的表，不能删别人建的表