PostgreSQL 创建和管理用户

jetsung

PostgreSQL 创建和管理用户

在服务器端设置 PostgreSQL

登录 PostgreSQL
使用超级用户连接到 PostgreSQL：

   psql -U postgres

创建数据库
为 asciinema 创建数据库：

   CREATE DATABASE asciinema;

创建用户
创建一个专用用户来访问数据库，并设置密码：

   CREATE USER asciinema WITH PASSWORD 'your_password';

授予权限
将该用户赋予 asciinema 数据库的权限：

   GRANT ALL PRIVILEGES ON DATABASE asciinema TO asciinema;

使用超级用户登录数据库，建表

psql -U postgres -d asciinema

授权 asciinema 用户在 public schema 下有 USAGE 和 CREATE 权限：

GRANT USAGE ON SCHEMA public TO asciinema;
GRANT CREATE ON SCHEMA public TO asciinema;

测试连接

在服务器上运行以下命令测试连接是否成功：

psql -h <external-db-host> -U asciinema -d asciinema

输入密码后，应能够连接到数据库。

要从 PostgreSQL 数据库中删除一个用户，可以按照以下步骤操作：

登录 PostgreSQL

以超级用户身份登录 PostgreSQL：

psql -U postgres

删除用户

检查用户是否在使用
确保用户未拥有任何数据库或其他对象：

   REASSIGN OWNED BY asciinema_user TO postgres;
   DROP OWNED BY asciinema;

REASSIGN OWNED: 将用户拥有的对象转移给其他用户（例如 postgres）。
DROP OWNED: 删除用户的所有权限和引用。

删除用户
如果用户不再被任何对象引用，可以安全删除用户：

   DROP USER asciinema;

注意事项

如果用户仍拥有数据库或表，会导致删除失败。请确保清理所有相关对象。
使用 REASSIGN OWNED 和 DROP OWNED 时需要目标用户（如 postgres）存在，确保其具有管理权限。

验证用户已被删除

运行以下命令检查用户是否还存在：

\du

你将看不到 asciinema 用户。

jetsung

确认表是否存在，以及它的所有者：

SELECT tablename, tableowner
FROM pg_tables
WHERE tablename = 'schema_migrations';

如果输出类似：

 tablename         | tableowner
-------------------+-------------
 schema_migrations | postgres

授予目标用户权限（比如 asciinema 用户）访问该表：

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE schema_migrations TO asciinema;

如果你不想每次都授予权限，可以直接把该表转让给 asciinema 用户：

ALTER TABLE schema_migrations OWNER TO asciinema;

只修复权限（不清空数据）

-- 授权访问所有当前表
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO asciinema;

-- 授权使用所有序列（auto-increment ID）
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA public TO asciinema;

jetsung

登录用户

psql -U postgres

# 指定数据库
psql -U postgres -d asciinema

添加可创建数据库的权限

ALTER ROLE asciinema WITH CREATEDB

重置密码

ALTER ROLE asciinema WITH PASSWORD 'newpassword';

授予连接权限（例如到 postgres 数据库）：

GRANT CONNECT ON DATABASE postgres TO asciinema;

提升至超级用户权限（危险）

ALTER ROLE asciinema WITH SUPERUSER;

授予 asciinema 对 postgres 角色的权限

GRANT postgres TO asciinema;

切换角色

SET ROLE asciinema;

恢复原始角色

RESET ROLE;

# 查看当前用户
SELECT current_user;

# 查看当前数据库
SELECT current_database();

# 创建数据库
CREATE DATABASE asciinema;

# 转移所有权限给指定用户
ALTER DATABASE asciinema OWNER TO asciinema;

# 验证所有权是否已变更
SELECT datname, rolname
FROM pg_database
JOIN pg_roles ON pg_database.datdba = pg_roles.oid
WHERE datname = 'asciinema';

# 以 asciinema 身份执行命令
\c - asciinema

# 查看表
\dt

# 查看表结构（假设表名为 my_table）
\d my_table

# 显示详细信息
\d+

# 列出所有模式中的表
\dt *.*

# 仅列出特定模式的表
\dt public.*

# 查看角色属性
\du asciinema

# 检查数据库权限
\l mydb

# 检查表权限
\dp

# 启用扩展模式，以便查看数据
\x

# 终端宽度：垂直格式适合窄终端或字段较多的表。如果终端太宽，可调整 \pset columns
\pset columns 80

# 多记录：\x 模式下多条记录会逐条显示为垂直格式，可能较长。建议配合 LIMIT 使用：
SELECT * FROM my_table LIMIT 1 \x;

# 设置输出格式为 wrapped 或 aligned
\pset format wrapped

# 检查是否有活跃连接
SELECT pid, usename, client_addr, query
FROM pg_stat_activity
WHERE usename = 'asciinema' AND pid <> pg_backend_pid();

# 断开所有会话
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = 'asciinema' AND pid <> pg_backend_pid();

# 删除数据库
DROP DATABASE asciinema;

删除用户

-- 检查拥有的对象
SELECT * FROM pg_catalog.pg_class WHERE relowner = (SELECT oid FROM pg_roles WHERE rolname = 'asciinema');

-- 删除拥有的对象（或使用 REASSIGN OWNED 转移）
DROP OWNED BY asciinema;

-- 删除角色
DROP ROLE asciinema;

-- 验证删除
SELECT rolname FROM pg_roles WHERE rolname = 'asciinema';

-- 确保连接到正确的数据库
\c asciinema

-- 假设原所有者是 postgres，新所有者是 asciinema
-- 转移 postgres 用户在当前数据库中拥有的所有对象的所有权
REASSIGN OWNED BY postgres TO asciinema;

-- 授予新用户对 schema 的 USAGE 权限（以防权限缺失）
GRANT USAGE ON SCHEMA public TO asciinema;

-- 授予新用户对所有表的 SELECT、INSERT、UPDATE、DELETE 等权限
GRANT ALL ON ALL TABLES IN SCHEMA public TO asciinema;

-- 授予新用户对所有序列的 USAGE 和 SELECT 权限（如果表有序列）
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO asciinema;

-- 可选：验证表的所有权
\dt

-- 可选：检查权限
\dp