postgresql

创建用户并授予权限

在 PostgreSQL 中创建一个用户并授予权限通常分为几个步骤。以下是创建用户并为其授予对数据库和表的权限的优化步骤。

1. 创建用户

首先，使用 CREATE USER 创建新用户。可以设置密码，确保安全性。

CREATE USER sunboxiang WITH PASSWORD 'xxx';

执行这条命令后，PostgreSQL 会返回 CREATE ROLE，表示用户创建成功。

2. 授予用户对数据库的权限

接下来，我们需要授予用户对指定数据库的权限。使用 GRANT 命令授予用户在数据库上的所有权限。

GRANT ALL PRIVILEGES ON DATABASE mydb TO sunboxiang;

这条命令确保 sunboxiang 用户具有对 mydb 数据库的完全访问权限，包括创建、修改、删除表等操作。

3. 授予用户对表和序列的权限

由于在 PostgreSQL 中自增列（如 SERIAL 或 BIGSERIAL）依赖于序列，用户需要有适当的权限来使用序列，否则在插入数据时可能会遇到权限不足的错误。为了避免这种情况，我们需要授予用户对所有序列的权限。

授予对所有序列的权限

GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO sunboxiang;

这条命令为 sunboxiang 用户授予了对 public 模式下所有序列的 USAGE、SELECT 和 UPDATE 权限：

• USAGE: 允许用户使用序列（例如，生成下一个序列值）。

• SELECT: 允许用户读取序列的当前值。

• UPDATE: 允许用户修改序列（例如，使用 nextval() 获取下一个值）。

授予对所有表的权限

如果用户需要对表进行插入、查询、更新等操作，还需要为用户授予对所有表的权限：

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO sunboxiang;

这确保了 sunboxiang 用户对 public 模式下所有现有表有完全的操作权限。

4. 授予对未来对象的权限（可选）

如果你希望用户能够自动访问将来创建的所有表和序列，可以设置默认权限：

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO sunboxiang;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON SEQUENCES TO sunboxiang;

这将确保 sunboxiang 用户对未来在 public 模式下创建的所有表和序列自动拥有所有权限。

---

服务器命令整理

1. 登录到 PostgreSQL

使用 psql 命令行工具可以轻松登录到 PostgreSQL 数据库。你可以使用以下命令登录：

psql -U sunboxiang -d 数据库名称

• -U sunboxiang：指定使用 sunboxiang 用户登录 PostgreSQL。

• -d 数据库名称：指定要连接的数据库名称。如果没有指定数据库，系统会自动连接到与当前用户名同名的数据库。

• 注：超级用户可以不输入 -d 数据库名称，默认连接到默认数据库（通常是数据库用户名同名的数据库）。

示例：

psql -U sunboxiang -d mydb

这样会以 sunboxiang 用户身份连接到 mydb 数据库。

2. 退出 PostgreSQL

当你完成 PostgreSQL 的操作后，可以使用以下命令退出：

\q

这将会安全地退出 psql 会话。

3. 列出数据库中的表空间

在 PostgreSQL 中，表空间用于管理数据库中的数据存储位置。你可以使用 \db 命令来查看当前数据库的所有表空间。

\db

此命令将列出所有的表空间及其所在的存储位置。如果没有创建任何自定义表空间，输出通常会显示 PostgreSQL 的默认表空间，如 pg_default。

示例输出：

      Name     |  Owner   |  Location
---------------+----------+-----------
 pg_default    | postgres | 
 pg_global     | postgres |

• Name：表空间名称

• Owner：表空间的所有者

• Location：表空间存储的物理位置

4. 列出当前数据库中的所有表

要查看当前数据库中的所有表，可以使用 \dt 命令。这个命令列出了数据库中所有模式下的表对象。

\dt

示例输出：

               List of relations
 Schema |   Name    | Type  |  Owner  
--------+-----------+-------+---------
 public | test_table| table | postgres
 public | users     | table | postgres
(2 rows)

• Schema：表所在的模式（例如 public 是默认模式）。

• Name：表的名称。

• Type：对象的类型，通常为 table。

• Owner：表的所有者。

5. 列出当前数据库中的所有对象（包括视图、索引等）

如果你希望列出数据库中的所有对象（不仅仅是表，还包括视图、索引等），可以使用 \d 命令。

\d

此命令将显示当前数据库中的所有对象，包括表、视图、索引等，帮助你全面了解数据库的结构。