title: Using Ansible's postgresql_privs to lock down Postgres public schema
body: <p>I want to lock down the public schema in a postgres database using Ansible.
  Using psql in a newly created postgres install:</p>
  
  <pre><code>postgres=# REVOKE CREATE ON SCHEMA public FROM PUBLIC;
  REVOKE
  postgres=# REVOKE USAGE ON SCHEMA public FROM PUBLIC;
  REVOKE
  </code></pre>
  
  <p>works first time.</p>
  
  <p>Using Ansible though I'm getting various errors.</p>
  
  <pre><code>- name: Lock down PUBLIC schema
    postgresql_privs:
        db: BOB
        schema: public
        state: absent
        privs: CREATE, USAGE
        objs: public
        role: PUBLIC
    become: true
    become_user: postgres
  </code></pre>
  
  <p>The first error complained that the database BOB didn't exist - it didn't, I hadn't created it yet - this doesn't seem to bother psql however. The <em>db:</em> parameter is mandatory with <em><code>postgresql_privs</code></em> so I solved that by creating BOB first. </p>
  
  <p>My latest effort (the above Ansible stanza) gets me </p>
  
  <blockquote>
    <p>fatal: [hostname.net]: FAILED! => {"changed": false, "failed": true, "msg": "Invalid privileges specified: frozenset([' USAGE'])"}</p>
  </blockquote>
  
  <p>I've went through the <a href="https://docs.ansible.com/ansible/postgresql_privs_module.html" rel="nofollow noreferrer">Ansible documentation</a> and played around with the various parameters but no luck.</p>
  
  <p>Thinking that maybe <em>Invalid privileges specified</em> was something to do with the user that owns the BOB database I used the above stanza with an additional line</p>
  
  <pre><code>login: fred
  </code></pre>
  
  <p>(ie the user that I made the owner of database BOB) but that also didn't work:     </p>
  
  <blockquote>
    <p>fatal: [hostname.net]: FAILED! => {"changed": false, "failed": true, "msg": "Could not connect to database: FATAL:  Peer authentication failed for user \"fred\"\n"}</p>
  </blockquote>