Re: Separate DDL and SQL users - Mailing list pgsql-admin
What about using schemas:
CREATE SCHEMA migrations;
GRANT CREATE, USAGE ON SCHEMA migration TO re_migration WITH GRANT OPTION;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA migration TO live_application;
- The difference between databases and schemas in postgres are few. (you can't execute queries between databases but you can do it between schemas).
Paz,
--
Helio Campos Mello de Andrade
http://training.linuxfoundation.org/certification/verify-linux-certifications (ID: LFCS-1500-0312-0100, Last name: Andrade)
2016-03-11 9:25 GMT-03:00 Robert Emery <robertemery@codeweavers.net>:
Hi Helio,
I've tried something similar however, I believe what you're suggesting
only works on a single table if I under stand correctly? for example:
GRANT SELECT, INSERT, UPDATE, DELETE ON migration TO live_application;
fails with the error:
ERROR: relation "migration" does not exist
SQL state: 42P01> http://training.linuxfoundation.org/certification/verify-linux-certifications
On 11/03/2016, Helio Campos Mello de Andrade <helio.campos@gmail.com> wrote:
> 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
>
> (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@codeweavers.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@codeweavers.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>
>>
>
--
Robert Emery
Infrastructure Director
E: robertemery@codeweavers.net | T: 01785 711633 | W: www.codeweavers.net
--
<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@codeweavers.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>
pgsql-admin by date: