From: Samed YILDIRIM <samed@reddoc.net>
To: pgsql-admin <pgsql-admin@postgresql.org>
To: JC Putter <jcputter@gmail.com>
Subject: Re: Roles and Privileges
Date: Thu, 25 May 2017 17:01:13 +0300
Message-ID: <1749171495720873@web27o.yandex.ru> (raw)
In-Reply-To: <CAKKrXOnrL85rdEA6UZWLi5aRXDgU8MRtexOuX-OzYM7Y=E2T5w@mail.gmail.com>
References: <CAKKrXOnrL85rdEA6UZWLi5aRXDgU8MRtexOuX-OzYM7Y=E2T5w@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
<div><div>Hi JC,</div><div> </div><div>I try creating a test environment like your question. I guess adapting following scenario to your environment solves your problem.</div><div> </div><blockquote><div>Create roles (users and group)</div><div>CREATE ROLE ahmet LOGIN;</div><div>CREATE ROLE samed LOGIN;</div><div>CREATE ROLE mail_list_group;</div></blockquote><blockquote><div> </div><div>GRANT mail_list_group TO ahmet;</div><div>GRANT mail_list_group TO samed;</div></blockquote><div> </div><div>Create schema</div><blockquote><div>CREATE SCHEMA mail_list</div><div> AUTHORIZATION mail_list_group;</div></blockquote><div> </div><div>Alter default permissions for users and schema</div><blockquote><div>ALTER DEFAULT PRIVILEGES FOR ROLE samed IN SCHEMA mail_list</div><div> GRANT ALL ON TABLES</div><div> TO mail_list_group WITH GRANT OPTION;</div><div>ALTER DEFAULT PRIVILEGES FOR ROLE samed IN SCHEMA mail_list</div><div> GRANT ALL ON SEQUENCES</div><div> TO mail_list_group WITH GRANT OPTION;</div><div>ALTER DEFAULT PRIVILEGES FOR ROLE samed IN SCHEMA mail_list</div><div> GRANT ALL ON FUNCTIONS</div><div> TO mail_list_group WITH GRANT OPTION;</div><div> </div><div>ALTER DEFAULT PRIVILEGES FOR ROLE ahmet IN SCHEMA mail_list</div><div> GRANT ALL ON TABLES</div><div> TO mail_list_group WITH GRANT OPTION;</div><div>ALTER DEFAULT PRIVILEGES FOR ROLE ahmet IN SCHEMA mail_list</div><div> GRANT ALL ON SEQUENCES</div><div> TO mail_list_group WITH GRANT OPTION;</div><div>ALTER DEFAULT PRIVILEGES FOR ROLE ahmet IN SCHEMA mail_list</div><div> GRANT ALL ON FUNCTIONS</div><div> TO mail_list_group WITH GRANT OPTION;</div></blockquote></div><div> </div><div> </div><div>Create table as samed and insert dumy data.</div><div><blockquote><div>atp=> create table mail_list.t1 (c1 int);</div><div>CREATE TABLE</div><div>atp=> insert into t1 select generate_series(1,100);</div><div>INSERT 0 100</div></blockquote><div> </div><div>Select table as ahmet</div><blockquote><div><div>atp=> select * from mail_list.t1 limit 5;</div><div> c1</div><div>----</div><div> 1</div><div> 2</div><div> 3</div><div> 4</div><div> 5</div><div>(5 rows)</div></div></blockquote></div><div> <div>Drop table as ahmet</div><div><blockquote><div>atp=> drop table mail_list.t1;</div><div>DROP TABLE</div></blockquote></div><div> </div><div>Best regards.</div><div>Samed YILDIRIM</div><div> </div><div> </div><div> </div><div>25.05.2017, 13:22, "JC Putter" <jcputter@gmail.com>:</div><blockquote type="cite"><div>Hi,<div> </div><div>I would like to setup a new schema on an existing database and grant a set of users full control to the schema.</div><div> </div><div>I've done the following and would appreciate help as I'm not getting the behavior I was hoping for.</div><div> </div><div>I create a new group role and added members to the role then granted the role all privileges to the schema. </div><div> </div><div>When users create tables the table owner it set to the user instead of the role and I have to grant each newly created table before other members can access it. </div><div> </div><div>I've altered the default privileges however it seems to not have an effect. Someone pointed out that users would need to SET ROLE before creating tables, is this the only possible way or it there another way to achieve this?</div><div> </div><div>Thank you in advance </div></div></blockquote></div>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: samed@reddoc.net, jcputter@gmail.com
Subject: Re: Roles and Privileges
In-Reply-To: <1749171495720873@web27o.yandex.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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