Re: Separate DDL and SQL users

Hi Rob,

- Does this helps?

THE RE_MIGRATION_ROLE
=====================

# Creating the role
## Create the role with your permissions
CREATE ROLE re_migration WITH optional_permissions;
## Grant role ALL permissions in the database migration
GRANT ALL ON migration TO re_migration;
## Make everyone in this role to automaticaly have all the role's privileges
ALTER ROLE re_migration INHERIT;

## Create the user
CREATE USER migration_user1 ....;
GRANT re_migration TO migration_user1;

THE LIVE_APPLICATION_ROLE
=========================

# Creating the role
## Create the role with your permissions
CREATE ROLE live_application WITH optional_permissions;
## Grant role SELECT, INSERT, UPDATE, DELETE permissions in the database
migration
GRANT SELECT, INSERT, UPDATE, DELETE ON migration TO live_application;
## Make everyone in this role to automaticaly have all the role's privileges
ALTER ROLE live_application INHERIT;

## Create the user
CREATE USER live_app_user1 ....;
GRANT live_application TO live_app_user1;

Regards,

--
Helio Campos Mello de Andrade

http://training.linuxfoundation.org/certification/verify-linux-certifications
(ID: LFCS-1500-0312-0100, Last name: Andrade)
http://www.expertrating.com/reports/transcript.aspx?transcriptid=1608144
http://www.expertrating.com/reports/transcript.aspx?transcriptid=2962390

2016-03-11 8:18 GMT-03:00 Rob Emery <re-pgsql(at)codeweavers(dot)net>:

> Hello All,
>
> So we're looking at automating our migrations against PG for the
> developers so that it's simple enough for them and no maintenance for me.
> I'm struggling to find a role/permissions structure that works; we've come
> from SQL Server so we're used to having DBRoles.
>
> So I want the re_migration role to be able to create tables, sequences etc
> and grant to other users etc; yet I want the live_application role to be
> able to select,insert,update,delete.
>
> It seems that the only real solution here is to have the db owned by
> re_migration, then in every migration GRANT SELECT,INSERT,UPDATE,DELETE to
> the live_application role?
>
> Previously I've always set the owner on tables etc to 'postgres' so that
> it's not bound to any special user.
>
> Any suggestions?
>
> Thanks,
> Rob
>
> <https://www.codeweavers.net>
>
>
> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56dzZCf1d8gHj02?t=http%3A%2F%2Fus6.campaign-archive1.com%2F%3Fu%3Ddb934935d4174e46e9063c8c4%26id%3De34a33f79a&si=4882959321006080&pi=04b7a1ae-3680-4ca6-d2bb-74bc3d2d6fc0>
> Codeweavers
> ​
> March
> Newsletter
> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56dzZCf1d8gHj02?t=http%3A%2F%2Fus6.campaign-archive2.com%2F%3Fu%3Ddb934935d4174e46e9063c8c4%26id%3D8939a8a212&si=4882959321006080&pi=04b7a1ae-3680-4ca6-d2bb-74bc3d2d6fc0>
>
> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56dzZCf1d8gHj02?t=http%3A%2F%2Fus6.campaign-archive2.com%2F%3Fu%3Ddb934935d4174e46e9063c8c4%26id%3D8939a8a212&si=4882959321006080&pi=04b7a1ae-3680-4ca6-d2bb-74bc3d2d6fc0> l
>
> *Codeweavers' Digital Marketing Conference
> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56dzZCf1d8gHj02?t=https%3A%2F%2Fcodeweavers.net%2Fcodeweavers-digital-marketing-conference&si=4882959321006080&pi=04b7a1ae-3680-4ca6-d2bb-74bc3d2d6fc0>*
>
> The launch of the stats that will help you sell more cars
> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56dzZCf1d8gHj02?t=https%3A%2F%2Fcodeweavers.net%2Fcompany-blog%2Fthe-launch-of-the-stats-that-will-help-you-sell-more-cars&si=4882959321006080&pi=04b7a1ae-3680-4ca6-d2bb-74bc3d2d6fc0>
>
> *Phone:* 0800 021 0888 * Email: *contactus(at)codeweavers(dot)net
> *Codeweavers Ltd* | Barn 4 | Dunston Business Village | Dunston | ST18 9AB
> Registered in England and Wales No. 04092394 | VAT registration no. 974
> 9705 63
>
> <https://www.linkedin.com/company/codeweavers-limited>
> <https://vimeo.com/codeweaversltd>
> <https://plus.google.com/b/105942302039373248738/+CodeweaversNet/posts>
> <https://twitter.com/CodeweaversTeam?lang=en-gb>
>