Re: Separate DDL and SQL users - Mailing list 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;
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;
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;
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;
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;
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)
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,
RobPhone: 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
pgsql-admin by date: