test_postgresql.yml

---

- hosts: localhost
  sudo: True
  sudo_user: postgres
  tasks:
    #
    # Create and destroy db
    #
    - name: Create DB
      postgresql_db:
        state: present
        name: ansible_test

    - name: Check that database created
      shell: echo "select datname from pg_database where datname = 'ansible_test';" | psql
      register: result

    - assert:
        that:
          - "result.stdout_lines[-1] == '(1 row)'"

    - name: Destroy DB
      postgresql_db:
        state: absent
        name: ansible_test

    - name: Check that database was destroyed
      shell: echo "select datname from pg_database where datname = 'ansible_test';" | psql
      register: result

    - assert:
        that:
          - "result.stdout_lines[-1] == '(0 rows)'"


# This corner case works to add but not to drop.  This is sufficiently crazy
# that I'm not going to attempt to fix it unless someone lets me know that they
# need the functionality
#
#    - postgresql_db:
#        state: 'present'
#        name: '"silly.""name"'
#    - shell: echo "select datname from pg_database where datname = 'silly.""name';" | psql
#      register: result
#
#    - assert:
#        that: "result.stdout_lines[-1] == '(1 row)'"
#    - postgresql_db:
#        state: absent
#        name: '"silly.""name"'
#    - shell: echo "select datname from pg_database where datname = 'silly.""name';" | psql
#      register: result
#
#    - assert:
#        that: "result.stdout_lines[-1] == '(0 rows)'"

    #
    # Test encoding, collate, ctype, template options
    #
    - name: Create a DB with encoding, collate, ctype, and template options
      postgresql_db:
        name: 'ansible_test'
        state: 'present'
        encoding: 'LATIN1'
        lc_collate: 'pt_BR'
        lc_ctype: 'es_MX'
        template: 'template0'

    - name: Check that the DB has all of our options
      shell: echo "select datname, pg_encoding_to_char(encoding), datcollate, datctype from pg_database where datname = 'ansible_test';" | psql
      register: result

    - assert:
        that:
          - "result.stdout_lines[-1] == '(1 row)'"
          - "'LATIN1' in result.stdout_lines[-2]"
          - "'pt_BR' in result.stdout_lines[-2]"
          - "'es_MX' in result.stdout_lines[-2]"
          - "'UTF8' not in result.stdout_lines[-2]"
          - "'en_US' not in result.stdout_lines[-2]"

    - name: Cleanup test DB
      postgresql_db:
        name: 'ansible_test'
        state: 'absent'

    - shell: echo "select datname, pg_encoding_to_char(encoding), datcollate, datctype from pg_database where datname = 'ansible_test';" | psql
      register: result

    - assert:
        that:
          - "result.stdout_lines[-1] == '(0 rows)'"


    #
    # Create and destroy user
    #
    - name: Create a user
      postgresql_user:
        name: "ansible_user1"
        encrypted: 'yes'
        password: "md55c8ccfd9d6711fc69a7eae647fc54f51"

    - name: Check that they were created
      shell: echo "select * from pg_user where usename='ansible_user1';" | psql
      register: result

    - assert:
        that:
          - "result.stdout_lines[-1] == '(1 row)'"

    - name: Remove user
      postgresql_user:
        name: "ansible_user1"
        state: 'absent'

    - name: Check that they were removed
      shell: echo "select * from pg_user where usename='ansible_user1';" | psql
      register: result

    - assert:
        that:
          - "result.stdout_lines[-1] == '(0 rows)'"

    - name: Create a user with all role attributes
      postgresql_user:
        name: "ansible_user1"
        state: "present"
        role_attr_flags: "SUPERUSER,CREATEROLE,CREATEDB,INHERIT,LOGIN,replication"

    - name: Check that the user has the requested role attributes
      shell: echo "select 'super:'||rolsuper, 'createrole:'||rolcreaterole, 'create:'||rolcreatedb, 'inherit:'||rolinherit, 'login:'||rolcanlogin, 'repl:'||rolreplication from pg_roles where rolname='ansible_user1';" | psql
      register: result

    - assert:
        that:
          - "result.stdout_lines[-1] == '(1 row)'"
          - "'super:t' in result.stdout_lines[-2]"
          - "'createrole:t' in result.stdout_lines[-2]"
          - "'create:t' in result.stdout_lines[-2]"
          - "'inherit:t' in result.stdout_lines[-2]"
          - "'login:t' in result.stdout_lines[-2]"
          - "'repl:t' in result.stdout_lines[-2]"

    - name: Modify a user to have no role attributes
      postgresql_user:
        name: "ansible_user1"
        state: "present"
        role_attr_flags: "NOSUPERUSER,NOCREATEROLE,NOCREATEDB,NOINHERIT,NOLOGIN,NOREPLICATION"

    - name: Check that the user has the requested role attributes
      shell: echo "select 'super:'||rolsuper, 'createrole:'||rolcreaterole, 'create:'||rolcreatedb, 'inherit:'||rolinherit, 'login:'||rolcanlogin, 'repl:'||rolreplication from pg_roles where rolname='ansible_user1';" | psql
      register: result

    - assert:
        that:
          - "result.stdout_lines[-1] == '(1 row)'"
          - "'super:f' in result.stdout_lines[-2]"
          - "'createrole:f' in result.stdout_lines[-2]"
          - "'create:f' in result.stdout_lines[-2]"
          - "'inherit:f' in result.stdout_lines[-2]"
          - "'login:f' in result.stdout_lines[-2]"
          - "'repl:f' in result.stdout_lines[-2]"

    - name: Modify a single role attribute on a user
      postgresql_user:
        name: "ansible_user1"
        state: "present"
        role_attr_flags: "LOGIN"

    - name: Check that the user has the requested role attributes
      shell: echo "select 'super:'||rolsuper, 'createrole:'||rolcreaterole, 'create:'||rolcreatedb, 'inherit:'||rolinherit, 'login:'||rolcanlogin, 'repl:'||rolreplication from pg_roles where rolname='ansible_user1';" | psql
      register: result

    - assert:
        that:
          - "result.stdout_lines[-1] == '(1 row)'"
          - "'super:f' in result.stdout_lines[-2]"
          - "'createrole:f' in result.stdout_lines[-2]"
          - "'create:f' in result.stdout_lines[-2]"
          - "'inherit:f' in result.stdout_lines[-2]"
          - "'login:t' in result.stdout_lines[-2]"
          - "'repl:f' in result.stdout_lines[-2]"

    - name: Cleanup the user
      postgresql_user:
        name: "ansible_user1"
        state: 'absent'

    - name: Check that they were removed
      shell: echo "select * from pg_user where usename='ansible_user1';" | psql
      register: result

    - assert:
        that:
          - "result.stdout_lines[-1] == '(0 rows)'"

    ### TODO: test expires, fail_on_user

    #
    # Test db owner ship
    #
    - name: Create an unprivileged user to own a DB
      postgresql_user:
        name: "ansible_user1"
        encrypted: 'yes'
        password: "md55c8ccfd9d6711fc69a7eae647fc54f51"

    - name: Create db with user ownership
      postgresql_db:
        name: "ansible_test"
        state: "present"
        owner: "ansible_user1"

    - name: Check that the user owns the newly created DB
      shell: echo "select pg_catalog.pg_get_userbyid(datdba) from pg_catalog.pg_database where datname = 'ansible_test';" | psql
      register: result

    - assert:
        that:
          - "result.stdout_lines[-1] == '(1 row)'"
          - "'ansible_user1' == '{{ result.stdout_lines[-2] | trim }}'"

    - name: Change the owner on an existing db
      postgresql_db:
        name: "ansible_test"
        state: "present"
        owner: "postgres"

    - name: Check that the user owns the newly created DB
      shell: echo "select pg_catalog.pg_get_userbyid(datdba) from pg_catalog.pg_database where datname = 'ansible_test';" | psql
      register: result

    - assert:
        that:
          - "result.stdout_lines[-1] == '(1 row)'"
          - "'postgres' == '{{ result.stdout_lines[-2] | trim }}'"

    - name: Cleanup db
      postgresql_db:
        name: "ansible_test"
        state: "absent"

    - name: Check that database was destroyed
      shell: echo "select datname from pg_database where datname = 'ansible_test';" | psql
      register: result

    - assert:
        that:
          - "result.stdout_lines[-1] == '(0 rows)'"

    - name: Cleanup test user
      postgresql_user:
        name: "ansible_user1"
        state: 'absent'

    - name: Check that they were removed
      shell: echo "select * from pg_user where usename='ansible_user1';" | psql
      register: result

    - assert:
        that:
          - "result.stdout_lines[-1] == '(0 rows)'"

    #
    # Test settings privleges
    #
    - name: Create db
      postgresql_db:
        name: "ansible_test"
        state: "present"

    - name: Create some tables on the db
      shell: echo "create table test_table1 (field text);" | psql ansible_test

    - shell: echo "create table test_table2 (field text);" | psql ansible_test

    - name: Create a user with some permissions on the db
      postgresql_user:
        name: "ansible_user1"
        encrypted: 'yes'
        password: "md55c8ccfd9d6711fc69a7eae647fc54f51"
        db: "ansible_test"
        priv: 'test_table1:INSERT,SELECT,UPDATE,DELETE,TRUNCATE,REFERENCES,TRIGGER/test_table2:INSERT/CREATE,CONNECT,TEMP'

    - name: Check that the user has the requested permissions (table1)
      shell: echo "select privilege_type from information_schema.role_table_grants where grantee='ansible_user1' and table_name='test_table1';" | psql ansible_test
      register: result_table1

    - name: Check that the user has the requested permissions (table2)
      shell: echo "select privilege_type from information_schema.role_table_grants where grantee='ansible_user1' and table_name='test_table2';" | psql ansible_test
      register: result_table2

    - name: Check that the user has the requested permissions (database)
      shell: echo "select datacl from pg_database where datname='ansible_test';" | psql ansible_test
      register: result_database

    - assert:
        that:
          - "result_table1.stdout_lines[-1] == '(7 rows)'"
          - "'INSERT' in result_table1.stdout"
          - "'SELECT' in result_table1.stdout"
          - "'UPDATE' in result_table1.stdout"
          - "'DELETE' in result_table1.stdout"
          - "'TRUNCATE' in result_table1.stdout"
          - "'REFERENCES' in result_table1.stdout"
          - "'TRIGGER' in result_table1.stdout"
          - "result_table2.stdout_lines[-1] == '(1 row)'"
          - "'INSERT' == '{{ result_table2.stdout_lines[-2] | trim }}'"
          - "result_database.stdout_lines[-1] == '(1 row)'"
          - "'ansible_user1=CTc/postgres' in result_database.stdout_lines[-2]"

    - name: Add another permission for the user
      postgresql_user:
        name: "ansible_user1"
        encrypted: 'yes'
        password: "md55c8ccfd9d6711fc69a7eae647fc54f51"
        db: "ansible_test"
        priv: 'test_table2:select'

    - name: Check that the user has the requested permissions (table2)
      shell: echo "select privilege_type from information_schema.role_table_grants where grantee='ansible_user1' and table_name='test_table2';" | psql ansible_test
      register: result_table2
    - assert:
        that:
          - "result_table2.stdout_lines[-1] == '(2 rows)'"
          - "'INSERT' in result_table2.stdout"
          - "'SELECT' in result_table2.stdout"


    #
    # Test priv setting via postgresql_privs module
    # (Depends on state from previous _user privs tests)
    #

    - name: Revoke a privilege
      postgresql_privs:
        type: "table"
        state: "absent"
        roles: "ansible_user1"
        privs: "INSERT"
        objs: "test_table2"
        db: "ansible_test"

    - name: Check that the user has the requested permissions (table2)
      shell: echo "select privilege_type from information_schema.role_table_grants where grantee='ansible_user1' and table_name='test_table2';" | psql ansible_test
      register: result_table2
    - assert:
        that:
          - "result_table2.stdout_lines[-1] == '(1 row)'"
          - "'SELECT' == '{{ result_table2.stdout_lines[-2] | trim }}'"

    - name: Revoke many privileges on multiple tables
      postgresql_privs:
        state: "absent"
        roles: "ansible_user1"
        privs: "INSERT,select,UPDATE,TRUNCATE,REFERENCES,TRIGGER,delete"
        objs: "test_table2,test_table1"
        db: "ansible_test"

    - name: Check that permissions were revoked (table1)
      shell: echo "select privilege_type from information_schema.role_table_grants where grantee='ansible_user1' and table_name='test_table1';" | psql ansible_test
      register: result_table1

    - name: Check that permissions were revoked (table2)
      shell: echo "select privilege_type from information_schema.role_table_grants where grantee='ansible_user1' and table_name='test_table2';" | psql ansible_test
      register: result_table2

    - assert:
        that:
          - "result_table1.stdout_lines[-1] == '(0 rows)'"
          - "result_table2.stdout_lines[-1] == '(0 rows)'"

    - name: Revoke database privileges
      postgresql_privs:
        type: "database"
        state: "absent"
        roles: "ansible_user1"
        privs: "Create,connect,TEMP"
        objs: "ansible_test"
        db: "ansible_test"

    - name: Check that the user has the requested permissions (database)
      shell: echo "select datacl from pg_database where datname='ansible_test';" | psql ansible_test
      register: result_database

    - assert:
        that:
          - "result_database.stdout_lines[-1] == '(1 row)'"
          - "'ansible_user1' not in result_database.stdout"

    - name: Grant database privileges
      postgresql_privs:
        type: "database"
        state: "present"
        roles: "ansible_user1"
        privs: "CREATE,connect"
        objs: "ansible_test"
        db: "ansible_test"

    - name: Check that the user has the requested permissions (database)
      shell: echo "select datacl from pg_database where datname='ansible_test';" | psql ansible_test
      register: result_database

    - assert:
        that:
          - "result_database.stdout_lines[-1] == '(1 row)'"
          - "'ansible_user1=Cc' in result_database.stdout"

    - name: Grant a single privilege on a table
      postgresql_privs:
        state: "present"
        roles: "ansible_user1"
        privs: "INSERT"
        objs: "test_table1"
        db: "ansible_test"

    - name: Check that permissions were added (table1)
      shell: echo "select privilege_type from information_schema.role_table_grants where grantee='ansible_user1' and table_name='test_table1';" | psql ansible_test
      register: result_table1

    - assert:
        that:
          - "result_table1.stdout_lines[-1] == '(1 row)'"
          - "'{{ result_table1.stdout_lines[-2] | trim }}' == 'INSERT'"

    - name: Grant many privileges on multiple tables
      postgresql_privs:
        state: "present"
        roles: "ansible_user1"
        privs: 'INSERT,SELECT,UPDATE,DELETE,TRUNCATE,REFERENCES,trigger'
        objs: "test_table2,test_table1"
        db: "ansible_test"

    - name: Check that permissions were added (table1)
      shell: echo "select privilege_type from information_schema.role_table_grants where grantee='ansible_user1' and table_name='test_table1';" | psql ansible_test
      register: result_table1

    - name: Check that permissions were added (table2)
      shell: echo "select privilege_type from information_schema.role_table_grants where grantee='ansible_user1' and table_name='test_table2';" | psql ansible_test
      register: result_table2

    - assert:
        that:
          - "result_table1.stdout_lines[-1] == '(7 rows)'"
          - "'INSERT' in result_table1.stdout"
          - "'SELECT' in result_table1.stdout"
          - "'UPDATE' in result_table1.stdout"
          - "'DELETE' in result_table1.stdout"
          - "'TRUNCATE' in result_table1.stdout"
          - "'REFERENCES' in result_table1.stdout"
          - "'TRIGGER' in result_table1.stdout"
          - "result_table2.stdout_lines[-1] == '(7 rows)'"
          - "'INSERT' in result_table2.stdout"
          - "'SELECT' in result_table2.stdout"
          - "'UPDATE' in result_table2.stdout"
          - "'DELETE' in result_table2.stdout"
          - "'TRUNCATE' in result_table2.stdout"
          - "'REFERENCES' in result_table2.stdout"
          - "'TRIGGER' in result_table2.stdout"

    #
    # Cleanup
    #
    - name: Cleanup db
      postgresql_db:
        name: "ansible_test"
        state: "absent"

    - name: Check that database was destroyed
      shell: echo "select datname from pg_database where datname = 'ansible_test';" | psql
      register: result

    - assert:
        that:
          - "result.stdout_lines[-1] == '(0 rows)'"

    - name: Cleanup test user
      postgresql_user:
        name: "ansible_user1"
        state: 'absent'

    - name: Check that they were removed
      shell: echo "select * from pg_user where usename='ansible_user1';" | psql
      register: result

    - assert:
        that:
          - "result.stdout_lines[-1] == '(0 rows)'"

- hosts: localhost
  tasks:
    #
    # Test login_user functionality
    #
    - name: Create a user to test login module parameters
      sudo: True
      sudo_user: postgres
      postgresql_user:
        name: "ansible_user1"
        state: "present"
        encrypted: 'no'
        password: "password"
        role_attr_flags: "CREATEDB,LOGIN,CREATEROLE"

    - name: Create db
      postgresql_db:
        name: "ansible_test"
        state: "present"
        login_user: "ansible_user1"
        login_password: "password"
        login_host: "localhost"

    - name: Check that database created
      sudo: True
      sudo_user: postgres
      shell: echo "select datname from pg_database where datname = 'ansible_test';" | psql
      register: result

    - assert:
        that:
          - "result.stdout_lines[-1] == '(1 row)'"

    - name: Create a user
      postgresql_user:
        name: "ansible_user2"
        state: "present"
        encrypted: 'yes'
        password: "md55c8ccfd9d6711fc69a7eae647fc54f51"
        db: "ansible_test"
        login_user: "ansible_user1"
        login_password: "password"
        login_host: "localhost"

    - name: Check that they were created
      sudo: True
      sudo_user: postgres
      shell: echo "select * from pg_user where usename='ansible_user2';" | psql
      register: result

    - assert:
        that:
          - "result.stdout_lines[-1] == '(1 row)'"

    - name: Grant database privileges
      postgresql_privs:
        type: "database"
        state: "present"
        roles: "ansible_user2"
        privs: "CREATE,connect"
        objs: "ansible_test"
        db: "ansible_test"
        login: "ansible_user1"
        password: "password"
        host: "localhost"

    - name: Check that the user has the requested permissions (database)
      sudo: True
      sudo_user: postgres
      shell: echo "select datacl from pg_database where datname='ansible_test';" | psql ansible_test
      register: result_database

    - assert:
        that:
          - "result_database.stdout_lines[-1] == '(1 row)'"
          - "'ansible_user2=Cc' in result_database.stdout"

    - name: Remove user
      postgresql_user:
        name: "ansible_user2"
        state: 'absent'
        priv: "ALL"
        db: "ansible_test"
        login_user: "ansible_user1"
        login_password: "password"
        login_host: "localhost"

    - name: Check that they were removed
      sudo: True
      sudo_user: postgres
      shell: echo "select * from pg_user where usename='ansible_user2';" | psql
      register: result

    - assert:
        that:
          - "result.stdout_lines[-1] == '(0 rows)'"

    - name: Destroy DB
      postgresql_db:
        state: absent
        name: ansible_test
        login_user: "ansible_user1"
        login_password: "password"
        login_host: "localhost"

    - name: Check that database was destroyed
      sudo: True
      sudo_user: postgres
      shell: echo "select datname from pg_database where datname = 'ansible_test';" | psql
      register: result

    - assert:
        that:
          - "result.stdout_lines[-1] == '(0 rows)'"

    #
    # Cleanup
    #
    - name: Cleanup test user
      sudo: True
      sudo_user: postgres
      postgresql_user:
        name: "ansible_user1"
        state: 'absent'

    - name: Check that they were removed
      sudo: True
      sudo_user: postgres
      shell: echo "select * from pg_user where usename='ansible_user1';" | psql
      register: result

    - assert:
        that:
          - "result.stdout_lines[-1] == '(0 rows)'"