PostgreSQL小工具脚本

授权某个用户对所有数据库下的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 $*