授权某个用户对所有数据库下的schema的访问权限
可根据实际需求修改
#!/bin/bash #Defining connection information #Default user posgres #Author:Songshaohua #Date:2020-07-08 #Version:default function Usage(){ if [ $# -ne 1 ];then echo "Usage: sh $0 需要被授权的用户" exit 999 fi } function grant_privs(){ DEFAULTUSER=postgres DEFAULTDB=postgres conninfo="psql -U ${DEFAULTUSER} -d ${DEFAULTDB} -Atq -c" dblist=`${conninfo} "SELECT datname FROM pg_database WHERE datname !~ ‘template0|template1‘"` #连接到每个数据库进行授权 for db in ${dblist};do #先查询每个数据库中的schema,普通用户对数据库操作实际上是对schema操作 dbconn="psql -U ${DEFAULTUSER} -d ${db} -Atq -c" schemalist=`${dbconn} "SELECT nspname FROM pg_namespace WHERE nspname !~ ‘^pg|information_schema‘"` for slist in ${schemalist};do ${dbconn} "GRANT USAGE ON SCHEMA ${slist} TO $1" ${dbconn} "GRANT SELECT ON ALL TABLES IN SCHEMA ${slist} TO $1" ${dbconn} "GRANT UPDATE ON ALL TABLES IN SCHEMA ${slist} TO $1" done done } main(){ Usage $1 grant_privs $1 } main $*