PostgreSQL远程连接用户权限配置问题求助

首先，你遇到的报错是因为语法错误：PostgreSQL中，CREATE不是表级别的权限，不能直接在ALL TABLES上撤销CREATE权限。CREATE权限是针对数据库或 schema 的，用来控制是否能在其中创建对象（比如表、schema）。

接下来，我会帮你一步步配置符合需求的权限：允许对指定数据库的表执行任意行操作（增删改查），但禁止添加列、创建/删除表/库，且无其他数据库访问权限。

前提准备（如果还没创建用户） #

首先创建用户并允许远程访问（注意替换user_name和your_password）：

CREATE USER user_name WITH PASSWORD 'your_password' LOGIN;
-- 允许远程访问（如果需要，可限制特定IP，比如替换为'192.168.1.0/24'）
ALTER USER user_name WITH HOST '%';

同时记得修改pg_hba.conf文件，添加一行允许该用户远程连接：

host    example     user_name     0.0.0.0/0     scram-sha-256

然后重启PostgreSQL服务生效。

正确的权限配置步骤 #

1. 仅允许用户连接到指定数据库
   先撤销之前可能给的过度权限，只保留连接权限：

REVOKE ALL PRIVILEGES ON DATABASE "example" FROM user_name;
GRANT CONNECT ON DATABASE "example" TO user_name;

2. 授予schema的使用权限（否则无法访问表）

GRANT USAGE ON SCHEMA public TO user_name;

3. 授予表的行操作权限，同时禁止修改表结构（添加列需要ALTER权限）

-- 授予现有表的增删改查权限
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO user_name;
-- 撤销表的ALTER权限（禁止添加/删除列、修改表结构）
REVOKE ALTER ON ALL TABLES IN SCHEMA public FROM user_name;

-- 为未来新建的表设置默认权限（避免后续建表后用户没有权限）
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO user_name;

ALTER DEFAULT PRIVILEGES IN SCHEMA public 
REVOKE ALTER ON TABLES FROM user_name;

4. 禁止用户在数据库中创建schema，以及在schema中创建表

-- 撤销数据库上的CREATE权限（禁止创建新schema）
REVOKE CREATE ON DATABASE "example" FROM user_name;
-- 撤销schema上的CREATE权限（禁止创建新表）
REVOKE CREATE ON SCHEMA public FROM user_name;

权限验证 #

你可以切换到该用户，测试以下操作：

• ✅ 可以执行SELECT * FROM table_name;、INSERT INTO table_name VALUES(...);等行操作
• ❌ 无法执行ALTER TABLE table_name ADD COLUMN new_col INT;（会提示权限不足）
• ❌ 无法执行CREATE TABLE new_table(...);（权限不足）
• ❌ 无法访问其他数据库（比如\c other_db会失败）

内容的提问来源于stack exchange，提问作者user3541631