CODEWITHSUNDEEP
postgresqlansible
Pierre Brasseau
Pierre Brasseau

Reputation: 259

Using Ansible's postgresql_privs to lock down Postgres public schema

I want to lock down the public schema in a postgres database using Ansible. Using psql in a newly created postgres install:

postgres=# REVOKE CREATE ON SCHEMA public FROM PUBLIC;
REVOKE
postgres=# REVOKE USAGE ON SCHEMA public FROM PUBLIC;
REVOKE

works first time.

Using Ansible though I'm getting various errors.

- 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

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 db: parameter is mandatory with postgresql_privs so I solved that by creating BOB first.

My latest effort (the above Ansible stanza) gets me

fatal: [hostname.net]: FAILED! => {"changed": false, "failed": true, "msg": "Invalid privileges specified: frozenset([' USAGE'])"}

I've went through the Ansible documentation and played around with the various parameters but no luck.

Thinking that maybe Invalid privileges specified was something to do with the user that owns the BOB database I used the above stanza with an additional line

login: fred

(ie the user that I made the owner of database BOB) but that also didn't work:

fatal: [hostname.net]: FAILED! => {"changed": false, "failed": true, "msg": "Could not connect to database: FATAL: Peer authentication failed for user \"fred\"\n"}

Upvotes: 1

Views: 1780

Answers (1)

Kostiantyn Nemchenko
Kostiantyn Nemchenko

Reputation: 41

Remove extra whitespace in parameter privs to solve the 1st problem. Try the following

privs: CREATE,USAGE

To solve the second problem just specify the parameter host explicitly. For example:

host: 127.0.0.1

Or fix your pg_hba.conf file. According to the official PostgreSQL docs on auth-methods:

Peer Authentication

The peer authentication method works by obtaining the client's operating system user name from the kernel and using it as the allowed database user name (with optional user name mapping). This method is only supported on local connections.

Upvotes: 1

Related Questions