Add role for postgres profile

[mpcogito]

Describe the feature

The postgres profile has an optional search_path configuration to override the search_path. I would like an optional role configuration to override the user. It would use the set role command before any operations.

This would be useful because objects would be created with the correct ownership and permissions, instead of needing hooks at the end to grant ownership and permissions to created objects.

Describe alternatives you've considered

Grant ownership and permissions via hooks.

Additional context

This feature is only relevant to Postgres.

Who will this benefit?

This will benefit anyone using role-based access control in Postgres.

For example, I have three roles in my data warehouse: dwadmin, dwloader, and dwclient. Each role has multiple users, and a user can have multiple roles granted to them. When operating on the data warehouse, first the user sets their role, and then performs the operation with appropriate permissions.

Currently, dbt runs as a user and has to grant ownership and permissions to the roles at the end of the run. I would like for dbt to run as a given role instead, so ownership and permissions are correct when the objects are created.

[drewbanin]

This is a cool idea @mpcogito. We don't typically use postgres as an analytical database, so I'm not deeply familiar with role-based access control on pg. Do you think this is as easy as running set role ... every time a connection is opened? I imagine this would be more tractable to support as a profile-level config, not as a model-level config. Does that sound appropriate to you?

[mpcogito]

@drewbanin That sounds right to me. I think the best time is just after the connection is opened in https://github.com/fishtown-analytics/dbt/blob/e51c942e91a94936f68f2965963d3b46f1257658/plugins/postgres/dbt/adapters/postgres/connections.py#L100

It should be handled as an optional config.

I think this would work for Redshift as well since its permission model is similar to Postgres, but I never did get Redshift permissions working to my satisfaction!

Over the holidays I'll work on a PR for this.

[drewbanin]

Sounds good - thanks @mpcogito :)