【postgresql 基础入门】创建数据库的方法，存储位置，决定自己的数据的访问用户和范围

创建数据库

​专栏内容：

• postgresql内核源码分析
• 手写数据库toadb
• 并发编程

​开源贡献：

• toadb开源库

个人主页：我的主页
管理社区：开源数据库
座右铭：天行健，君子以自强不息；地势坤，君子以厚德载物.

系列文章

• 入门准备
• postgrersql基础架构
• 快速使用
• 初始化集群
• 数据库服务管理
• psql客户端使用
• 创建数据库
• 数据库操作

前言

postgresql 数据库是一款通用的关系型数据，在开源数据库中能与商业数据媲美，在业界也越来越流行。

因为是开源数据库，不仅公开源码，还有很多使用案例，好用的插件，所以它的慢慢变成了数据库的先驱和标准，通过postgresql可以很好从使用到原理，彻底搞懂；

如果是学习编程，也可以学到丰富的编程知识，数据结构，编程技巧，它里面还有很多精妙的架构设计，分层思想，可以灵活定制的思想。

本专栏主要介绍postgresql 入门使用，数据库维护管理，通过这些使用来了解数据库原理，慢慢了解postgresql是什么样的数据库，能做那些事情，以及如何做好服务，最关键的是这些知识都是面试的必备项。

概述

本文主要分享PostgreSQL的基础的架构，方便我们对对PostgreSQL的部件之间如何相互作用的理解将会使本节更易理解，在我们使用的时候，不至于与其它类型的数据库产生混淆；

概述

使用数据库服务的前提是，需要指定一个database对象，这个database是其它数据库对象的载体，或者说是上级名命空间。
本文来分享一下创建数据库的几种方法，给数据库赋予用户使用的权限；

原理介绍

大家在想，创建数据库很简单，一条SQL语句就可以了，还需要讲原理吗？

那我们来看这几个问题？

• 数据库存储的位置在那里;
• 数据库的所有者是谁；
• 其它用户如何来访问这个数据库；

看起来还是不简单，数据库也是有存储位置的，它其实也是一个目录，它下面可以存储其它的数据库对象，如该数据库中的表对象；

当然，数据库作为一个各类数据库汇集存储的管理系统，不是所有用户都可以任意访问和操作的，这就有了数据库的所有者，所有者具有该数据库的所有权限，类似于这个数据库的一个管理员；

同样，数据库中有很多用户，它们也是不可以随意访问和操作的，需要管理员给他们分配权限，不符合权限的访问会被拒绝，就如同宿合管理，女生宿舍是不允许男生进入的。

现在通过演示来解答上述问题, 大概流程是这样的：

1. 登录到数据库 ；
2. 执行创建数据库的SQL；
3. 创建新的用户；
4. 将新数据库的使用权限授予新用户 ；
5. 新用户登录到新数据库；

创建数据库

这一步的前提是，数据库服务已经启动，当前用户具有创建数据库的权限，一般刚开始我们都使用超级管理员来创建第一个自定义的数据库；

新数据库将通过复制模版数据库template1来创建，也就是说创建数据库，其实就是复制了一份template1模版库，而模版库是在集群初始化时就预定义好的；
在postgresql 中每个数据库对象都有一个唯一的标识，叫做OID，新数据库也会分配一个OID；

创建数据库常见方法：

• 在图形化客户端创建，通过菜单创建数据库；
• 通过命令或SQL来创建，本文也是分享这种方式，因为它简单直接，而且不会随着UI变动而发生不同；

本文介绍通过命令和SQL来创建 ，图形化相对简单；

在通过创建之前，我们需要准备登录信息，因为不管是那种创建方式，都是需要能够登录到数据库系统中，最终还是转化为SQL的执行。
通过上篇分享，在刚初始化的集群中，默认有一个数据库 postgres，有一个超级管理员用户，默为当前系统的用户 senllang (根据自已的情况修改) ，监听IP为 localhost, 端口5432；

有了这些信息，我们就可以开始创建新的数据库了；

SQL创建

• 登录数据库系统

[senllang@hatch bin]$ ./psql -d postgres
psql (16beta1)
Type "help" for help.postgres=#

这里的用户名，IP，端口都采用默认值，只指定了数据库名；

• 创建数据库
  在命令行中输入以下命令：

postgres=# CREATE DATABASE db_factory;
CREATE DATABASE

在上述命令中，将 db_factory 替换为你希望创建的数据库名称。

通过 \l 命令就可以看到数据库的列表

postgres=# \lList of databasesName    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |   Access privileges
------------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------db_factory | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           |postgres   | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           |template0  | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           | =c/senllang          +|          |          |                 |             |             |            |           | senllang=CTc/senllangtemplate1  | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           | =c/senllang          +|          |          |                 |             |             |            |           | senllang=CTc/senllang
(4 rows)

刚才创建的数据库 db_factory 的 Owner就是创建者用户，默认owner拥有该数据库的所有权限，所有Access privileges 一栏没有信息；

客户端命令创建

在安装目录下的bin目录中，有一个工具 createdb ，看名称我们就知道它就是创建数据库专用工具了；
先来看下它的帮助信息

[senllang@hatch bin]$ ./createdb --help
createdb creates a PostgreSQL database.Usage:createdb [OPTION]... [DBNAME] [DESCRIPTION]Options:-D, --tablespace=TABLESPACE  default tablespace for the database-e, --echo                   show the commands being sent to the server-E, --encoding=ENCODING      encoding for the database-l, --locale=LOCALE          locale settings for the database--lc-collate=LOCALE      LC_COLLATE setting for the database--lc-ctype=LOCALE        LC_CTYPE setting for the database--icu-locale=LOCALE      ICU locale setting for the database--icu-rules=RULES        ICU rules setting for the database--locale-provider={libc|icu}locale provider for the database's default collation-O, --owner=OWNER            database user to own the new database-S, --strategy=STRATEGY      database creation strategy wal_log or file_copy-T, --template=TEMPLATE      template database to copy-V, --version                output version information, then exit-?, --help                   show this help, then exitConnection options:-h, --host=HOSTNAME          database server host or socket directory-p, --port=PORT              database server port-U, --username=USERNAME      user name to connect as-w, --no-password            never prompt for password-W, --password               force password prompt--maintenance-db=DBNAME      alternate maintenance databaseBy default, a database with the same name as the current user is created.Report bugs to <[e-mail]>.
PostgreSQL home page: <https://www.postgresql.org/>

可以看到，使用时，如果是默认ip,port，用户时，直接创建新的数据库即可；
我们再创建一个 db_factory1的数据库

[senllang@hatch bin]$ ./createdb db_factory1

postgres=# \lList of databasesName     |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |   Access privileges
-------------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------db_factory  | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           |db_factory1 | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           |postgres    | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           |template0   | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           | =c/senllang          +|          |          |                 |             |             |            |           | senllang=CTc/senllangtemplate1   | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           | =c/senllang          +|          |          |                 |             |             |            |           | senllang=CTc/senllang
(5 rows)

通过查询，可以看到创建成功了，其它信息与第一种方法创建的一样；

以上两种方法都能成功创建 PostgreSQL 数据库，你可以根据个人喜好和使用环境选择适合你的方法。

存储位置

数据库存储在那里呢，前面博客介绍了数据库的逻辑架构，其中有说存储层次和命名空间，默认情况下，存储在默认的表空间pg_default，它的位置就是当前集群目录下的base目录；如果指定了表空间，那就是表空间对应的目录下了；

我们来证实一下，在集群目录下存储的目录和文件都是以OID来命名的，所以需要查看当前数据库的OID；

postgres=# select * from pg_tablespace ;oid  |   spcname    | spcowner | spcacl | spcoptions
-------+--------------+----------+--------+------------1663 | pg_default   |       10 |        |1664 | pg_global    |       10 |        |16394 | tblspc_test1 |       10 |        |
(3 rows)
postgres=# select oid, datname, dattablespace from pg_database ;oid  |   datname   | dattablespace
-------+-------------+---------------5 | postgres    |          16631 | template1   |          16634 | template0   |          166316445 | db_factory  |          166316446 | db_factory1 |          1663
(5 rows)

在系统表中记录了数据的组织形式，也可以叫它们为数据字典；
在pg_tablespace 中记录了表空间的信息，名称和对应的OID；
在pg_database 中记录了数据库的信息，名称和对应的OID，还有存储对应的表空间的OID；可以看到我们创建的两个factory的数据库，OID分别是 16445和16446，对应的表空间为 1663，也就是pg_default，即默认表空间的存储位置；

在系统内部所有数据库对象的使用都是通过OID来标识和引用，很多时间我们需要通过OID来找到它对应的名称；

接下来找到默认表空间的位置，它位于集群目录testdemo1下，对应的就是base目录；

[senllang@hatch bin]$ cd testdemo1/base/
[senllang@hatch base]$ ls -l
total 60
drwx------. 2 senllang develops 8192 Sep  3 09:40 1
drwx------. 2 senllang develops 8192 Sep  7 08:27 16445
drwx------. 2 senllang develops 8192 Sep  7 08:32 16446
drwx------. 2 senllang develops 8192 Sep  2 22:02 4
drwx------. 2 senllang develops 8192 Sep  6 13:57 5
-- 集群目录的路径，根据自己的实际位置查找 
[senllang@hatch base]$ pwd
/opt/postgres/bin/testdemo1/base

可以看到base 目录下有五个文件夹，都是以数字命名，正好对应了数据库列表中的OID；新创建的两个数据库 16445，16446也在这里，而且它们下面已经有很多文件了，是从模版数据库复制过来的；

所有者

默认的owner用户为创建者，也可以在创建时指定，当然也可以在创建后修改。

owner在权限管理中有非常关键的作用，它相当于该数据库对象的管理者；

在 PostgreSQL 中，数据库对象的 owner 具有以下作用：

1. 权限控制：数据库对象的 owner 具有对对象的完全控制权限。他们可以执行任何与该对象相关的操作，包括读取、修改、删除等。其他用户则需要通过 owner 授予的权限才能对对象进行操作。
2. 对象的访问：数据库对象的 owner 可以决定哪些用户可以访问该对象。通过授予不同的权限级别，owner 可以控制用户对对象的读取、修改和删除等操作。
3. 对象的所有权：数据库对象的 owner 是该对象的所有者，他们可以对该对象进行任何操作。所有权可以转移给其他用户，但只有 owner 可以回收所有权。
4. 对象的依赖关系：有些数据库对象（如视图、触发器等）依赖于其他对象，当这些依赖对象被删除或修改时，owner 可以决定是否允许删除或修改操作。

访问权限

数据库系统平常使用时，一般不会用超级管理员用户，只有在特殊场景时才会用超级管理员。
我们创建一个新用户 manager 成为 数据库的db_factory所有者，由它来全权负责，给其它使用者分配权限；
再创建一个 hr 的用户，它可以使用数据库，比如创建表等，但不能对数据库进行修改；

创建普通用户

新建两个普通用户，指定密码为123456；

postgres=# create user manager with password '123456';
CREATE ROLE
postgres=# create user hr with password '123456';
CREATE ROLE

这里有个细节，最后输出是 CREATE ROLE ，明明创建的是 create user，这个到用户权限章节再给大家分享；

修改所有者

将超级管理员的职责归位，用户的数据库应该由各自的管理员负责，所以这里将数据库 db_factory 的管理权限交给 manager用户。

当然这个命令是非常危险的，最好在一开始创建时就分配好owner。

postgres=# alter database db_factory owner to manager ;
ALTER DATABASE
db_factory=> \lList of databasesName     |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |   Access privileges
-------------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------db_factory  | manager  | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           | =Tc/manager          +|          |          |                 |             |             |            |           | manager=CTc/manager  +|          |          |                 |             |             |            |           | hr=CTc/managerdb_factory1 | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           |postgres    | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           |template0   | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           | =c/senllang          +|          |          |                 |             |             |            |           | senllang=CTc/senllangtemplate1   | senllang | UTF8     | icu             | en_US.UTF-8 | en_US.UTF-8 | en-US      |           | =c/senllang          +|          |          |                 |             |             |            |           | senllang=CTc/senllang
(5 rows)

通过查看，db_factory 的owner确实变为了 manager用户，Access privileges字段中也有了权限信息；

有人要说了，为什么这么麻烦呢？ 举个例子，这个数据库系统被三个业务共同使用，它们对应三个数据库，如果都由超级管理员用户管理，那每天都会吵架 “谁又动了我的库“，哈哈~

授予访问权限

用普通用户 manager 登录到 数据库 db_factory；可以退出 \q 之后重新登录，也可以用之前分享的方法 \c进行切换数据库和用户；

给 hr 用户授予在当前数据库中的create 权限；

postgres=# \c db_factory manager
You are now connected to database "db_factory" as user "manager".db_factory=> grant ALL ON database db_factory TO hr ;
GRANT

用普通用户 hr 登录到 数据库 db_factory； 尝试创建表

db_factory=> \c db_factory hr
You are now connected to database "db_factory" as user "hr".db_factory=> CREATE TABLE employees (
db_factory(>   employee_id SERIAL PRIMARY KEY,
db_factory(>   name VARCHAR(100),
db_factory(>   department VARCHAR(100),
db_factory(>   position VARCHAR(100),
db_factory(>   hire_date DATE,
db_factory(>   salary NUMERIC(10, 2)
db_factory(> );
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE employees (

居然失败了，不过从报错信息，看出来对于默认的schema，也就是public没有权限；通过前面命名空间的介绍，我们知道有在database之下还有schema，表必须在某个schema中；

前面授予了database下的create权限，但是没有schema的权限；
下面授予schema下的create权限；

postgres=# \c db_factory manager
You are now connected to database "db_factory" as user "manager".db_factory=> grant create on schema public  TO hr;
GRANT

新数据库中创建表

用普通用户 hr 登录到 数据库 db_factory；

db_factory=> \c db_factory hr
You are now connected to database "db_factory" as user "hr".
db_factory=> CREATE TABLE employees (employee_id SERIAL PRIMARY KEY,name VARCHAR(100),department VARCHAR(100),position VARCHAR(100),hire_date DATE,salary NUMERIC(10, 2)
);
CREATE TABLE
db_factory=> \dList of relationsSchema |           Name            |   Type   | Owner
--------+---------------------------+----------+-------public | employees                 | table    | hrpublic | employees_employee_id_seq | sequence | hr
(2 rows)

创建雇员表成功，它的owner就是创建者hr，所以hr用户拥有该表的所有权限；这样就是一种分层管理的策略，符合现实中的人员管理层级关系。

结尾

非常感谢大家的支持，在浏览的同时别忘了留下您宝贵的评论，如果觉得值得鼓励，请点赞，收藏，我会更加努力！

作者邮箱：[e-mail]
如有错误或者疏漏欢迎指出，互相学习。

注：未经同意，不得转载！