PostgreSQL创建表详细步骤

使用的PostgreSQL版本

postgres=# show server_version;
            server_version             
---------------------------------------
 16.10 (Ubuntu 16.10-0ubuntu0.24.04.1)
(1 row)

执行目标

• 第一步：构建一个用户
• 第二步：构建一个数据库
• 第三步：在这个数据库下构建一个schema（数据库默认有一个public的schema,我们不用这个默认的，新建一个其他的）
• 第四步：将这个schema的权限赋予新创建的用户
• 第五步：在这个schema下构建一个表
• 第六步：给用户赋予对这个表的select，update,insert权限

理清关系

在创建表之前我们先理清一下PostgreSQL各个名词之间的关系。

                        cluser(instance)
                                |
        database1           database2          databasen
            |                   |                   |
    schema1   scheman    schema1   scheman  schema1   scheman        

详细步骤

第一步：创建用户

postgres=# CREATE USER postkarte WITH PASSWORD '123456';
CREATE ROLE

第二步：创建一个数据库

postgres=# CREATE DATABASE postkarte_db OWNER postkarte;
CREATE DATABASE

第三、四步：创建一个schema

连接到新的数据库上

postgres=# \c postkarte_db;
You are now connected to database "postkarte_db" as user "postgres"

创建schema

# 创建schema

postkarte_db=# CREATE SCHEMA postkarte_schema;
CREATE SCHEMA

# 查看当前数据库下的schema，可以看到当前schema拥有者为postgres

postkarte_db=# \dn
           List of schemas
       Name       |       Owner       
------------------+-------------------
 postkarte_schema | postgres
 public           | pg_database_owner
(2 rows)

# 修改schema拥有者

postkarte_db=# ALTER SCHEMA postkarte_schema OWNER TO postkarte;
ALTER SCHEMA

# 再次查看下schema

postkarte_db=# \dn
           List of schemas
       Name       |       Owner       
------------------+-------------------
 postkarte_schema | postkarte
 public           | pg_database_owner
(2 rows)

第五步：在这个schema下构建一个表

postkarte_db=# CREATE TABLE postkarte_schema.test2(id INT);
CREATE TABLE

第六步： 给表授予权限

将schema下所有表的select,insert,update权限都赋予给了postkarte用户

postkarte_db=# GRANT SELECT,INSERT,UPDATE ON ALL TABLES IN SCHEMA postkarte_schema TO postkarte;
GRANT

测试

连接数据库

root@node1:~# psql -h 192.168.0.24 -U postkarte -d postkarte_db -W
Password: 
psql (16.10 (Ubuntu 16.10-0ubuntu0.24.04.1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postkarte_db=>

查看schema

postkarte_db=> \dn
           List of schemas
       Name       |       Owner       
------------------+-------------------
 postkarte_schema | postkarte
 public           | pg_database_owner
(2 rows)

查看表, postkarte_schema下有一个属于postgres用户的test2表

postkarte_db=> \dt postkarte_schema.*
              List of relations
      Schema      | Name  | Type  |  Owner   
------------------+-------+-------+----------
 postkarte_schema | test2 | table | postgres
(1 row)

往表中插入数据

postkarte_db=> INSERT INTO postkarte_schema.test2 (id) VALUES (1);
INSERT 0 1

删除数据,删除失败，因为我们没有将表的DELETE权限授予用户

postkarte_db=> DELETE FROM postkarte_schema.test2 WHERE id = 1;
ERROR:  permission denied for table test2