一个用户维护的小脚本

一个用户维护的小脚本

近期工作中收到很多新建用户和database的需求，权限控制要求如下：

1）回收新建database的connect权限

2）回收public模式的create权限

3）创建用户和同名schema，并赋予database的connect权限

create database db_name;
\c db_name
revoke connect on database db_name from public;
revoke create on schema public from public;
create user user_name with password 'passwd';
grant connect on database db_name to user_name;
create schema user_name authorization user_name;
复制

每次创建都要写语句，尤其是需要创建用户较多时，现写语句浪费时间,简单写了一个小脚本用来生成创建语句

结果展示

脚本如下

#!/bin/bash

userlist='add_user.list'

create_user(){
  user_list=$1
  password="$2"
  for i in `cat ${user_list}`
    do
      echo "create user $i with password '${password}';"
    done
}

grant_connect(){
  user_list=$1
  db_name=$2
  for i in `cat ${user_list}`
    do
      echo "grant connect on database ${db_name} to $i;"
    done
}

create_schema(){
  user_list=$1
  for i in `cat ${user_list}`
    do
      echo "create schema $i authorization $i;"
    done
}

if [ $# -ne 2 ];then
  echo "eg: $0 database password"
  exit;
else
  echo "-----------------result sql------------------"
  echo "create database $1;"
  echo "\\c $1"
  echo
  echo "revoke connect on database $1 from public;"
  echo "revoke create on schema public from public;"
  echo
  create_user ${userlist} "$2"
  echo
  grant_connect ${userlist} $1
  echo
  create_schema ${userlist}
fi
复制