Re: Separate DDL and SQL users

No, it's working as expected. ALTER DEFAULT PRIVILEGES doesn't change the
default privileges for every object created by anyone in a given schema. It
changes the default privileges for objects created by a given role. If you
don't give a role, then the default privileges are changed for the role
that's currently logged in.

We've managed this by having all DDL (object creation and maintenance) done
by a specific role. We usually make that role the owner of everything as
well.

Keith

On Fri, Mar 11, 2016 at 9:27 AM, Rob Emery <re-pgsql(at)codeweavers(dot)net> wrote:

> Hi Thomas
>
> That sounds actually ideal; I can create the schema as a superuser and
> switch the owner to re_migration.
>
> From your suggestion, I've actually just tried that, and I was finding
> that doing:
>
> ALTER DEFAULT PRIVILEGES
> IN SCHEMA new_schema
> GRANT SELECT ON TABLES TO live_application;
>
> didn't seem to work, a table when created wasn't readable by
> live_application.
>
> Although when I switched it to :
>
> ALTER DEFAULT PRIVILEGES
> FOR ROLE re_migration
> IN SCHEMA new_schema
> GRANT SELECT ON TABLES TO live_application;
>
> it seemed to work!? This feels really odd to me, I'd expect the first one
> to apply to everyone. Unless I'm totally misinterpreting that behaviour?
>
> Thanks,
> Rob
>
> On 11 March 2016 at 13:08, Thomas Kellerer <spam_eater(at)gmx(dot)net> wrote:
>
>> Rob Emery schrieb am 11.03.2016 um 12:18:
>> > 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?
>>
>> You can set default privileges for a schema:
>> http://www.postgresql.org/docs/current/static/sql-alterdefaultprivileges.html
>>
>> If you do that, every object that is created in the schema is
>> automatically assigned those default privileges.
>>
>> So you only need to do do once, after you create a new schema, e.g.
>>
>> as re_migration do:
>>
>> create schema dbo;
>> grant usage on dbo to life_application;
>> alter default privileges in schema dbo grant
>> select,insert,update,delete on tables to live_application;
>> alter default privileges in schema dbo grant usage,select,update on
>> sequences to live_application;
>>
>> Of course the re_migration role needs to have the privileges to create a
>> schema.
>>
>> Thomas
>>
>>
>>
>>
>> --
>> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin
>>
>> --
>> <http://www.postgresql.org/mailpref/pgsql-admin>
>> Robert Emery
>> Infrastructure Director
>>
>> E: <http://www.postgresql.org/mailpref/pgsql-admin>
>> robertemery(at)codeweavers(dot)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(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>
>