Gitlab用户信息批量导出，批量block，unblock

1. 查看Gitlab对应的系统用户 

[root@ljettodevops-prod-38 wubo]# cat /etc/passwd | grep gitlab
gitlab-www:x:994:990::/var/opt/gitlab/nginx:/bin/false
git:x:993:989::/var/opt/gitlab:/bin/sh
gitlab-redis:x:992:988::/var/opt/gitlab/redis:/bin/false
gitlab-psql:x:991:987::/var/opt/gitlab/postgresql:/bin/sh
gitlab-prometheus:x:990:986::/var/opt/gitlab/prometheus:/bin/sh

2.根据信息登陆数据库

[root@ljettodevops-prod-38 wubo]# su - gitlab-psql

3. # 登陆数据库（-h指定host，-d指定数据库）

 psql -h /var/opt/gitlab/postgresql -d gitlabhq_production

4.查看数据库

gitlabhq_production=# \l
                                             List of databases
        Name         |    Owner    | Encoding |   Collate   |    Ctype    |        Access privileges        
---------------------+-------------+----------+-------------+-------------+---------------------------------
 gitlabhq_production | gitlab      | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 postgres            | gitlab-psql | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | 
 template0           | gitlab-psql | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/"gitlab-psql"               +
                     |             |          |             |             | "gitlab-psql"=CTc/"gitlab-psql"
 template1           | gitlab-psql | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/"gitlab-psql"               +
                     |             |          |             |             | "gitlab-psql"=CTc/"gitlab-psql"
(4 rows)

5. 使用库且查看库中包

gitlabhq_production=# \c gitlabhq_production
You are now connected to database "gitlabhq_production" as user "gitlab-psql".
gitlabhq_production=# \dt
                         List of relations
 Schema |                   Name                   | Type  | Owner  
--------+------------------------------------------+-------+--------
 public | abuse_reports                            | table | gitlab
 public | appearances                              | table | gitlab
 public | application_setting_terms                | table | gitlab
 public | application_settings                     | table | gitlab
 public | audit_events                             | table | gitlab
 public | award_emoji                              | table | gitlab
 public | badges                                   | table | gitlab
 public | boards                                   | table | gitlab
 public | broadcast_messages                       | table | gitlab
 public | chat_names                               | table | gitlab
 public | chat_teams                               | table | gitlab

6.表结构：

gitlabhq_production=# \d identities;
                                     Table "public.identities"
   Column   |            Type             |                        Modifiers                        
------------+-----------------------------+---------------------------------------------------------
 id         | integer                     | not null default nextval('identities_id_seq'::regclass)
 extern_uid | character varying           | 
 provider   | character varying           | 
 user_id    | integer                     | 
 created_at | timestamp without time zone | 
 updated_at | timestamp without time zone | 
Indexes:
    "identities_pkey" PRIMARY KEY, btree (id)
    "index_identities_on_user_id" btree (user_id)

7 。# 查看表信息    \x 可以像mysql的\G一样 展示数据

1、列举数据库：\l
2、选择数据库：\c 数据库名
3、查看该某个库中的所有表：\dt
4、切换数据库：\c interface
5、查看某个库中的某个表结构：\d 表名
6、查看某个库中某个表的记录：select * from apps limit 1;
7、显示字符集：\encoding
8、退出psgl：\q
设置字符集为 utf-8 就可以了.
postgres=# \encoding utf-8       // 设置客户端的字元集
postgres=# \encoding                  // 显示客户端的字元集
postgres=# show client_encoding;      // 显示客户端的字元集
postgres=# show server_encoding;      // 显示服务器的字元集

PostgreSQL操作-psql基本命令_沈荣荣的博客-CSDN博客_psql

gitlabhq_production=# \x
Expanded display is on.
gitlabhq_production=# select * from identities;
-[ RECORD 1 ]----------------------------------------------------
id         | 1
extern_uid | cn=sun_jian,ou=users,dc=rd,dc=hoperun,dc=com
provider   | ldapmain
user_id    | 132
created_at | 2020-03-13 12:17:13.085423
updated_at | 2020-03-13 12:17:13.085423
-[ RECORD 2 ]-----

8.退出数据库

gitlabhq_production=# \q

确定表表users中的 username , email , state 字段是需要提取的信息，进行导出操作

不是通过ldap创建的用户
echo 'select users.id,users.email,users.name,users.username,users.state from users where users.id not in (select user_id from identities);' |psql -h /var/opt/gitlab/postgresql -d gitlabhq_production > no-ldap-users.info
 
通过ldap创建的用户
echo 'select users.id,users.email,users.name,users.username,users.state,identities.extern_uid,identities.provider from identities,users where users.id=identities.user_id;' |psql -h /var/opt/gitlab/postgresql -d gitlabhq_production > ldap-users.info 
 
 
所有用户
echo 'select username,email,state from users;' |psql -h /var/opt/gitlab/postgresql -d gitlabhq_production > all-users.txt

通过接口方式：
 

#!/bin/bash
perPage=100
page=11
for i in {1..11}
do
	#curl  -X GET -H "PRIVATE-TOKEN: 9yxKWbi7rN6U64cgDfMz" http://172.16.10.38/api/v4/users?per_page=$perPage&page=$i|python -m json.tool
	curl  -X GET -H "PRIVATE-TOKEN: 9yxKWbi7rN6U64cgDfMz" http://172.16.10.38/api/v4/users?per_page=$perPage&page=$i
done

批量block

[root@ljettodevops-prod-38 wubo]# cat readme 
 
echo 'select users.id,users.email,users.name,users.username,users.state from users where users.id not in (select user_id from identities);' |psql -h /var/opt/gitlab/postgresql -d gitlabhq_production > no-ldap-users.info
 
echo 'select users.id,users.email,users.name,users.username,users.state,identities.extern_uid,identities.provider from identities,users where users.id=identities.user_id;' |psql -h /var/opt/gitlab/postgresql -d gitlabhq_production > ldap-users.info 
 
 
echo 'select username,email,state from users;' |psql -h /var/opt/gitlab/postgresql -d gitlabhq_production > all-users.txt
 
 
 
 
 
 
curl  -X GET -H "PRIVATE-TOKEN: xxxxxx" http://172.16.10.38/api/v4/users?username=wu_bo2 | python -m json.tool
 
 
curl  -X GET -H "PRIVATE-TOKEN: xxxxxxxxx" http://172.16.10.38/api/v4/users?username=li_jingtao | python -m json.tool | jq '.[].id'
curl  -X POST -H "PRIVATE-TOKEN: xxxxxxxxx" http://172.16.10.38/api/v4/users/292/block
curl  -X POST -H "PRIVATE-TOKEN: xxxxxxxxx" http://172.16.10.38/api/v4/users/292/unblock
 
 for i in  $(cat all_names_block);do curl  -X POST -H "PRIVATE-TOKEN: xxxxxxxx" http://172.16.10.38/api/v4/users/$(curl  -X GET -H "PRIVATE-TOKEN: xxxxxxxx" http://172.16.10.38/api/v4/users?username=$i | python -m json.tool | jq '.[].id')/block; done

[root@ljettodevops-prod-38 wubo]# cat all_names_block 
li_wanliang
xue_min
liuyang
liu_jianguo
jie_panjiao
tang_liang
zhang_yudong
zhao_xueqing