访问GitLab的PostgreSQL数据库

零 修订记录

一 摘要

gitlab 与ldap or ad 集成，有时因为数据问题导致用户异常，这时就需要去gitlab 数据库手工修复数据。
我这里是公司域名改了，员工账号未变但是邮箱变了，从而导致了部分数据异常

二环境信息

gitlab 与 ad 集成

三实施

(一) 访问GitLab的PostgreSQL数据库

3.1.1 获取PostgreSQL 连接信息

[root@10-3-170-101 ~]# cat /var/opt/gitlab/gitlab-rails/etc/database.yml
# This file is managed by gitlab-ctl. Manual changes will be
# erased! To change the contents below, edit /etc/gitlab/gitlab.rb
# and run `sudo gitlab-ctl reconfigure`.

production:
  adapter: postgresql
  encoding: unicode
  collation:
  database: gitlabhq_production
  pool: 1
  username: "gitlab"
  password:
  host: "/var/opt/gitlab/postgresql"
  port: 5432
  socket:
  sslmode:
  sslcompression: 0
  sslrootcert:
  sslca:
  load_balancing: {"hosts":[]}
  prepared_statements: false
  statements_limit: 1000
  fdw:
[root@10-3-170-101 ~]#

可见数据库名： gitlabhq_production
主机：/var/opt/gitlab/postgresql
用户名：gitlab

3.1.2 获取连接用户

[root@10-3-170-101 ~]# cat /etc/passwd |grep gitlab
gitlab-www:x:379:377::/var/opt/gitlab/nginx:/bin/false
git:x:378:376::/var/opt/gitlab:/bin/sh
gitlab-redis:x:377:375::/var/opt/gitlab/redis:/bin/false
gitlab-psql:x:376:374::/var/opt/gitlab/postgresql:/bin/sh
gitlab-prometheus:x:375:373::/var/opt/gitlab/prometheus:/bin/sh
[root@10-3-170-101 ~]#

可见postgresql 用户：gitlab-psql

3.1.3 登录数据库

先切换到gitlab-psql 用户

[root@10-3-170-101 ~]# su - gitlab-psql
上一次登录：三 1月  6 19:05:14 CST 2021pts/0 上
-sh-4.2$

连接数据库
psql -h /var/opt/gitlab/postgresql -d gitlabhq_production

-sh-4.2$ psql -h /var/opt/gitlab/postgresql -d gitlabhq_production
psql (10.12)
Type "help" for help.

gitlabhq_production=#

存放ldap 信息

select * from identities;

gitlabhq_production=# select * from identities where user_id=18;
 id |                                              extern_uid                                              | provider | user_id |         crea
ted_at         |         updated_at         | secondary_extern_uid | saml_provider_id
----+------------------------------------------------------------------------------------------------------+----------+---------+-------------
---------------+----------------------------+----------------------+------------------
 14 | cn=kaisu,ou=XXXX应用研发中心公共产品研发部,ou=XXXX应用研发中心,ou=XXXX,dc=kxdigit,dc=com | ldapmain |      18 | 2020-03-27 0
2:31:12.720781 | 2020-09-02 01:14:09.591125 |                      |
(1 row)

gitlabhq_production=#

gitlabhq_production=# update users set state='active' where id=18 and state='ldap_blocked';
UPDATE 1

update users set email='sbyang2@XXXX.com' ,notification_email='sbyang2@XXXX.com' where id=60 and email='sbyang2@XXX.com';