pg数据库创建用户给予所有数据库select权限

#!/bin/bash

USER="user"
PASSWORD="user!"  # 实际使用时用变量或密钥管理

# 创建用户
psql -U postgres -c "CREATE ROLE $USER WITH LOGIN PASSWORD '$PASSWORD';"

# 获取所有数据库列表
DATABASES=$(psql -U postgres -t -c "SELECT datname FROM pg_database WHERE datname NOT LIKE 'template%'")

# 授予连接权限
for DB in $DATABASES; do
    psql -U postgres -c "GRANT CONNECT ON DATABASE \"$DB\" TO $USER;"
done

# 在每个库中执行对象授权
for DB in $DATABASES; do
    psql -U postgres -d "$DB" <<-EOSQL
        GRANT USAGE ON SCHEMA public TO $USER;
        GRANT SELECT ON ALL TABLES IN SCHEMA public TO $USER;
        ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO $USER;
EOSQL
done

# 确保未来新库自动继承权限
psql -U postgres -c "GRANT CONNECT ON DATABASE template1 TO $USER;"