表不可见

Question

svmitin

Asked:2020-09-25 21:37:59 +0000 UTC2020-09-25 21:37:59 +0000 UTC 2020-09-25 21:37:59 +0000 UTC

使用 Ansible 在 PostgreSQL 中创建用户和数据库

772

在 Ubuntu 20.04 上部署一个干净的 VPS 来部署一个 Django 项目。

无法创建 PostgreSQL 用户和数据库

任务代码：

- name: Create database user
  become: yes
  become_user: postgres
  postgresql_user:
    name: "{{ db_user }}"
    password: "{{ db_password }}"
    role_attr_flags: SUPERUSER

- name: Create a new database
  become_user: postgres
  community.general.postgresql_db: 
    name="{{ db_name }}"
    encoding='UTF-8'
    lc_collate='en_US.UTF-8'
    lc_ctype='en_US.UTF-8'
    template='template0'
    state=present

pg_hba.conf：

local   all             postgres                                md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

给出错误消息：

TASK [system : Create database user] ************************************************************************************
[WARNING]: Module did not set no_log for no_password_changes
fatal: [root]: FAILED! => {"changed": false, "msg": "unable to connect to database: fe_sendauth: no password supplied\n"}

该怎么办？

2 个回答

Voted

don Rumata · Answer 1 · 2020-09-26T20:10:21Z

我的意大利面尿布（角色很紧张，没有时间），我做了一个节点，用于教授“DB”主题的学生：

---
# WORK
# https://www.postgresql.org/download/linux/ubuntu/
# https://info-comp.ru/sisadminst/598-install-postgresql-10-on-ubuntu-server.html
# https://www.digitalocean.com/community/tutorials/postgresql-ubuntu-16-04-ru
# https://eax.me/postgresql-install/
  - name: Install PostgreSQL
    hosts: all
    strategy: free
    serial:
      - "100%"
    tasks:

    # https://wiki.postgresql.org/wiki/Apt
    - name: Install PostgreSQL server 4 Ubuntu
      when:
        - ansible_distribution == 'Ubuntu'
        - ansible_architecture == 'x86_64'
      become: yes
      block:
        - apt_key:
            url: https://www.postgresql.org/media/keys/ACCC4CF8.asc
            state: present
        - apt_repository:
            repo: deb [arch=amd64] http://apt.postgresql.org/pub/repos/apt/ {{ ansible_distribution_release }}-pgdg main
            state: present
            filename: pgdg
        # Как видно из register - вычисляем последнюю версию pg.
        - shell: aptitude search -F %p postgresql- | grep postgresql-[0-9][0-9]$ | sort --version-sort | tail -1 | cut -d '-' -f 2
          register: pg_latest_version
          changed_when: pg_latest_version.rc != 0
        - debug:
            msg: '{{ pg_latest_version }}'
        - apt:
            name:
              - postgresql-{{ pg_latest_version.stdout }}
              # Советуют в интернетах поставить, чтобы было меньше геморняка.
              - postgresql-contrib
              - libpq-dev
              # Это чтобы ansible мог дальше рулить базами.
              - python-psycopg2
            state: latest
            install_recommends: yes
        - service:
            name: postgresql
            state: started
      tags:
        - linux
        - ubuntu
        - install
        - db
        - postgresql
        - pg

    # https://info-comp.ru/sisadminst/598-install-postgresql-10-on-ubuntu-server.html
    # По идее - тут универсальный путь до конфига. Но пока пусть будет бубунта.
    - name: Config files PostgreSQL 4 Ubuntu
      when:
        - ansible_distribution == 'Ubuntu'
        - ansible_architecture == 'x86_64'
      become: yes
      block:
        - lineinfile:
            path: /etc/postgresql/{{ pg_latest_version.stdout }}/main/postgresql.conf
            insertafter: 'Connection Settings'
            # line: listen_addresses = '10.0.0.0/8'
            # line: listen_addresses = '10.0.0.0'
            line: listen_addresses = '*'
            state: present
            backup: yes
          register: pgsql_config
        - debug:
            msg: '{{ pgsql_config.changed }}'
        - lineinfile:
            path: /etc/postgresql/{{ pg_latest_version.stdout }}/main/pg_hba.conf
            # Порядок строк - влияет на права. Поэтому, если воткнуть в конец - ничего работать не будет.
            # https://www.sql.ru/forum/actualutils.aspx?action=gotomsg&tid=1195193&msg=18678315
            insertafter: '# TYPE'
            line: host all all 10.0.0.0/8 md5
            state: present
            backup: yes
          register: pgsql_hba_config
        - service:
            name: postgresql
            state: restarted
          # Этот вариант пашет, когда поменялся хоть какой-нибудь.
          when: (pgsql_config.changed == true) or (pgsql_hba_config.changed == true)
          # Этот вариант пашет, когда ОБА конфига поменялись.
          # when:
          #   - pgsql_config.changed == true
          #   - pgsql_hba_config.changed == true
      tags:
        - linux
        - ubuntu
        - db
        - postgresql
        - pg
        - config

    - name: Create test database PostgreSQL 4 Linux
      when:
        - ansible_system == 'Linux'
      become: yes
      become_user: postgres
      vars:
        student_database: student_db
        student_user: student
        student_password: student
        password_for_default_postgres_user: qazwsxedc
      block:
        - postgresql_user:
            name: postgres
            password: '{{ password_for_default_postgres_user }}'
        - postgresql_db:
            db: '{{ student_database }}'
            encoding: UTF-8
            state: present
        - postgresql_user:
            db: '{{ student_database }}'
            name: '{{ student_user }}'
            password: '{{ student_password }}'
            state: present
            expires: infinity
            priv: CREATE,CONNECT,TEMPORARY
        - postgresql_db:
            db: '{{ student_database }}'
            owner: '{{ student_user }}'
            encoding: UTF-8
            state: present
        - postgresql_privs:
            database: '{{ student_database }}'
            state: present
            host: 127.0.0.1
            privs: '{{ item }}'
            type: database
            roles: '{{ student_user }}'
            login: '{{ student_user }}'
            password: '{{ student_password }}'
          with_items:
            - CREATE
            - CONNECT
            - TEMPORARY
      tags:
        - linux
        - ubuntu
        - db
        - postgresql
        - pg
        - config
        - create
        - student

哪些部分难以理解 - 在评论中提问。

svmitin · Answer 2 · 2020-09-26T01:36:12Z

第一部分. 寻找文件

您可以从官方文档中获取示例代码：

- name: Create a new database with name "acme" and specific encoding and locale # settings.
  community.general.postgresql_db:
    name: acme
    encoding: UTF-8
    lc_collate: de_DE.UTF-8
    lc_ctype: de_DE.UTF-8
    template: template0

他不忠实，不够

become: yes
become_user: postgres

如果我对所有内容都理解正确，本节会将用户从更改root为postgres（其密码未知）。Administer DBMS 只能postgres在安装 PostgreSQL 时自动安装在系统中

有时您可能会遇到此代码的错误替代方案：

sudo: postgres

这是不对的，不要这样做

第二部分。PostgreSQL 配置

在我遇到链接的论坛上

/etc/postgresql/12/main/pg_hba.conf
/etc/postgresql/12/main/postgresql.conf

因此，pg_hba.conf您需要更改peer为`md5. 我得到它是这样的：

local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

但是postgresql.conf需要更换

# listen_addresses = 'localhost'

在

listen_addresses = '*'

第三部分。准备环境

为了使所有这些经济运行，请在您的机器上安装 ansible 插件community.general

ansible-galaxy collection install community.general

要使 Ansible 与 PostgreSQL 一起工作，目标机器必须具有python3-psycopg2

- name: Install packages
  when: ansible_distribution == 'Ubuntu'
  become: yes
  apt:
    name:
      - postgresql
      - postgresql-contrib
      - python3-pip
      - python3-dev
      - python3-venv
      - python3-psycopg2
    state: present

使用 Ansible 在 PostgreSQL 中创建用户和数据库

如何从列表中打印最大元素（str 类型）的长度？

如何在 PyQT5 中清除 QFrame 的内容

如何将具有特定字符的字符串拆分为两个不同的列表？

导航栏活动元素

是否可以将文本放入数组中？[关闭]

如何一次用多个分隔符拆分字符串？

如何通过 ClassPath 创建 InputStream？

在一个查询中连接多个表

对列表列表中的所有值求和

如何对齐 string.Format 中的列？

使用 Ansible 在 PostgreSQL 中创建用户和数据库

2 个回答

相关问题