Issue with postgres secret backend

[thanasis.t...@upstreamsystems.com]

We are facing an issue with roles in postgres. Vault is unable to drop the roles it creates in postgres after their expiration, 

since in postgres you have to revoke all the privileges of a role in order to drop it. Vault tries to drop the roles before revoking 

them and after a few attempts it stops. This results in a lot of "garbage" roles inside the database. 

The issue is documented only when the roles are given privileges in our application schemas. Simple roles with access only to 'public'

are dropped by vault with no problem.

The log file documents the following errors:

2016/10/27 11:07:44.146201 [ERROR] expire: failed to revoke lease: lease_id=postgresql/creds/super/447b0d64-e894-e6b7-59e3-26e5f0ad7bd3 error=failed to revoke entry: resp:(*logical.Response)(nil) err:pq: role "root-3aea8269-d0d3-a9b9-49d2-ccc729510d29" cannot be dropped because some objects depend on it

2016/10/27 11:10:24.190875 [ERROR] expire: failed to revoke lease: lease_id=postgresql/creds/super/447b0d64-e894-e6b7-59e3-26e5f0ad7bd3 error=failed to revoke entry: resp:(*logical.Response)(nil) err:pq: role "root-3aea8269-d0d3-a9b9-49d2-ccc729510d29" cannot be dropped because some objects depend on it

2016/10/27 11:15:44.191348 [ERROR] expire: maximum revoke attempts reached: lease_id=postgresql/creds/super/447b0d64-e894-e6b7-59e3-26e5f0ad7bd3

Is there anyway to solve this through vault? Maybe sending a revoke command before the drop command for the role?

[Vishal Nayak]

Hi Thanasis,

There is a beta undocumented field `revocation_sql` on the
postgresql's role creation endpoint. This option can be seen through
path-help if you run `vault path-help postgresql/roles/name` after
mounting postgresql.

If you are curious about similar problems others had and how this
field has addressed it, refer this:
https://github.com/hashicorp/vault/issues/1857.

Hope this helps!

Regards,
Vishal

> --
> This mailing list is governed under the HashiCorp Community Guidelines -
> https://www.hashicorp.com/community-guidelines.html. Behavior in violation
> of those guidelines may result in your removal from this mailing list.
>
> GitHub Issues: https://github.com/hashicorp/vault/issues
> IRC: #vault-tool on Freenode
> ---
> You received this message because you are subscribed to the Google Groups
> "Vault" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to vault-tool+...@googlegroups.com.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/vault-tool/7f7289ff-34d8-44c6-b80d-5cf84b03a12e%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.



--
vn

[Jeff Mitchell]

To add to what Vishal said, please do let us know if that field is working well for you. It isn't super public because it was added literally the day we cut 0.6.2 so we really had no time to get feedback, but knowing it's working as designed would be a big help.

Best,
Jeff

> email to vault-tool+unsubscribe@googlegroups.com.

> To view this discussion on the web visit
> https://groups.google.com/d/msgid/vault-tool/7f7289ff-34d8-44c6-b80d-5cf84b03a12e%40googlegroups.com.
> For more options, visit https://groups.google.com/d/optout.



--
vn

--
This mailing list is governed under the HashiCorp Community Guidelines - https://www.hashicorp.com/community-guidelines.html. Behavior in violation of those guidelines may result in your removal from this mailing list.

GitHub Issues: https://github.com/hashicorp/vault/issues
IRC: #vault-tool on Freenode
---
You received this message because you are subscribed to the Google Groups "Vault" group.

To unsubscribe from this group and stop receiving emails from it, send an email to vault-tool+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/vault-tool/CAFy0tpzX5X2nYtFk_MAXBvbSmpFU9yOjHMJBULDWfQEmZ--V0A%40mail.gmail.com.

[Thanasis Tsilingiris]

Hello guys and thanks for the quick response. Unfortunately it is not working for me. This is what I'm trying to do:

./vault write postgresql/config/lease lease=5m lease_max=24h

./vault write postgresql/roles/super2 \

sql="CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';select grant_access('{{name}}','super');"

revocation_sql="select revoke_access('{{name}}');"

I am creating roles that have specific privileges given to them by the function "grant_access('rolename','access_type')", which works fine. The function revoke_access('rolename')

is revoking all the privileges from the role so it can be dropped. The function works ok when i execute it by postgres client tools (pgadmin, psql) but not from vault. This function has to be executed right before the drop user command in order for the drop to work, so my question is this:

When exactly does the sql statement in the revocation_sql parameter is being executed??

[Jeff Mitchell]

Hi Thanasis,

If you specify revocation sql, it is the *only* revocation logic run.
So if you want to drop a user, you need to specify that in your
revocation sql.

Best,
Jeff

> --
> This mailing list is governed under the HashiCorp Community Guidelines -
> https://www.hashicorp.com/community-guidelines.html. Behavior in violation
> of those guidelines may result in your removal from this mailing list.
>
> GitHub Issues: https://github.com/hashicorp/vault/issues
> IRC: #vault-tool on Freenode
> ---
> You received this message because you are subscribed to the Google Groups
> "Vault" group.
> To unsubscribe from this group and stop receiving emails from it, send an

> email to vault-tool+...@googlegroups.com.

> To view this discussion on the web visit

> https://groups.google.com/d/msgid/vault-tool/d6023fcf-69ec-44df-ab5a-1d5139f5c1ee%40googlegroups.com.

[Thanasis Tsilingiris]

Jeff thanks again! I used the following:

./vault write postgresql/roles/super2 \

sql="CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}';select grant_access('{{name}}','super');" \

revocation_sql="select revoke_access('{{name}}');DROP user \"{{name}}\";"

It worked like a charm. The user was successfully dropped after 2 minutes.

[Jeff Mitchell]

Great!

> --
> This mailing list is governed under the HashiCorp Community Guidelines -
> https://www.hashicorp.com/community-guidelines.html. Behavior in violation
> of those guidelines may result in your removal from this mailing list.
>
> GitHub Issues: https://github.com/hashicorp/vault/issues
> IRC: #vault-tool on Freenode
> ---
> You received this message because you are subscribed to the Google Groups
> "Vault" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to vault-tool+...@googlegroups.com.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/vault-tool/6c2863e9-15b9-4636-b62c-22d91c4c23a6%40googlegroups.com.

[Thanasis Tsilingiris]

Hello guys again,

this option seems to exist only when you start the vault as dev server. 

In production mode, in which i use the official docker vault image , revocation_sql in the 

postgresql's role creation endpoint doesn't exist.

In both cases i use v0.6.2.

Any ideas?

[Jeff Mitchell]

I'll have to ask Docker to rebuild it as I think they ran afoul of an
issue we had purging our CDN.

In the meantime you can make your own container by using vault:0.6.2
as a base and simply popping in the final 0.6.2 binary.

Best,
Jeff

> --
> This mailing list is governed under the HashiCorp Community Guidelines -
> https://www.hashicorp.com/community-guidelines.html. Behavior in violation
> of those guidelines may result in your removal from this mailing list.
>
> GitHub Issues: https://github.com/hashicorp/vault/issues
> IRC: #vault-tool on Freenode
> ---
> You received this message because you are subscribed to the Google Groups
> "Vault" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to vault-tool+...@googlegroups.com.
> To view this discussion on the web visit

> https://groups.google.com/d/msgid/vault-tool/bc423579-714a-41b3-ae54-4fdd41e47102%40googlegroups.com.

[Jeff Mitchell]

https://github.com/docker-library/official-images/pull/2291 for completeness.

--Jeff