目录

• 序言
• 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

              postgres=# \l
                       数据库列表
                 名称    |  拥有者  | 字元编码 |            校对规则            |             Ctype              |       存取权限     
              -----------+----------+----------+--------------------------------+--------------------------------+-----------------------
               Test      | postgres | UTF8     | Chinese (Simplified)_China.936 | Chinese (Simplified)_China.936 |
              

              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+

              postgres=# \dn+
                                         架构模式列表
                名称  |  拥有者  |       存取权限       |          描述
              --------+----------+----------------------+------------------------
               admin  | postgres | postgres=UC/postgres+|
                      |          | admin=U/postgres     |
               public | postgres | postgres=UC/postgres+| standard public schema
                      |          | =UC/postgres         |
              (2 行记录)
              

              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 模式内部的表。

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

              

              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
              

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

              

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

              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;
              

              权限理解触发截图：

              

              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 报错存在会话链接 导致无法删除数据库问题

              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有什么用？

              2. PostgreSQL_通过schema控制用户权限

              3. postgres中schema访问权限设置

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

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

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

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

              8. 第三节——模式

              9. PostgreSQL中search_path参数解密

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

              11. linux查看文件权限命令 查看文件权限 linux

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

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

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

               
               免费的企业邮箱有哪些  重庆网站定制  网站建设的方案实施包括  公众号运营  茂名专业网站建设公司  回龙观网站建设