PostgreSQL数据库管理：使用psql创建用户和数据库操作

本文还有配套的精品资源，点击获取

简介：本文详细介绍在PostgreSQL中使用psql命令行工具来创建用户、数据库以及执行管理操作。包括psql工具的安装与启动、创建新用户（角色）、数据库的创建和配置、权限的授予与管理、角色管理以及一些实用的psql命令。这些操作对于数据库的日常管理工作至关重要，熟练掌握它们能够提升工作效率并确保数据库的安全性。

1. psql工具安装与启动

在进行PostgreSQL数据库管理之前，首先需要安装并掌握如何启动psql命令行工具。psql是PostgreSQL数据库的官方命令行界面工具，它为数据库管理员和开发者提供了一个强大的接口来执行查询、管理数据库以及执行各种维护任务。

安装psql工具

对于大多数操作系统，可以通过包管理器安装psql。例如，在基于Debian的Linux系统中，可以使用以下命令安装：

sudo apt-get update
sudo apt-get install postgresql-client

在基于Red Hat的系统中，使用：

sudo yum install postgresql-contrib

安装完成后，确保psql命令已经正确添加到了系统的PATH环境变量中。

启动psql

要启动psql，你可以在命令行界面中输入 psql 然后按回车键。如果你是在Windows系统上操作，你也可以通过开始菜单找到psql图标并启动它。

启动psql后，你可能会看到如下欢迎信息，以及一个提示符，等待你输入SQL命令：

psql (13.0 (Ubuntu 13.0-1.pgdg18.04+1))
Type "help" for help.

postgres=#

在这里， postgres=# 表示当前连接到了名为 postgres 的数据库，并且以PostgreSQL的超级用户身份登录。

以上就是psql工具的安装与启动过程，接下来的章节我们将深入学习使用psql创建新用户、数据库，以及权限管理等高级功能。

2. 使用psql创建新用户（角色）

PostgreSQL中的角色概念

角色与用户的关系

在 PostgreSQL 中，”角色” 是一个通用术语，用来指代可以登录数据库系统的实体，以及这些实体拥有的权限。从这个意义上讲，角色可以被看作是用户或用户组的等价物。PostgreSQL 角色的灵活性在于它允许我们创建角色，可以将这些角色赋给用户或分配给其他角色，甚至可以为角色设置权限，以决定它们可以对数据库做什么或不能做什么。

角色系统的设计意图是为了简化权限管理，允许管理员通过角色而不是为每个单独的数据库用户设置权限。这意味着，当有新用户需要访问数据库时，不必为他们单独设置权限，而是可以将他们分配给一个已存在的角色。

角色的权限和作用域

角色拥有不同的权限和作用域，权限决定了角色可以在数据库系统中执行哪些操作。例如，有的角色可以创建数据库，有的角色可以管理其他角色的权限，还有角色可能只能读取特定表的数据。角色的权限通常分为以下几类：

• 登录 ：允许角色登录到数据库系统。
• 创建数据库 ：允许角色创建新的数据库。
• 创建角色 ：允许角色创建新的角色。
• 超级用户 ：拥有对数据库系统的最高控制权。

一个角色可以拥有以上一种或多种权限，从而决定了角色可以执行的操作范围。作用域则是角色权限所影响的数据库或表的范围。角色的权限作用域可以限定于特定的数据库，也可以是整个数据库集群。

创建新用户的命令行操作

创建新用户的语法

要创建一个新的用户（角色），我们可以使用 PostgreSQL 的 CREATE ROLE 命令。基本语法如下：

CREATE ROLE username [OPTIONS];

在这里， username 是你想要创建的角色名称。 OPTIONS 是一系列关键字和赋值对，用来定义角色的属性和权限。下面是一些常用的选项：

• LOGIN ：创建一个可以登录的用户。
• PASSWORD 'password' ：为角色设置密码。
• SUPERUSER 或 NOSUPERUSER ：创建一个超级用户。
• CREATEDB 或 NOCREATEDB ：是否赋予创建数据库的权限。
• CREATEROLE 或 NOCREATEROLE ：是否赋予创建其他角色的权限。

设置用户密码和登录权限

创建一个具有登录权限的角色，并为其设置密码的例子：

CREATE ROLE new_user WITH LOGIN PASSWORD 'new_user_password';

在这个例子中， new_user 是新创建的角色名称，我们通过 WITH LOGIN 选项明确指定了这个角色具有登录权限，通过 PASSWORD 关键字后跟密码值，为角色设置了密码。设置密码是一个非常重要的安全措施，可以防止未授权的访问。

配置用户其他属性

除了设置登录权限和密码之外，我们还可以配置其他属性，例如角色的创建日期、过期日期等。以下是一些可以设置的属性：

ALTER ROLE new_user CREATEDB CREATEROLE INHERIT NOBYPASSRLS VALID UNTIL 'YYYY-MM-DD HH:MM:SS';

在上面的命令中：

• CREATEDB 和 CREATEROLE 允许角色创建数据库和角色。
• INHERIT 表示该角色会自动继承其父角色的权限。
• NOBYPASSRLS 指定角色在使用行级安全性策略时不能绕过。
• VALID UNTIL 允许设置角色密码到期时间。

请注意，角色和用户的属性可以随时通过 ALTER ROLE 命令进行修改。

在此章节中，我们深入探讨了 PostgreSQL 中的角色概念以及如何通过 psql 命令行创建新用户，包括了创建新用户的语法、如何设置用户密码和登录权限以及配置用户其他属性。以上操作步骤为数据库管理员提供了创建新用户角色的详细指导，确保了执行过程的准确性和安全性。

3. 使用psql创建数据库及其配置

3.1 数据库创建的基础知识

3.1.1 数据库的定义和重要性

数据库是存储、管理、处理和检索数据的结构化系统。在信息技术中，数据库扮演着中心角色，用于组织和存储大量数据，使得数据的查询、更新和维护变得更加高效。数据库管理系统（DBMS）则提供了访问和操作数据库中的数据的接口，使得用户无需直接处理物理存储细节。

PostgreSQL是一个功能强大的开源对象关系数据库系统，它在数据完整性和安全性方面提供了高级特性。一个良好的数据库设计可促进数据的合理存储和管理，进而支持高效的数据访问模式，确保应用的稳定性和扩展性。

3.1.2 PostgreSQL中的模式概念

在PostgreSQL中，模式(schema)是一个命名空间，它包含了一系列的数据库对象，如表、视图、索引和序列等。一个数据库可以包含多个模式，而每个模式可以包含各自独立的对象。模式在数据库中扮演着组织数据库对象的角色，可以将不同的数据和操作功能分组。

使用模式的好处包括：

• 逻辑分组 ：将相关的数据库对象组织在一起，使得数据库的结构更加清晰。
• 权限控制 ：对模式的访问权限可以单独设置，方便管理不同用户对数据库对象的访问。
• 避免命名冲突 ：不同的模式可以包含同名的对象，这对于大型应用中的模块化设计非常有帮助。

3.2 创建数据库的命令和参数

3.2.1 创建数据库的基本命令

PostgreSQL使用 CREATE DATABASE 命令创建新数据库。语法结构如下：

CREATE DATABASE db_name
    [ [ WITH ] [ OWNER [=] db_owner ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LOCALE [=] locale ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ] ];

参数说明：

• db_name ：新数据库的名称。
• db_owner ：创建数据库的用户，默认为当前用户。
• template ：新数据库将基于哪个模板创建，默认为 template1 。
• encoding ：数据库字符编码，默认通常是 UTF-8 。
• locale 和 lc_collate 、 lc_ctype ：数据库的本地化设置。
• tablespace_name ：新数据库将使用的表空间，默认使用模板数据库的表空间。
• allowconn ：是否允许连接，默认为 true 。
• connlimit ：数据库的最大连接数。
• istemplate ：是否可以基于此数据库创建新数据库，默认为 false 。

3.2.2 配置字符集和排序规则

在创建数据库时，字符集和排序规则是重要的配置项，它们影响数据库中数据的编码和比较方式。

字符集定义了字符和字节之间的映射关系。对于大多数应用，使用 UTF-8 编码可以提供全球语言支持。

排序规则定义了数据的排序和比较规则，这通常包括字母、数字和其他字符的排序方式。PostgreSQL提供了多种预定义的本地化设置，如 en_US.UTF-8 等。

以下是一个配置字符集和排序规则的示例：

CREATE DATABASE mydb
    WITH 
    OWNER = myuser
    ENCODING = 'UTF-8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TABLESPACE = mytablespace
    CONNECTION LIMIT = -1;

3.2.3 设置默认模板和表空间

表空间是存储数据库对象的地方。通过设置默认表空间，可以控制数据库对象的物理存储位置，这有助于管理大型数据库的性能和存储。

模板数据库是创建新数据库时的模板。PostgreSQL提供了一些预定义的模板，如 template0 和 template1 ，它们具有不同的默认设置。使用 template1 时，它通常包含一些示例数据和函数，而 template0 则提供一个干净的环境，不包含这些额外的元素。

设置模板和表空间的示例命令如下：

CREATE DATABASE mydb
    WITH 
    TEMPLATE = template0
    TABLESPACE = mytablespace;

以上命令创建了一个名为 mydb 的数据库，它基于 template0 模板，并使用名为 mytablespace 的表空间。这样可以确保 mydb 数据库在创建时具有预定义的结构和独立的存储位置，有助于数据的管理与优化。

请注意，创建表空间和设置字符集、排序规则都是在创建数据库时需要仔细考虑的配置，因为它们影响着数据库的性能和数据的一致性。选择合适的设置，可确保数据库在运行期间具有最佳的性能表现和稳定性。

4. 数据库和表的权限管理

数据库和表的权限管理是保证数据安全和控制数据访问的关键环节。本章首先介绍权限管理的基本概念，包括权限的种类、作用以及角色与权限的关联。随后，我们将深入实践操作，探讨如何在PostgreSQL中通过命令行进行授权和撤销权限，以及如何针对具体的数据库对象进行权限管理。

4.1 权限管理的基本概念

权限管理是数据库管理的一个核心方面，涉及到数据访问的安全性和完整性。理解权限管理的基本概念是进行有效权限设置的前提。

4.1.1 权限的种类和作用

在PostgreSQL中，权限可以分为多种类型，主要包括：

• SELECT ：允许从数据库表中读取数据。
• INSERT ：允许向表中插入新的数据行。
• UPDATE ：允许更新表中的数据行。
• DELETE ：允许从表中删除数据行。
• TRUNCATE ：允许清空表中的所有数据。
• REFERENCES ：允许创建外键约束。
• CREATE ：允许在模式中创建新的数据库对象。
• ALTER ：允许修改已存在的数据库对象。
• DROP ：允许删除数据库对象。
• EXECUTE ：允许执行函数或者程序。
• USAGE ：特定类型的权限，例如用于模式、序列等。

每种权限都有其特定的作用，它们可以单独授予，也可以组合成角色分配给用户或角色，从而简化权限管理过程。

4.1.2 角色与权限的关联

在PostgreSQL中，角色是权限管理的基本单元。一个角色可以是一个用户，也可以是一个组，或者是一系列权限的集合。通过角色，可以将权限分配给一个单独的用户，或者分配给一组用户。

角色可以拥有其他角色，这样的结构形成了权限的层次体系。如果一个角色被授予了另一个角色，那么它也会拥有那个角色所拥有的所有权限。通过角色的继承，管理员可以方便地管理复杂权限结构中的权限分配。

4.2 实践中的权限设置

在理论知识基础上，我们需要掌握如何在实际工作中通过命令行操作来设置权限。

4.2.1 授权和撤销权限的命令

在PostgreSQL中，使用 GRANT 命令来授权。一个基本的 GRANT 命令格式如下：

GRANT permission_type ON database_object TO role;

• permission_type 是要授予权限的类型，可以是前面提到的任一权限。
• database_object 是权限应用于的对象，如表、视图或数据库。
• role 是被授权的角色名称。

撤销权限使用 REVOKE 命令，格式与 GRANT 类似：

REVOKE permission_type ON database_object FROM role;

4.2.2 针对数据库对象的权限管理

对于具体数据库对象，如表、视图等，我们可以授予权限以控制对这些对象的访问。例如，授权给 reader_role 角色从 sales_table 表中读取数据：

GRANT SELECT ON sales_table TO reader_role;

如果要撤销该角色的权限，可以执行：

REVOKE SELECT ON sales_table FROM reader_role;

4.2.3 权限的继承和传递

在PostgreSQL中，角色可以继承其他角色的权限，这是一个非常强大的特性，可以帮助我们维护复杂权限结构。

要让 reader_role 角色继承自 employee_role 角色，我们需要使用 INHERITS 关键字创建角色：

CREATE ROLE reader_role INHERITS employee_role;

此时， reader_role 将拥有 employee_role 的所有权限。如果 employee_role 的权限发生了变化， reader_role 的权限也会相应更新。

4.3 权限管理的高级技巧

在实际使用中，我们还可能需要设置一些更加复杂和高级的权限管理策略，比如限制访问时间和IP地址等。

4.3.1 设置时间限制的权限

我们可以通过创建自定义函数来实现基于时间的权限检查，然后授予或撤销角色基于时间的访问权限。例如，我们可以创建一个名为 check_time 的函数，根据当前时间授予或撤销特定权限。

4.3.2 IP限制的权限

PostgreSQL本身不直接支持IP地址限制，但我们可以通过外部工具或配置来实现。例如，使用防火墙规则或通过网络认证插件来限制特定IP地址的访问。

4.3.3 角色成员管理

角色成员管理是通过角色的层次结构来简化权限分配的有效方式。例如，管理员可以创建一个 manager_role 角色，然后将 employee_role 和 reader_role 角色作为成员加入 manager_role ，这样 manager_role 就拥有了所有成员角色的权限。

4.4 权限管理的实例分析

为了更好地理解权限管理，我们来看一个实际的例子。假设我们有一个在线商店的数据库，其中包括用户、订单和产品三个表。

4.4.1 角色和权限分配

首先，我们需要为不同的用户角色创建权限，例如：

• admin 角色拥有所有权限。
• sales_rep 角色可以读取和更新订单信息。
• customer_service 角色可以读取用户信息和订单信息，但是只能更新特定的订单字段。

-- 创建角色
CREATE ROLE admin;
GRANT ALL ON ALL TABLES IN SCHEMA public TO admin;
-- 创建销售代表角色
CREATE ROLE sales_rep;
GRANT SELECT, INSERT, UPDATE ON orders TO sales_rep;
-- 创建客户服务角色
CREATE ROLE customer_service;
GRANT SELECT ON customers, orders TO customer_service;

4.4.2 维护和审计

随着业务的发展，我们可能需要定期维护权限设置，确保它们仍然满足业务需求，并进行安全审计。

例如，我们可以定期检查哪些角色拥有 TRUNCATE 权限：

SELECT rolname FROM pg_roles WHERE rolname LIKE 'pg_%' OR rolname LIKE '%_role' OR rolname IN ('admin', 'sales_rep', 'customer_service');

通过这种方式，我们可以确保权限设置不会因为人员更迭或业务调整而造成安全隐患。

权限管理是数据库管理中不可或缺的一部分。在本章中，我们深入了解了权限管理的基本概念和实践操作，并通过实例分析进一步理解了权限管理的实际应用。掌握这些知识，将有助于我们在日常工作中确保数据的安全性和访问控制的有效性。

5. 角色的添加和删除以及psql中的实用命令

角色管理是数据库安全和访问控制的核心组成部分。在PostgreSQL中，角色既代表用户也代表用户组，拥有不同的权限集，用以控制对数据库对象的访问。本章节我们将深入了解如何添加和删除角色，以及一些psql实用命令，为数据库管理提供便利。

5.1 角色的添加和删除操作

在数据库中，添加新角色和删除角色是常见的操作。角色可以拥有不同的权限，对于资源的访问控制至关重要。

5.1.1 向现有角色添加权限

向角色添加权限通常使用 GRANT 命令，可以给角色赋予对数据库对象的访问权限。比如，要让一个角色 read_user 能够查询 sales 表，可以执行如下命令：

GRANT SELECT ON sales TO read_user;

这里 SELECT 是权限种类， sales 是要赋予权限的数据库对象。如果需要赋予多个权限，可以使用逗号分隔权限列表：

GRANT SELECT, UPDATE ON sales TO read_user;

5.1.2 删除角色及其权限

删除角色及所有关联的权限可以使用 DROP ROLE 命令。如果要删除角色 read_user ，可以执行：

DROP ROLE read_user;

在删除角色前，如果想要撤销角色的所有权限，可以使用 REVOKE 命令：

REVOKE ALL ON sales FROM read_user;

使用 REVOKE 会撤销角色的所有权限，之后再执行 DROP ROLE 命令将角色彻底从系统中移除。

5.2 psql中的其他实用命令

在日常的数据库管理工作中，psql提供了一些非常实用的命令，用于查询系统信息、操作数据库对象以及控制事务。

5.2.1 系统信息查询命令

psql提供了多种系统信息查询命令，可以帮助管理员了解数据库的状态。例如，要查看当前PostgreSQL服务器版本信息，可以使用：

SELECT version();

查看所有数据库列表：

\l

获取当前数据库的大小：

SELECT pg_size_pretty(pg_database_size('your_database_name'));

这些命令可以快速获取系统信息，方便进行数据库诊断和管理。

5.2.2 数据库对象操作命令

数据库对象包括表、视图、索引等，psql提供了多种命令来操作这些对象。例如，列出当前数据库所有表：

\dt

创建一个新表：

CREATE TABLE test_table (id SERIAL PRIMARY KEY, name VARCHAR);

查看表结构：

\d test_table

这些命令可以方便地查看和管理数据库对象。

5.2.3 事务控制和错误处理命令

在数据库操作中，事务控制是保证数据一致性的关键。psql提供了事务控制命令，如 BEGIN 、 COMMIT 和 ROLLBACK ：

• 开始一个新事务：

BEGIN;

• 提交当前事务：

COMMIT;

• 如果出现错误，回滚到事务开始前的状态：

ROLLBACK;

此外，psql允许执行脚本文件和条件判断，例如执行SQL脚本文件：

\i /path/to/script.sql

以及条件执行：

\if condition
   -- do something if condition is true
\endif

这些实用命令极大地提高了psql在数据库管理方面的灵活性和效率。

在本章中，我们深入了解了角色的添加和删除操作，并探索了psql中一系列实用命令，这些都对于数据库的日常管理和维护至关重要。掌握这些命令和操作将使得数据库管理工作更为高效和安全。接下来的章节，我们将进一步讨论如何进行数据备份和恢复，以及性能监控和优化技巧，从而确保数据库的稳定运行和数据安全。

本文还有配套的精品资源，点击获取

简介：本文详细介绍在PostgreSQL中使用psql命令行工具来创建用户、数据库以及执行管理操作。包括psql工具的安装与启动、创建新用户（角色）、数据库的创建和配置、权限的授予与管理、角色管理以及一些实用的psql命令。这些操作对于数据库的日常管理工作至关重要，熟练掌握它们能够提升工作效率并确保数据库的安全性。

本文还有配套的精品资源，点击获取