Permission denied to create role | Postgresql database plugin

[BillB]

Good morning everybody,

I have been searching for an answer but I didn't succeeded.I configured a database secret engine in Vault (v1.2.3).

Next I added a configuration for a postgresql plugin :

vault write database/config/gen_db_12 plugin_name="postgresql-database-plugin" connection_url="postgresql://{{username}}:{{password}}@xxxx.xxxxxxxxxxxx.xxx:5433/gen_db_12?sslmode=disable" allowed_roles="role" username="xxx" password="xxx"

Then I configured a role :

vault write database/roles/role db_name="gen_db_12" creation_statements="CREATE ROLE \"{{name}}\" WITH LOGIN PASSWORD '{{password}}' VALID UNTIL '{{expiration}}'; GRANT SELECT ON ALL TABLES IN SCHEMA public TO \"{{name}}\";" default_ttl="100h" max_ttl="320h"

Everything worked fine until here.

And here is what I got while trying to get my credentials for this role :

vault read database/creds/role

Error reading database/creds/role: Error making API request.

URL: GET http://127.0.0.1:8200/v1/database/creds/role
Code: 500. Errors:

* 1 error occurred:
        * pq: permission denied to create role

Could you help me to resolve this problem please ?

Thanks a lot

[Marc-Antoine ADELISE]

Hello BillB,

My understanding of your issue is that it's related to the permissions granted to the user you configure Vault with for creating the temporary users.

Otherwise said, the user that is mentioned in your connection_url .

I would suggest to log directly to the DB with this user and to try to launch the the creation statement directly.

It should fail.

In that case the administrator of the PG DB would have to grant the required permissions to that user so that Vault can use it and create the roles.

Hope it helps.

Marc-Antoine

[BillB]

Thank you Marc-Antoine,

I think that you are right, my user has not enough privileges to do that.

Could you please tell me which permissions/privileges it needs to create role on postgres ?

Thanks

[Marc-Antoine ADELISE]

I'm sorry I won't be able to give much help, mainly because I'm not a DBA, and I'm struggling a bit with this currently.

Maybe you should take a look to https://chartio.com/resources/tutorials/how-to-change-a-user-to-superuser-in-postgresql/ .

If someone can provide here additional information about how to provide the minimal set of permissions to be able to create roles in postgres, it would be very appreciated.

FYI, below, a list of valuable resources on the subject that will provide you with a better understanding of the roles functioning in PG:

https://aws.amazon.com/blogs/database/managing-postgresql-users-and-roles/

https://www.postgresql.org/docs/9.0/role-membership.html

Marc-Antoine

[Jason O'Donnell]

Hello,

To add privileges to roles in PostgreSQL, the following SQL can be used.  Note that you need elevated privileges (such as superuser) to do this:

If the role already exists:

ALTER ROLE <name of your role> CREATEROLE;

If the role doesn't exist:

CREATE ROLE <name of your role> LOGIN CREATEROLE;

Hope that helps.

-- 
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/3f2ee996-c948-4bda-a952-cacd8b2b75d6%40googlegroups.com.