From: | Rob Emery <re-pgsql(at)codeweavers(dot)net> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Cc: | Thomas Kellerer <spam_eater(at)gmx(dot)net> |
Subject: | Re: Separate DDL and SQL users |
Date: | 2016-03-11 14:27:10 |
Message-ID: | CAPCETptetcsihLY9F0E7sPQo+5==2mgpeiXfsyX6vG9FO5AT1g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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>
From | Date | Subject | |
---|---|---|---|
Next Message | Keith | 2016-03-11 15:05:26 | Re: Separate DDL and SQL users |
Previous Message | Thomas Kellerer | 2016-03-11 13:08:27 | Re: Separate DDL and SQL users |