Re: Separate DDL and SQL users

[Helio Campos Mello de Andrade <helio.campos@xxxxxxxxx>]

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@xxxxxxxxxxxxxxx>:

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

Codeweavers 

​

March

 Newsletter

 l 

Codeweavers' Digital Marketing Conference

The launch of the stats that will help you sell more cars

Phone: 0800 021 0888   Email: contactus@xxxxxxxxxxxxxxx
Codeweavers Ltd | Barn 4 | Dunston Business Village | Dunston | ST18 9AB
Registered in England and Wales No. 04092394 | VAT registration no. 974 9705 63