PostgreSQL常用命令-创建数据库、用户、多模式schema、修改用户密码、访问其他模式或在其他模式建表的权限、切换用户、无法删除数据库、导入导出sql、活用set search_path、建表

古城微笑少年丶 2024-03-22 22:11 7阅读 0赞

#### 目录 ####

*  序言
 *  1、创建 PostgreSQL 的数据库命令
 *   *  tips: 使用双引号区分英文大小写名字，单引号隔开SQL命令和内容，SQL 命令英文大小写都可以
 *  2、创建用户命令
 *  3、修改用户密码
 *  4、 创建多模式
 *  5、修改多模式的拥有者
 *  6、分配用户访问其他模式的权限或在其他模式建表的权限
 *   *  6.1 撤销权限命令
     *  多用户多模式权限分配步骤
 *  7、切换数据库并切换用户
 *  8、删除数据库和删除用户
 *   *  8.1、删除数据库时，报错无法删除数据库的问题，会话存在
 *  9、导入/导出 sql 文件命令
 *   *  9.1 导入sql 文件命令
     *  9.2 导出 sql 文件命令
 *  10、活用 set search\_path
 *   *  总结
 *  11、创建表（or 其他模式的表）
 *   *  11.1 模式的使用与创建模式内的表：
     *  11.2 模式的更改：
 *  参考链接

## 序言 ##

下面都以 test 数据库，my\_schema 模式名为例，主用户是 postgres ，新建用户是 admin

测试环境：

Windows 10

## 1、创建 PostgreSQL 的数据库命令 ##

### tips: 使用双引号区分英文大小写名字，单引号隔开SQL命令和内容，SQL 命令英文大小写都可以 ###

小写 t

create database test owner postgres;

大写 T

create database "Test" owner postgres;

test 和 Test 这两个是不同的数据库

`owner` 参数可以指定拥有者（所有者），但如果不指定 `owner` ，那么就会默认当前用户

查看数据库命令：（注意是英文小写的 `l`）

`\l`

## 2、创建用户命令 ##

小写

create user admin with password 'mypassword';

大写

create user "Admin" with password 'mypassword';

查看用户命令：

`\du`

postgres=# \du
                                 角色列表
     角色名称 |                    属性                    | 成员属于
    ----------+--------------------------------------------+----------
     Admin    |                                            | {}
     admin    |                                            | {}
     postgres | 超级用户, 建立角色, 建立 DB, 复制, 绕过RLS | {}

## 3、修改用户密码 ##

alter user admin with password 'mypassword';

## 4、 创建多模式 ##

create schema my_schema;

查看当前的多模式现状命令：

简略信息

`\dn`

postgres=# \dn
       架构模式列表
      名称  |  拥有者
    --------+----------
     admin  | postgres
     public | postgres
    (2 行记录)

详细信息的加 + 号

`\dn+`

## 5、修改多模式的拥有者 ##

ALTER SCHEMA my_schema OWNER TO postgres;

## 6、分配用户访问其他模式的权限或在其他模式建表的权限 ##

比如模式拥有者是 `postgres` 那么需要分配给其他用户用于访问用户 `postgres` 的 `my_schema` 模式内的表，例如用户 `admin` 。

# 重点输入如下命令，修改用户admin权限可以访问 schema 模式 admin
    # 1
    grant all privileges on database "test" to admin;
    # 2
    # 在 my_schema 模式内拥有访问修改权限 U
    grant usage on schema my_schema to admin;
    # 在 my_schema 模式内拥有创建表权限 C
    grant create  on schema my_schema to admin;
    # 3
    grant all privileges on all tables in schema my_schema to admin;

一般来说，只需要配置上面四个个命令，就可以让某个用户拥有访问和某个数据库内的其他模式的权限，以及在其他模式内创建表的权限，比如上面的例子意义是，首先，用户 `admin` 第一步需要拥有访问数据库 `test` 的权限，才能进入数据库 `test` 内部的模式进行访问，然后在赋予用户 `admin` 拥有访问数据库内 `my_schema` 模式的权限，进入数据库 `test` 内部存在的 `my_schema` 模式后，还要赋予用户 `admin` 拥有访问 `my_schema` 模式内部的表。

赋予在某模式下拥有可以修改表和创建表的权限效果演示：

![在这里插入图片描述][56ca1ee704f548978c03627857b2bfff.png]

### 6.1 撤销权限命令 ###

revoke all privileges on schema my_schema from public

这里的 `public` 代指所有的用户（`public`代表了`all users`），指的是所有用户都无法访问 `my_schema` 模式或在 `my_schema` 模式内创建数据库对象（比如建表或修改表访问表等操作）

也可以修改为特定的用户名：（如 admin 用户）

revoke all privileges on schema my_schema from admin

撤销了特定用户的的权限效果演示：

![在这里插入图片描述][83d812315d2844119d37d329c23cb164.png]

### 多用户多模式权限分配步骤 ###

1.  用户 `admin` 第一步需要拥有访问数据库 `test` 的权限（当然这里是包含了在这个数据库内拥有创建、更新‘’删除表等等的权限，访问权限只是其中之一）；
2.  赋予用户 admin 拥有访问数据库内 `my_schema` 模式的权限；
3.  赋予用户 `admin` 拥有访问 `my_schema` 模式内部的表。

> 在 PostgreSQL 中也是一样。您需要模式 `usage` 权限和对象权限才能对对象执行操作，例如表中的 `SELECT`。类似查询语句的权限，比如 Linux 系统访问目录是需要 `rwx` 中的 `x` 权限，`usage` 差不多就是 `x` 权限的意思。

下面的命令笔者感觉不需要，感觉有点重复，如果今后需要用到如下的三条命令，再说。笔者会再次补充。

# 上面3个是重点。
    grant all privileges on all sequences in schema my_schema to admin;
     
    grant select,insert,update,delete on all tables in schema my_schema to admin;
     
    grant all on schema my_schema to admin;

> 权限理解触发截图：
> 
> ![在这里插入图片描述][209877ed5e1b4a689e1d4880381d3672.png]

## 7、切换数据库并切换用户 ##

如下命令，意义是，切换当前数据库，相当于 MySQL 数据库的 `use "Test"` 切换数据库命令，然后后面可以跟着用户名，相当于是切换当前用户，从 `postgres` 用户切换为用户 `admin`

\c "Test" admin

先查看当前用户：`select current_user;`

postgres=# select current_user;
     current_user
    --------------
     postgres
    (1 行记录)

postgres=# \c "Test" admin;
    用户 admin 的口令：
    您现在已经连接到数据库 "Test",用户 "admin".
    Test=> select current_user;
     current_user
    --------------
     admin
    (1 行记录)

## 8、删除数据库和删除用户 ##

删除数据库

drop databse test;

删除用户

drop use admin;

### 8.1、删除数据库时，报错无法删除数据库的问题，会话存在 ###

Postgresql删除数据库失败解决方法

“ERROR: database "dbname" is being accessed by other users
    DETAIL: There are 2 other sessions using the database.”

需断开数据库所有连接执行

输入如下命令，断开 test 数据库的所有连接，注意 `datname ='删除的数据库名'`

SELECT pg_terminate_backend(pg_stat_activity.pid)
    FROM pg_stat_activity
    WHERE datname='test' AND pid<>pg_backend_pid();

如果还是无法删除数据库，请看如下链接：

[关于 PostgreSQL 删除数据库 - 命令行删除，报错数据库不存在，pgadmin 报错存在会话链接 导致无法删除数据库问题][PostgreSQL _ - _pgadmin _]

## 9、导入/导出 sql 文件命令 ##

### 9.1 导入sql 文件命令 ###

psql -h 127.0.0.1 -p 5432 -U postgres -d test -f initData.sql

`-h` 主机ip

`-p` 主机 postgresql 数据库监听的端口号

`-U` 主机 postgresql 数据库的验证用户

`-d` 指定访问的数据库名

`-f` 要导入的`sql` 文件绝对路径或相对路径，看当前位置。

### 9.2 导出 sql 文件命令 ###

pg_dump -U postgres -c test -f /home/test.sql

## 10、活用 set search\_path ##

如果要在 postgresql 数据库中使用这个 `set search_path` 命令，需求是为了查看当前数据库内其他模式内的表。那么首先就得知道 `\d` 或 `\d+` （查看表命令）

可以查看当前的 search\_path 的内容

postgres=# show search_path;
       search_path
    -----------------
     "$user", public
    (1 行记录)

> 第一个值指定一个与当前用户同名的schema被搜索。如果不存在这样的schema，该条目将被忽略。第二个元素指的是我们已经看到的public schema。

search\_path中存在的第一个schema是创建新对象的默认位置。这就是默认情况下，对象是在public schema中创建的原因。当对象在没有schema限定的情况下被引用时（表修改、数据修改或查询命令），search\_path被遍历，直到找到一个匹配的对象。因此，在默认配置中，任何未经限定的访问又只能引用public schema。

设置参数  
为了把我们的新schema放在路径中，我们使用：

SET search_path TO myschema,public;

我们在这里省略了`$user`，因为我们没有立即需要它，然后我们可以在没有schema限定的情况下访问该表。

DROP TABLE mytable

另外，由于myschema是路径中的第一个元素，新的对象将默认在其中创建。

我们也可以这样写。

SET search_path TO myschema

然后我们就不再能够在没有明确限定的情况下访问public schema了。除了默认存在之外，public schema并没有什么特别之处。它也可以被放弃。

参数理解：

"$user" :这是指名称为当前用户的模式。
    
    'public': 这是指所有用户都可以访问的默认模式。

### 总结 ###

总之，就是说假如除了默认的模式 `public` ，还存在一个新的模式，叫做 `my_schema`，那么按照原来的命令 \\d 或 \\d+ 命令，是无法查询其他模式下的表的，所以需要配置 set search\_path 将查询指向另外的一个新模式，上面的说法，有些繁琐，直接上例子。

演示：

`database`：`test`  
`schema`：`public`、`my_schema`

默认情况：

postgres=# show search_path;
       search_path
    -----------------
     "$user", public
    (1 行记录)

修改后情况：`set search_path to my_schema,public;`

postgres=# set search_path to my_schema,public;
    SET
    postgres=# show search_path;
      search_path
    ---------------
     my_schema, public
    (1 行记录)

然后就可以使用命令 `\d` 或 `\d+` 查询到新模式 `my_schema` 和 `public` 模式内的表情况了

## 11、创建表（or 其他模式的表） ##

### 11.1 模式的使用与创建模式内的表： ###

创建表时在表明前面添加模式名，若不指定模式名，默认使用search\_path第一个模式创建数据表，命令：  
`create table 模式名.表名 (列名 数据类型);`

eg：创建两个数据表，`testtable1`使用默认模式名创建，`testtable2` 指定模式名创建

1.  默认是 public 模式，所以是在 public 模式内，创建表 `testtable1`
2.  如果是指定模式，自然就不是在 public 模式下创建表 `testtable2`

### 11.2 模式的更改： ###

a)重命名：`alter schema 旧名字 to 新名字;`  
b)修改模式的归属用户：`alter schema 模式名 to 新用户;`  
模式的删除：`drop schema 模式名;`

--------------------

## 参考链接 ##

[1. 在PostgreSQL中使用SET search\_path有什么用？][1. _PostgreSQL_SET search_path]

[2. PostgreSQL\_通过schema控制用户权限][2. PostgreSQL_schema]

[3. postgres中schema访问权限设置][3. postgres_schema]

[4. postgres 基于Schema 权限访问探讨][4. postgres _Schema]

[5. postgresql数据库中多个Schemas互相访问][5. postgresql_Schemas]

[6. Postgres ： 创建schema、创建表空间与指定用户权限][6. Postgres _ _schema]

[7. 为什么POSTGRESQL的\\ DT只显示公共模式表？][7. _POSTGRESQL_ DT]

[8. 第三节——模式][8.]

[9. PostgreSQL中search\_path参数解密][9. PostgreSQL_search_path]

[10. 关于数据库：SCHANT上的GRANT USAGE到底能做什么？][10. _SCHANT_GRANT USAGE]

[11. linux查看文件权限命令 查看文件权限 linux ][11. linux_ _ linux]

[12. PostgreSQL-表空间、数据库、模式、角色实践][12. PostgreSQL-]

[13. postgreSQL数据库基本操作和导入导出数据-命令行][13. postgreSQL_-]

[14. PostgreSQL如何导出数据库文件][14. PostgreSQL]

[56ca1ee704f548978c03627857b2bfff.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/c645be99487c42daa1343ca49222fea9.png
[83d812315d2844119d37d329c23cb164.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/762bb718f98842e6ac191adf6b51189d.png
[209877ed5e1b4a689e1d4880381d3672.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/14/73c22d32d1cd40e88f99846987e4dd6e.png
[PostgreSQL _ - _pgadmin _]: https://blog.csdn.net/qq_42701659/article/details/131810749
[1. _PostgreSQL_SET search_path]: https://www.saoniuhuo.com/question/detail-2661529.html
[2. PostgreSQL_schema]: https://www.swvq.com/boutique/detail/54194
[3. postgres_schema]: https://code84.com/356060.html
[4. postgres _Schema]: https://www.likecs.com/show-307416882.html
[5. postgresql_Schemas]: https://blog.csdn.net/dengzhi9024/article/details/102232136/
[6. Postgres _ _schema]: http://wed.xjx100.cn/news/175970.html?action=onClick
[7. _POSTGRESQL_ DT]: https://www.dovov.com/postgresql-dt.html
[8.]: https://blog.csdn.net/qq_37589805/article/details/118889998
[9. PostgreSQL_search_path]: https://www.modb.pro/db/236038
[10. _SCHANT_GRANT USAGE]: https://www.codenong.com/17338621/
[11. linux_ _ linux]: http://www.bxcqd.com/news/544923.html
[12. PostgreSQL-]: https://blog.csdn.net/mymhj/article/details/108258192
[13. postgreSQL_-]: https://www.modb.pro/db/655220
[14. PostgreSQL]: https://www.python51.com/postgresql/70610.html