pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedSeparate DDL and SQL users
8+ messages / 5 participants
[nested] [flat]
* Separate DDL and SQL users
@ 2016-03-11 11:18 Rob Emery <re-pgsql@codeweavers.net>
0 siblings, 2 replies; 8+ messages in thread
From: Rob Emery @ 2016-03-11 11:18 UTC (permalink / raw)
To: pgsql-admin
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_-56...;
Codeweavers
March
Newsletter
<http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...;
<http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...; l
*Codeweavers' Digital Marketing Conference
<http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...
The launch of the stats that will help you sell more cars
<http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...;
*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;
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Separate DDL and SQL users
@ 2016-03-11 12:12 Helio Campos Mello de Andrade <helio.campos@gmail.com>
parent: Rob Emery <re-pgsql@codeweavers.net>
1 sibling, 1 reply; 8+ messages in thread
From: Helio Campos Mello de Andrade @ 2016-03-11 12:12 UTC (permalink / raw)
To: pgsql-admin
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@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_-56...;
> Codeweavers
>
> March
> Newsletter
> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...;
>
> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...; l
>
> *Codeweavers' Digital Marketing Conference
> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...
>
> The launch of the stats that will help you sell more cars
> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...;
>
> *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;
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Separate DDL and SQL users
@ 2016-03-11 12:25 Robert Emery <robertemery@codeweavers.net>
parent: Helio Campos Mello de Andrade <helio.campos@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Robert Emery @ 2016-03-11 12:25 UTC (permalink / raw)
To: Helio Campos Mello de Andrade <helio.campos@gmail.com>; +Cc: pgsql-admin
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
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
>
> 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@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_-56...;
>> Codeweavers
>>
>> March
>> Newsletter
>> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...;
>>
>> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...;
>> l
>>
>> *Codeweavers' Digital Marketing Conference
>> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...
>>
>> The launch of the stats that will help you sell more cars
>> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...;
>>
>> *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_-56...;
Codeweavers
March
Newsletter
<http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...;
<http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...; l
*Codeweavers' Digital Marketing Conference
<http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...
The launch of the stats that will help you sell more cars
<http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...;
*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;
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Separate DDL and SQL users
@ 2016-03-11 12:36 Helio Campos Mello de Andrade <helio.campos@gmail.com>
parent: Robert Emery <robertemery@codeweavers.net>
0 siblings, 0 replies; 8+ messages in thread
From: Helio Campos Mello de Andrade @ 2016-03-11 12:36 UTC (permalink / raw)
To: Robert Emery <robertemery@codeweavers.net>; +Cc: 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)
http://www.expertrating.com/reports/transcript.aspx?transcriptid=1608144
http://www.expertrating.com/reports/transcript.aspx?transcriptid=2962390
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
>
> 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
> >
> >
> 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@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_-56...
> >
> >> Codeweavers
> >>
> >> March
> >> Newsletter
> >> <
> http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...
> >
> >>
> >> <
> http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...
> >
> >> l
> >>
> >> *Codeweavers' Digital Marketing Conference
> >> <
> http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...
> >*
> >>
> >> The launch of the stats that will help you sell more cars
> >> <
> http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...
> >
> >>
> >> *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_-56...
> >
> Codeweavers
>
> March
> Newsletter
> <
> http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...
> >
> <
> http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...;
> l
>
> *Codeweavers' Digital Marketing Conference
> <
> http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...
> >*
>
> The launch of the stats that will help you sell more cars
> <
> http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...
> >
>
> *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;
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Separate DDL and SQL users
@ 2016-03-11 13:08 Thomas Kellerer <spam_eater@gmx.net>
parent: Rob Emery <re-pgsql@codeweavers.net>
1 sibling, 1 reply; 8+ messages in thread
From: Thomas Kellerer @ 2016-03-11 13:08 UTC (permalink / raw)
To: pgsql-admin
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Separate DDL and SQL users
@ 2016-03-11 14:27 Rob Emery <re-pgsql@codeweavers.net>
parent: Thomas Kellerer <spam_eater@gmx.net>
0 siblings, 1 reply; 8+ messages in thread
From: Rob Emery @ 2016-03-11 14:27 UTC (permalink / raw)
To: pgsql-admin; +Cc: Thomas Kellerer <spam_eater@gmx.net>
Hi Thomas
That sounds actually ideal; I can create the schema as a superuser and
switch the owner to re_migration.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Separate DDL and SQL users
@ 2016-03-11 15:05 Keith <keith@keithf4.com>
parent: Rob Emery <re-pgsql@codeweavers.net>
0 siblings, 1 reply; 8+ messages in thread
From: Keith @ 2016-03-11 15:05 UTC (permalink / raw)
To: Rob Emery <re-pgsql@codeweavers.net>; +Cc: pgsql-admin; Thomas Kellerer <spam_eater@gmx.net>
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@codeweavers.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@gmx.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@postgresql.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@codeweavers.net | T: 01785 711633 | W: www.codeweavers.net
>>
>
> <https://www.codeweavers.net;
>
>
> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...;
> Codeweavers
>
> March
> Newsletter
> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...;
>
> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...; l
>
> *Codeweavers' Digital Marketing Conference
> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...
>
> The launch of the stats that will help you sell more cars
> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...;
>
> *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;
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Separate DDL and SQL users
@ 2016-03-12 17:47 Rob Emery <re-pgsql@codeweavers.net>
parent: Keith <keith@keithf4.com>
0 siblings, 0 replies; 8+ messages in thread
From: Rob Emery @ 2016-03-12 17:47 UTC (permalink / raw)
To: pgsql-admin; +Cc: Thomas Kellerer <spam_eater@gmx.net>; Keith <keith@keithf4.com>
Hi Keith,
Ah! That makes a lot more sense, thanks for the clarification; we've done
exactly the same now, we use one user for all schema changes who owns the
db and has its default permissions set to grant as appropriate over the
tables for the live applications.
Thanks for the help!
Rob
On 11 March 2016 at 15:05, Keith <keith@keithf4.com> wrote:
> 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@codeweavers.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@gmx.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@postgresql.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@codeweavers.net | T: 01785 711633 | W: www.codeweavers.net
>>>
>>
>> <https://www.codeweavers.net;
>>
>>
>> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...;
>> Codeweavers
>>
>> March
>> Newsletter
>> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...;
>>
>> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...; l
>>
>> *Codeweavers' Digital Marketing Conference
>> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...
>>
>> The launch of the stats that will help you sell more cars
>> <http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...;
>>
>> *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_-56...;
Codeweavers
March
Newsletter
<http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...;
<http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...; l
*Codeweavers' Digital Marketing Conference
<http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...
The launch of the stats that will help you sell more cars
<http://t.sidekickopen42.com/e1t/c/5/f18dQhb0S7lC8dDMPbW2n0x6l2B9nMJN7t5XX4QBhKzW64Js984WrKTYVd0r_-56...;
*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;
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2016-03-12 17:47 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2016-03-11 11:18 Separate DDL and SQL users Rob Emery <re-pgsql@codeweavers.net>
2016-03-11 12:12 ` Helio Campos Mello de Andrade <helio.campos@gmail.com>
2016-03-11 12:25 ` Robert Emery <robertemery@codeweavers.net>
2016-03-11 12:36 ` Helio Campos Mello de Andrade <helio.campos@gmail.com>
2016-03-11 13:08 ` Thomas Kellerer <spam_eater@gmx.net>
2016-03-11 14:27 ` Rob Emery <re-pgsql@codeweavers.net>
2016-03-11 15:05 ` Keith <keith@keithf4.com>
2016-03-12 17:47 ` Rob Emery <re-pgsql@codeweavers.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox