如何修改psql语句为指定PostgreSQL数据库授予public schema权限？

你遇到的问题是因为执行授权语句时默认连接的是postgres数据库，导致权限被授予到了默认库而非myapp_db。需要修改脚本，让授权命令针对目标数据库执行，同时补充schema的访问权限。

修改后的脚本如下：

#!/bin/bash

# 检查并创建数据库
psql -U postgres -tc "SELECT 1 FROM pg_database WHERE datname = 'myapp_db'" \
| grep -q 1 || psql -U postgres -c "CREATE DATABASE myapp_db"

# 创建用户
psql -U postgres -c "CREATE USER myapp_user WITH PASSWORD 'some_password'"

# 授予数据库权限
psql -U postgres -c "GRANT ALL ON DATABASE myapp_db TO myapp_user"

# 连接到myapp_db，授予public schema的使用权限和所有表权限
psql -U postgres -d myapp_db -c "GRANT USAGE ON SCHEMA public TO myapp_user"
psql -U postgres -d myapp_db -c "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO myapp_user"

# 可选：确保后续新创建的表自动赋予权限
psql -U postgres -d myapp_db -c "ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL PRIVILEGES ON TABLES TO myapp_user"

关键修改说明：

• 使用-d myapp_db参数指定授权命令在myapp_db中执行，而非默认的postgres库
• 添加GRANT USAGE ON SCHEMA public TO myapp_user：用户必须拥有schema的USAGE权限才能访问其中的对象，这是容易遗漏的必要权限
• 可选的默认权限设置：确保后续在public schema中创建的新表自动赋予myapp_user权限，避免后续新增表再次出现权限问题

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