Hashicorp vault dynamic postgres database credentials role revocation issue

Question

We are currently investigating Dynamic database credentials using postgres plugin with HC vault version v1.15.2

We are using below revocation statements:

REASSIGN OWNED BY "{{name}}" TO postgres;
DROP ROLE IF EXISTS "{{name}}";

As part of testing we have observed below 2 patterns:

Pattern-1 Role ID’s appearing in postgres db logs for REASSIGN OWNED doesn’t seems to be appearing in list of user’s table.

e.g: the below line for id: v-kubernet-db-app-XcLvJhE3JMusXMbYcPQn-1708078911"appears in the log but not in the list of users.

REASSIGN OWNED BY "v-kubernet-db-app-XcLvJhE3JMusXMbYcPQn-1708078911" TO postgres

BUT there is no trace or log for DROP ROLE against the above role ID if we assume that that role has been deleted?

So pattern-1 observation says that the role v-kubernet-db-app-XcLvJhE3JMusXMbYcPQn-1708078911 doesn’t exist in the users list. i.e. role is dropped however, the DROP ROLE statement is not appearing in the postgres sql log for v-kubernet-db-app-XcLvJhE3JMusXMbYcPQn-1708078911

Pattern-2 ID’s from the list of users table seems to be appearing in the logs for DROP ROLE statement and it fails with error role "v-kubernet-db-app-tCBmXmAHsSQwhRlEhxLG-1708082378" cannot be dropped because some objects depend on it.

e.g: For Role id v-kubernet-db-app-lfV4xdqM7bN6uTm3wIdh-1708083414 we have below line appearing the logs:

role "v-kubernet-db-app-lfV4xdqM7bN6uTm3wIdh-1708083414" cannot be dropped because some objects depend on it

So pattern-2 observation says that the DROP ROLE has failed for some ID’s due to some dependency on objects.

Any idea what could be wrong here? Also any suggestion on what could be the best way to revoke roles from postgres without impacting applications connecting to the DB? Example of revocation statement would be helpful.

Answer 1

You cannot reassign in revocation_statements because those statements are executed in the maintenance database, which is most likely not where your user created objects.

What you can do is create generic roles in the database where you will create object and assign that role as the default role of the temporary vault users. We have 3 such roles: schema, application and readonly, and we modify the temporary users to use those roles by default. Using this, objects created are owned by the generic role and not the temporary user.

An example ansible role we use does this:

• Create the 3 roles
• Create the database
• Grant usage on public to all roles
• Create hashivault db role
  • Note the use of alter role "{{ '{{name}}' }}" set role "{{ database_name }}_{{ pg_role_type }}";

- name: create database roles
  community.postgresql.postgresql_user:
    name: "{{ pg_db_role }}"
    role_attr_flags: "NOLOGIN"
    login_host: "{{ database_host }}"
    login_unix_socket: "{{ database_socket | default(omit) }}"
    login_port: "{{ database_port }}"
    login_user: "{{ database_login_user }}"
    login_password: "{{ database_login_password }}"
    login_db: "{{ maintenance_database }}"
    ssl_mode: require
  loop:
    - "{{ database_name }}_application"
    - "{{ database_name }}_schema"
    - "{{ database_name }}_readonly"
  loop_control:
    loop_var: pg_db_role

- name: make sure the database exists
  community.postgresql.postgresql_db:
    name: "{{ database_name }}"
    encoding: "{{ database_encoding }}"
    lc_collate: "{{ database_locale }}.UTF-8"
    lc_ctype: "{{ database_locale }}.UTF-8"
    owner: "{{ database_name }}_schema"
    login_host: "{{ database_host }}"
    login_unix_socket: "{{ database_socket | default(omit) }}"
    login_port: "{{ database_port }}"
    login_user: "{{ database_login_user }}"
    login_password: "{{ database_login_password }}"
    maintenance_db: "{{ maintenance_database }}"
    template: "{{ database_template | default(omit) }}"

- name: grant usage on schema public to _application
  community.postgresql.postgresql_privs:
    db: "{{ database_name }}"
    privs: "usage"
    type: schema
    objs: "public"
    role: "{{ pg_db_role }}"
    login_host: "{{ database_host }}"
    login_unix_socket: "{{ database_socket | default(omit) }}"
    login_port: "{{ database_port }}"
    login_user: "{{ database_login_user }}"
    login_password: "{{ database_login_password }}"
  loop:
    - "{{ database_name }}_application"
    - "{{ database_name }}_schema"
    - "{{ database_name }}_readonly"
  loop_control:
    loop_var: pg_db_role

- name: Create hashicorp vault role
  become: false
  delegate_to: localhost
  ansible.legacy.hashivault_db_secret_engine_role:
    authtype: token
    token: "{{ hashi_vault_token | default(lookup('env', 'VAULT_TOKEN')) | mandatory }}"
    url: "{{ hashi_vault_address }}"
    name: "{{ hashi_vault_role_name }}"
    db_name: "{{ hashi_vault_connection_name }}"
    mount_point: "{{ hashi_vault_mount_point }}"
    creation_statements: >-
      create role "{{ '{{name}}' }}"
      with login password '{{ '{{password}}' }}'
      valid until '{{ '{{expiration}}' }}'
      in role "{{ database_name }}_{{ pg_role_type }}";
      grant connect on database "{{ database_name }}" to "{{ '{{name}}' }}";
      alter role "{{ '{{name}}' }}" set role "{{ database_name }}_{{ pg_role_type }}";
    revocation_statements: >-
      revoke connect on database "{{ database_name }}" from "{{ '{{name}}' }}";
      revoke "{{ database_name }}_{{ pg_role_type }}" from "{{ '{{name}}' }}";
      -- disable login in case the drop fails
      alter user "{{ '{{name}}' }}" with nologin;
      drop role "{{ '{{name}}' }}";
    renew_statements: omit
    rollback_statements: omit
    token_ttl: "{{ hashi_vault_role_token_ttl }}"
  loop:
    - "application"
    - "schema"
    - "readonly"
  loop_control:
    loop_var: pg_role_type