From rodrigo.sakai@zanthus.com.br Wed Jul  26 13:49:54 2006
X-Original-To: pgsql-admin-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id 3107D9FA6B2
	for <pgsql-admin-postgresql.org@postgresql.org>;
	Wed, 26 Jul 2006 10:49:54 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 72447-04 for <pgsql-admin-postgresql.org@postgresql.org>;
	Wed, 26 Jul 2006 10:49:42 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from mail.zanthus.com.br (mail.zanthus.com.br [200.212.65.85])
	by postgresql.org (Postfix) with ESMTP id CF9499FA666
	for <pgsql-admin@postgresql.org>; Wed, 26 Jul 2006 10:49:41 -0300 (ADT)
Received: from cprsakai (unknown [192.168.0.71])
	(using TLSv1 with cipher RC4-MD5 (128/128 bits))
	(No client certificate requested)
	by mail.zanthus.com.br (Postfix) with ESMTP id 311A5241B1
	for <pgsql-admin@postgresql.org>; Wed, 26 Jul 2006 10:51:27 -0300 (BRT)
Reply-To: <rodrigo.sakai@zanthus.com.br>
From: "Rodrigo Sakai" <rodrigo.sakai@zanthus.com.br>
To: <pgsql-admin@postgresql.org>
Subject: ROLES and PRIVILEGES
Date: Wed, 26 Jul 2006 10:50:48 -0300
Organization: Zanthus
Message-ID: <002901c6b0ba$805f6960$4700a8c0@TREEZANTHUS>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_NextPart_000_002A_01C6B0A1.5B147B50"
X-Mailer: Microsoft Office Outlook 11
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1807
Thread-Index: AcawuoAt9f4na2YjQ56cKXrWsU+cVA==
X-Zanthus-MailScanner: Found to be clean
X-Zanthus-MailScanner-From: rodrigo.sakai@zanthus.com.br
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE
X-Spam-Level: 
X-Archive-Number: 200607/220
X-Sequence-Number: 22466

This is a multi-part message in MIME format.

------=_NextPart_000_002A_01C6B0A1.5B147B50
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit

 

  Hi all,

 

  Sorry if this is an already answered question. I'm having some troubles to
set privileges to a user (role). I have to roles, postgres (the superuser)
and roger, I created a database 'test' with postgres role and I want that
roger have only SELECT, INSERT, UPDATE and DELETE on that database, so I
tried:

 

  REVOKE ALL ON DATABASE test FROM roger;

 

  GRANT SELECT, UPDATE, INSERT, DELETE ON <table_name> TO roger;

 

  But the role roger can still create objects in this database and I don't
want it!

 

  Any HELP???

 

  Thanks!!!

 

  

 

 

 

Rodrigo Sakai

Administrador de Banco de Dados

*(11) 3750-7075

* rodrigo.sakai@zanthus.com.br

 

 


------=_NextPart_000_002A_01C6B0A1.5B147B50
Content-Type: text/html;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:st1=3D"urn:schemas-microsoft-com:office:smarttags" =
xmlns=3D"http://www.w3.org/TR/REC-html40">

<head>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 11 (filtered medium)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]--><o:SmartTagType
 namespaceuri=3D"urn:schemas-microsoft-com:office:smarttags" =
name=3D"PersonName"/>
<!--[if !mso]>
<style>
st1\:*{behavior:url(#default#ieooui) }
</style>
<![endif]-->
<style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:Wingdings;
	panose-1:5 0 0 0 0 0 0 0 0 0;}
@font-face
	{font-family:Webdings;
	panose-1:5 3 1 2 1 5 9 6 7 3;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0cm;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
span.EstiloDeEmail17
	{mso-style-type:personal-compose;
	font-family:Arial;
	color:windowtext;}
@page Section1
	{size:595.3pt 841.9pt;
	margin:70.85pt 3.0cm 70.85pt 3.0cm;}
div.Section1
	{page:Section1;}
-->
</style>

</head>

<body lang=3DPT-BR link=3Dblue vlink=3Dpurple>

<div class=3DSection1>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>&nbsp; Hi all,<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span lang=3DEN-US =
style=3D'font-size:
10.0pt;font-family:Arial'>&nbsp; Sorry if this is an already answered =
question. I&#8217;m
having some troubles to set privileges to a user (role). I have to =
roles,
postgres (the superuser) and roger, I created a database =
&#8216;test&#8217; with
postgres role and I want that roger have only SELECT, INSERT, UPDATE and =
DELETE
on that database, so I tried:<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span lang=3DEN-US =
style=3D'font-size:
10.0pt;font-family:Arial'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span lang=3DEN-US =
style=3D'font-size:
10.0pt;font-family:Arial'>&nbsp; REVOKE ALL ON DATABASE test FROM =
roger;<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span lang=3DEN-US =
style=3D'font-size:
10.0pt;font-family:Arial'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span lang=3DEN-US =
style=3D'font-size:
10.0pt;font-family:Arial'>&nbsp; GRANT SELECT, UPDATE, INSERT, DELETE ON =
&lt;table_name&gt;
TO roger;<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span lang=3DEN-US =
style=3D'font-size:
10.0pt;font-family:Arial'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span lang=3DEN-US =
style=3D'font-size:
10.0pt;font-family:Arial'>&nbsp; But the role roger can still create =
objects in this
database and I don&#8217;t want it!<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span lang=3DEN-US =
style=3D'font-size:
10.0pt;font-family:Arial'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span lang=3DEN-US =
style=3D'font-size:
10.0pt;font-family:Arial'>&nbsp; Any =
HELP???<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span lang=3DEN-US =
style=3D'font-size:
10.0pt;font-family:Arial'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span lang=3DEN-US =
style=3D'font-size:
10.0pt;font-family:Arial'>&nbsp; Thanks!!!<o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span lang=3DEN-US =
style=3D'font-size:
10.0pt;font-family:Arial'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span lang=3DEN-US =
style=3D'font-size:
10.0pt;font-family:Arial'>&nbsp; <o:p></o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span lang=3DEN-US =
style=3D'font-size:
10.0pt;font-family:Arial'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span lang=3DEN-US =
style=3D'font-size:
10.0pt;font-family:Arial'><o:p>&nbsp;</o:p></span></font></p>

<p class=3DMsoNormal><strong><b><font size=3D2 face=3DArial><span =
lang=3DEN-US
style=3D'font-size:10.0pt;font-family:Arial'><o:p>&nbsp;</o:p></span></fo=
nt></b></strong></p>

<p class=3DMsoNormal><st1:PersonName ProductID=3D"Rodrigo Sakai" =
w:st=3D"on"><strong><b><font
 size=3D3 face=3D"Times New Roman"><span =
style=3D'font-size:12.0pt'>Rodrigo =
Sakai</span></font></b></strong></st1:PersonName><o:p></o:p></p>

<div>

<p class=3DMsoNormal><font size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;
font-family:Arial'>Administrador de Banco de =
Dados</span></font><o:p></o:p></p>

</div>

<div>

<p class=3DMsoNormal><font size=3D2 face=3DWebdings><span =
style=3D'font-size:10.0pt;
font-family:Webdings'>&Eacute;</span></font><font size=3D2 =
face=3DArial><span
style=3D'font-size:10.0pt;font-family:Arial'>(11) =
3750-7075<o:p></o:p></span></font></p>

<p class=3DMsoNormal =
style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=3D2 face=3DWingdings><span =
style=3D'font-size:10.0pt;font-family:Wingdings'>*</span></font><font
size=3D2 face=3DArial><span =
style=3D'font-size:10.0pt;font-family:Arial'> <a
href=3D"mailto:rodrigo.sakai@zanthus.com.br">rodrigo.sakai@zanthus.com.br=
</a></span></font><o:p></o:p></p>

</div>

<div>

<p class=3DMsoNormal =
style=3D'mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><font
size=3D3 face=3D"Times New Roman"><span =
style=3D'font-size:12.0pt'><o:p></o:p></span></font></p>

</div>

<div>

<p class=3DMsoNormal><font size=3D3 face=3D"Times New Roman"><span =
style=3D'font-size:
12.0pt'>&nbsp;</span><o:p></o:p></font></p>

</div>

<p class=3DMsoNormal><font size=3D3 face=3D"Times New Roman"><span =
style=3D'font-size:
12.0pt'><o:p>&nbsp;</o:p></span></font></p>

</div>

</body>

</html>

------=_NextPart_000_002A_01C6B0A1.5B147B50--




From tgl@sss.pgh.pa.us Wed Jul  26 15:05:55 2006
X-Original-To: pgsql-admin-postgresql.org@postgresql.org
Received: from localhost (mx1.hub.org [200.46.208.251])
	by postgresql.org (Postfix) with ESMTP id C5FC49FA506
	for <pgsql-admin-postgresql.org@postgresql.org>;
	Wed, 26 Jul 2006 12:05:55 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024)
	with ESMTP id 79149-10 for <pgsql-admin-postgresql.org@postgresql.org>;
	Wed, 26 Jul 2006 12:05:45 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130])
	by postgresql.org (Postfix) with ESMTP id B5D3E9FA666
	for <pgsql-admin@postgresql.org>; Wed, 26 Jul 2006 12:05:15 -0300 (ADT)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
	by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k6QF5DXJ001983;
	Wed, 26 Jul 2006 11:05:13 -0400 (EDT)
To: rodrigo.sakai@zanthus.com.br
cc: pgsql-admin@postgresql.org
Subject: Re: ROLES and PRIVILEGES 
In-reply-to: <002901c6b0ba$805f6960$4700a8c0@TREEZANTHUS> 
References: <002901c6b0ba$805f6960$4700a8c0@TREEZANTHUS>
Comments: In-reply-to "Rodrigo Sakai" <rodrigo.sakai@zanthus.com.br>
	message dated "Wed, 26 Jul 2006 10:50:48 -0300"
Date: Wed, 26 Jul 2006 11:05:13 -0400
Message-ID: <1982.1153926313@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0.042 tagged_above=0 required=5 tests=AWL,
	SPF_HELO_PASS, SPF_PASS
X-Spam-Level: 
X-Archive-Number: 200607/221
X-Sequence-Number: 22467

"Rodrigo Sakai" <rodrigo.sakai@zanthus.com.br> writes:
>   But the role roger can still create objects in this database and I don't
> want it!

revoke create on the public schema ... database-level privileges are not
the issue here.  See the GRANT manual page.

			regards, tom lane



From walterc@indiana.edu Fri Jul  25 14:39:34 2008
Received: from localhost (unknown [200.46.204.183])
	by postgresql.org (Postfix) with ESMTP id D981B650465
	for <pgsql-admin-postgresql.org@postgresql.org>;
	Fri, 25 Jul 2008 11:39:34 -0300 (ADT)
Received: from postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 63508-02 for <pgsql-admin-postgresql.org@postgresql.org>;
	Fri, 25 Jul 2008 11:39:11 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from belushi.uits.indiana.edu (belushi.uits.indiana.edu
	[129.79.1.188]) by postgresql.org (Postfix) with ESMTP id CD0F765034C
	for <pgsql-admin@postgresql.org>; Fri, 25 Jul 2008 11:30:08 -0300 (ADT)
Received: from mail-relay.iu.edu (stjoseph.uits.indiana.edu [129.79.1.78])
	by belushi.uits.indiana.edu (8.14.2/8.13.8/IU Messaging Team) with
	ESMTP id m6PEU6Yq022306
	for <pgsql-admin@postgresql.org>; Fri, 25 Jul 2008 10:30:06 -0400
Received: from [129.79.36.69] (walter.slis.indiana.edu [129.79.36.69])
	(authenticated bits=0)
	by mail-relay.iu.edu (8.13.8/8.12.10/IUPO) with ESMTP id m6PEU4ni014592
	(version=TLSv1/SSLv3 cipher=AES128-SHA bits=128 verify=NO)
	for <pgsql-admin@postgresql.org>; Fri, 25 Jul 2008 10:30:06 -0400 (EDT)
Mime-Version: 1.0 (Apple Message framework v753)
Content-Transfer-Encoding: 7bit
Message-Id: <4E99090C-5EB8-473F-94D5-83E1362F30C7@indiana.edu>
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
To: pgsql-admin@postgresql.org
From: Carol Walter <walterc@indiana.edu>
Subject: Roles and privileges?
Date: Fri, 25 Jul 2008 10:30:04 -0400
X-Mailer: Apple Mail (2.753)
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200807/217
X-Sequence-Number: 29827

Greetings one and all,

I've been working a lot with roles and privileges lately.  I have  
some developers which I would like to give just temporary access to  
make tables and so on in databases.  There is a "temporary" privilege  
that can be granted to a database.  Does this work this way, or does  
a user have to have the ability to the create option specified on the  
user's role?

Is there a query I can run to find out which users have which roles?

What does the "references" privilege do?

Can I grant privileges on views to users just as I do on tables, or  
does having privileges to the underlying tables take care of that.   
I've gotten errors in the past when I've tried to grant privs on  
views, but I've done an upgrade since then.  I'm now on 8.2.3.

Thanks,
Carol 
  



From jcputter@gmail.com Thu May  25 10:22:28 2017
Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from
 <pgsql-admin-owner+M46689=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
	id 1dDpuW-0002FV-Rb
	for pgsql-admin@arkaria.postgresql.org; Thu, 25 May 2017 10:22:28 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.84_2)
	(envelope-from
 <pgsql-admin-owner+M46689=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
	id 1dDpuW-0000Ft-EC
	for pgsql-admin@arkaria.postgresql.org; Thu, 25 May 2017 10:22:28 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <jcputter@gmail.com>)
	id 1dDpsi-00052c-0o
	for pgsql-admin@postgresql.org; Thu, 25 May 2017 10:20:36 +0000
Received: from mail-qt0-x231.google.com ([2607:f8b0:400d:c0d::231])
	by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256)
	(Exim 4.84_2)
	(envelope-from <jcputter@gmail.com>)
	id 1dDpsb-0004H4-Gd
	for pgsql-admin@postgresql.org; Thu, 25 May 2017 10:20:34 +0000
Received: by mail-qt0-x231.google.com with SMTP id f55so176241702qta.3
        for <pgsql-admin@postgresql.org>;
 Thu, 25 May 2017 03:20:29 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=gmail.com; s=20161025;
        h=mime-version:from:date:message-id:subject:to;
        bh=x75B0tELbm7LBWRHTZPT07JrM9X7n0szVE5SDtyvKOA=;
        b=hOFFMT+i+yBOJAdR8TeKeT9pHlq5Yda6sXXnxWHkv/3Yj4pcu98PtML4Sy+h0BV2la
         e2RXsRYcGuByMQ29XcEMj9zTKbn9Hbl1NlfxT1DW0p8Lph7EM5WYWkHRNtI9ZUlqkEXX
         t3qLFcdOKtTBtjnsASCUO76LJQA9JBrataee84UV4fNRrBgvKi7YGiJWrhTtZ1iH/W4U
         YtcmvBnSNn4ijG+i2A4ZHzJhDE9OfnmsWOMNKcQRt1EkKrrT8lfjbnrWfCP6tnpFRGo4
         WT2bhBBxu8txqA/69rjCru7Zrqdb7n/ZMxDb+Eg3RGZr9tj/MqOi04t6TZjrDlltDHt5
         h1/w==
X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed;
        d=1e100.net; s=20161025;
        h=x-gm-message-state:mime-version:from:date:message-id:subject:to;
        bh=x75B0tELbm7LBWRHTZPT07JrM9X7n0szVE5SDtyvKOA=;
        b=gDgcYy2gv2ALMgCiM28fKNr2l6GsN2uXZvbQqmhLkSIgA0Ik0ChmMH7xZBMAuMhmSE
         hXcsVdysm7HLeDct9DABADgEL0oQy+pS1FErcqJnm906kja/DG8J0OmTFqX9aqu+/yJZ
         JmTaH+XWEd+PtQ7MYIpADDD6rEQLgz5jANVbl7ukLjmM0hchNP8hnkl99yRRjdjUpIdF
         UG54YtYIBbcOBCTO8/rNhdCXMIPaltwWBvOwCWHxBaVndRsy5swjzpcGniYuQWT7GOKm
         oQGLblYQm9vJUI30aALsRHRC4hRBuC7SuHFHRl+KgKIdJV27as3TDmzRfX4ODmlMuDrR
         bGDA==
X-Gm-Message-State: AODbwcCF4h/NJafzyr3+/gQlKq/ko1Vse15La3U/Vlmc8oBXC4zwE1Vp
	lYvwxGHpFmCSAkGPrYqRUrFniG/hxI5l
X-Received: by 10.237.36.151 with SMTP id t23mr40003174qtc.115.1495707628009;
 Thu, 25 May 2017 03:20:28 -0700 (PDT)
MIME-Version: 1.0
Received: by 10.12.180.15 with HTTP; Thu, 25 May 2017 03:20:27 -0700 (PDT)
From: JC Putter <jcputter@gmail.com>
Date: Thu, 25 May 2017 12:20:27 +0200
Message-ID: 
 <CAKKrXOnrL85rdEA6UZWLi5aRXDgU8MRtexOuX-OzYM7Y=E2T5w@mail.gmail.com>
Subject: Roles and Privileges
To: pgsql-admin@postgresql.org
Content-Type: multipart/alternative; boundary="001a113d7eb4db00b90550569160"
X-Pg-Spam-Score: -2.0 (--)
List-Archive: <http://archives.postgresql.org/pgsql-admin>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-admin.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@postgresql.org>
List-Post: <mailto:pgsql-admin@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-admin>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
X-Mailing-List: pgsql-admin
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

--001a113d7eb4db00b90550569160
Content-Type: text/plain; charset="UTF-8"

Hi,

I would like to setup a new schema on an existing database and grant a set
of users full control to the schema.

I've done the following and would appreciate help as I'm not getting the
behavior I was hoping for.

I create a new group role and added members to the role then granted the
role all privileges to the schema.

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.

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?

Thank you in advance

--001a113d7eb4db00b90550569160
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi,<div><br></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><br></div><div>I&#39;ve done the following and would appreciate=
 help as I&#39;m not getting the behavior I was hoping for.</div><div><br><=
/div><div>I create a new group role and added members to the role then gran=
ted the role all privileges to the schema.=C2=A0</div><div><br></div><div>W=
hen users create tables the table owner it set to the user instead of the r=
ole and I have to grant each newly created table before other members can a=
ccess it. =C2=A0</div><div><br></div><div>I&#39;ve altered the default priv=
ileges however it seems to not have an effect. Someone pointed out that use=
rs would need to SET ROLE before creating tables, is this the only possible=
 way or it there another way to achieve this?</div><div><br></div><div>Than=
k you in advance=C2=A0</div></div>

--001a113d7eb4db00b90550569160--




From samed@reddoc.net Thu May  25 14:01:24 2017
Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtp (Exim 4.84_2)
	(envelope-from
 <pgsql-admin-owner+M46690=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
	id 1dDtKO-0000LL-2O
	for pgsql-admin@arkaria.postgresql.org; Thu, 25 May 2017 14:01:24 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
	by malur.postgresql.org with smtp (Exim 4.84_2)
	(envelope-from
 <pgsql-admin-owner+M46690=pgsql+2Dadmin=arkaria.postgresql.org@postgresql.org>)
	id 1dDtKM-0004UV-TZ
	for pgsql-admin@arkaria.postgresql.org; Thu, 25 May 2017 14:01:22 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
	by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <samed@reddoc.net>)
	id 1dDtKL-0004OY-RA
	for pgsql-admin@postgresql.org; Thu, 25 May 2017 14:01:22 +0000
Received: from forward5p.cmail.yandex.net ([2a02:6b8:0:1465::15])
	by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256)
	(Exim 4.84_2)
	(envelope-from <samed@reddoc.net>)
	id 1dDtKI-0008VG-Ju
	for pgsql-admin@postgresql.org; Thu, 25 May 2017 14:01:20 +0000
Received: from mxback6j.mail.yandex.net (mxback6j.mail.yandex.net
 [IPv6:2a02:6b8:0:1619::10f])
	by forward5p.cmail.yandex.net (Yandex) with ESMTP id 51C2F20D5A;
	Thu, 25 May 2017 17:01:14 +0300 (MSK)
Received: from web27o.yandex.ru (web27o.yandex.ru [95.108.205.127])
	by mxback6j.mail.yandex.net (nwsmtp/Yandex) with ESMTP id
 U8L6EKKCIX-1Dk00mN3;
	Thu, 25 May 2017 17:01:13 +0300
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=reddoc.net; s=mail;
 t=1495720873;
	bh=spYDm53+RWk8cYcgRGpWznt9wfJBeP5X8R2CkmudGKc=;
	h=From:To:In-Reply-To:References:Subject:Message-Id:Date;
	b=Gd+13YrwfVgl3qsE6rS1uujLWa8ZyV+DH/w8XkumG2u2kiORVvI9kn9fbCh3j9hkm
	 C1kQ+N+16ewr2wFkNK1GPEpcArGV4PQX3ahYYHE0QEdNaJq3u7099lz9Wprf9fxuvC
	 WEY94LqPfbVp+manrdKClrTDcwqS/1jYtxCX4XII=
Authentication-Results: mxback6j.mail.yandex.net;
 dkim=pass header.i=@reddoc.net
Received: by web27o.yandex.ru with HTTP;
	Thu, 25 May 2017 17:01:13 +0300
From: Samed YILDIRIM <samed@reddoc.net>
To: pgsql-admin <pgsql-admin@postgresql.org>,
	JC Putter <jcputter@gmail.com>
In-Reply-To: 
 <CAKKrXOnrL85rdEA6UZWLi5aRXDgU8MRtexOuX-OzYM7Y=E2T5w@mail.gmail.com>
References: 
 <CAKKrXOnrL85rdEA6UZWLi5aRXDgU8MRtexOuX-OzYM7Y=E2T5w@mail.gmail.com>
Subject: Re: Roles and Privileges
MIME-Version: 1.0
Message-Id: <1749171495720873@web27o.yandex.ru>
X-Mailer: Yamail [ http://yandex.ru ] 5.0
Date: Thu, 25 May 2017 17:01:13 +0300
Content-Transfer-Encoding: base64
Content-Type: text/html; charset=utf-8
X-Pg-Spam-Score: -0.9 (/)
List-Archive: <http://archives.postgresql.org/pgsql-admin>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-admin.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@postgresql.org>
List-Post: <mailto:pgsql-admin@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-admin>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
X-Mailing-List: pgsql-admin
Precedence: bulk
Sender: pgsql-admin-owner@postgresql.org

PGRpdj48ZGl2PkhpIEpDLDwvZGl2PjxkaXY+wqA8L2Rpdj48ZGl2PkkgdHJ5IGNyZWF0aW5nIGEg
dGVzdCBlbnZpcm9ubWVudCBsaWtlIHlvdXIgcXVlc3Rpb24uIEkgZ3Vlc3MgYWRhcHRpbmcgZm9s
bG93aW5nIHNjZW5hcmlvIHRvIHlvdXIgZW52aXJvbm1lbnQgc29sdmVzIHlvdXIgcHJvYmxlbS48
L2Rpdj48ZGl2PsKgPC9kaXY+PGJsb2NrcXVvdGU+PGRpdj5DcmVhdGUgcm9sZXMgKHVzZXJzIGFu
ZCBncm91cCk8L2Rpdj48ZGl2PkNSRUFURSBST0xFIGFobWV0IExPR0lOOzwvZGl2PjxkaXY+Q1JF
QVRFIFJPTEUgc2FtZWQgTE9HSU47PC9kaXY+PGRpdj5DUkVBVEUgUk9MRSBtYWlsX2xpc3RfZ3Jv
dXA7PC9kaXY+PC9ibG9ja3F1b3RlPjxibG9ja3F1b3RlPjxkaXY+wqA8L2Rpdj48ZGl2PkdSQU5U
IG1haWxfbGlzdF9ncm91cCBUTyBhaG1ldDs8L2Rpdj48ZGl2PkdSQU5UIG1haWxfbGlzdF9ncm91
cCBUTyBzYW1lZDs8L2Rpdj48L2Jsb2NrcXVvdGU+PGRpdj7CoDwvZGl2PjxkaXY+Q3JlYXRlIHNj
aGVtYTwvZGl2PjxibG9ja3F1b3RlPjxkaXY+Q1JFQVRFIFNDSEVNQSBtYWlsX2xpc3Q8L2Rpdj48
ZGl2PsKgIEFVVEhPUklaQVRJT04gbWFpbF9saXN0X2dyb3VwOzwvZGl2PjwvYmxvY2txdW90ZT48
ZGl2PsKgPC9kaXY+PGRpdj5BbHRlciBkZWZhdWx0IHBlcm1pc3Npb25zIGZvciB1c2VycyBhbmQg
c2NoZW1hPC9kaXY+PGJsb2NrcXVvdGU+PGRpdj5BTFRFUiBERUZBVUxUIFBSSVZJTEVHRVMgRk9S
IFJPTEUgc2FtZWQgSU4gU0NIRU1BIG1haWxfbGlzdDwvZGl2PjxkaXY+wqDCoMKgIEdSQU5UIEFM
TCBPTiBUQUJMRVM8L2Rpdj48ZGl2PsKgwqDCoCBUTyBtYWlsX2xpc3RfZ3JvdXAgV0lUSCBHUkFO
VCBPUFRJT047PC9kaXY+PGRpdj5BTFRFUiBERUZBVUxUIFBSSVZJTEVHRVMgRk9SIFJPTEUgc2Ft
ZWQgSU4gU0NIRU1BIG1haWxfbGlzdDwvZGl2PjxkaXY+wqDCoMKgIEdSQU5UIEFMTCBPTiBTRVFV
RU5DRVM8L2Rpdj48ZGl2PsKgwqDCoCBUTyBtYWlsX2xpc3RfZ3JvdXAgV0lUSCBHUkFOVCBPUFRJ
T047PC9kaXY+PGRpdj5BTFRFUiBERUZBVUxUIFBSSVZJTEVHRVMgRk9SIFJPTEUgc2FtZWQgSU4g
U0NIRU1BIG1haWxfbGlzdDwvZGl2PjxkaXY+wqDCoMKgIEdSQU5UIEFMTCBPTiBGVU5DVElPTlM8
L2Rpdj48ZGl2PsKgwqDCoCBUTyBtYWlsX2xpc3RfZ3JvdXAgV0lUSCBHUkFOVCBPUFRJT047PC9k
aXY+PGRpdj7CoDwvZGl2PjxkaXY+QUxURVIgREVGQVVMVCBQUklWSUxFR0VTIEZPUiBST0xFIGFo
bWV0IElOIFNDSEVNQSBtYWlsX2xpc3Q8L2Rpdj48ZGl2PsKgwqDCoCBHUkFOVCBBTEwgT04gVEFC
TEVTPC9kaXY+PGRpdj7CoMKgwqAgVE8gbWFpbF9saXN0X2dyb3VwIFdJVEggR1JBTlQgT1BUSU9O
OzwvZGl2PjxkaXY+QUxURVIgREVGQVVMVCBQUklWSUxFR0VTIEZPUiBST0xFIGFobWV0IElOIFND
SEVNQSBtYWlsX2xpc3Q8L2Rpdj48ZGl2PsKgwqDCoCBHUkFOVCBBTEwgT04gU0VRVUVOQ0VTPC9k
aXY+PGRpdj7CoMKgwqAgVE8gbWFpbF9saXN0X2dyb3VwIFdJVEggR1JBTlQgT1BUSU9OOzwvZGl2
PjxkaXY+QUxURVIgREVGQVVMVCBQUklWSUxFR0VTIEZPUiBST0xFIGFobWV0IElOIFNDSEVNQSBt
YWlsX2xpc3Q8L2Rpdj48ZGl2PsKgwqDCoCBHUkFOVCBBTEwgT04gRlVOQ1RJT05TPC9kaXY+PGRp
dj7CoMKgwqAgVE8gbWFpbF9saXN0X2dyb3VwIFdJVEggR1JBTlQgT1BUSU9OOzwvZGl2PjwvYmxv
Y2txdW90ZT48L2Rpdj48ZGl2PsKgPC9kaXY+PGRpdj7CoDwvZGl2PjxkaXY+Q3JlYXRlIHRhYmxl
IGFzIHNhbWVkIGFuZCBpbnNlcnQgZHVteSBkYXRhLjwvZGl2PjxkaXY+PGJsb2NrcXVvdGU+PGRp
dj5hdHA9Jmd0OyBjcmVhdGUgdGFibGUgbWFpbF9saXN0LnQxIChjMSBpbnQpOzwvZGl2PjxkaXY+
Q1JFQVRFIFRBQkxFPC9kaXY+PGRpdj5hdHA9Jmd0OyBpbnNlcnQgaW50byB0MSBzZWxlY3QgZ2Vu
ZXJhdGVfc2VyaWVzKDEsMTAwKTs8L2Rpdj48ZGl2PklOU0VSVCAwIDEwMDwvZGl2PjwvYmxvY2tx
dW90ZT48ZGl2PsKgPC9kaXY+PGRpdj5TZWxlY3QgdGFibGUgYXMgYWhtZXQ8L2Rpdj48YmxvY2tx
dW90ZT48ZGl2PjxkaXY+YXRwPSZndDsgc2VsZWN0ICogZnJvbSBtYWlsX2xpc3QudDEgbGltaXQg
NTs8L2Rpdj48ZGl2PsKgYzE8L2Rpdj48ZGl2Pi0tLS08L2Rpdj48ZGl2PsKgIDE8L2Rpdj48ZGl2
PsKgIDI8L2Rpdj48ZGl2PsKgIDM8L2Rpdj48ZGl2PsKgIDQ8L2Rpdj48ZGl2PsKgIDU8L2Rpdj48
ZGl2Pig1IHJvd3MpPC9kaXY+PC9kaXY+PC9ibG9ja3F1b3RlPjwvZGl2PjxkaXY+wqA8ZGl2PkRy
b3AgdGFibGUgYXMgYWhtZXQ8L2Rpdj48ZGl2PjxibG9ja3F1b3RlPjxkaXY+YXRwPSZndDsgZHJv
cCB0YWJsZSBtYWlsX2xpc3QudDE7PC9kaXY+PGRpdj5EUk9QIFRBQkxFPC9kaXY+PC9ibG9ja3F1
b3RlPjwvZGl2PjxkaXY+wqA8L2Rpdj48ZGl2PkJlc3QgcmVnYXJkcy48L2Rpdj48ZGl2PlNhbWVk
IFlJTERJUklNPC9kaXY+PGRpdj7CoDwvZGl2PjxkaXY+wqA8L2Rpdj48ZGl2PsKgPC9kaXY+PGRp
dj4yNS4wNS4yMDE3LCAxMzoyMiwgIkpDIFB1dHRlciIgJmx0O2pjcHV0dGVyQGdtYWlsLmNvbSZn
dDs6PC9kaXY+PGJsb2NrcXVvdGUgdHlwZT0iY2l0ZSI+PGRpdj5IaSw8ZGl2PsKgPC9kaXY+PGRp
dj5JIHdvdWxkIGxpa2UgdG8gc2V0dXAgYSBuZXcgc2NoZW1hIG9uIGFuIGV4aXN0aW5nIGRhdGFi
YXNlIGFuZCBncmFudCBhIHNldCBvZiB1c2VycyBmdWxsIGNvbnRyb2wgdG8gdGhlIHNjaGVtYS48
L2Rpdj48ZGl2PsKgPC9kaXY+PGRpdj5JJ3ZlIGRvbmUgdGhlIGZvbGxvd2luZyBhbmQgd291bGQg
YXBwcmVjaWF0ZSBoZWxwIGFzIEknbSBub3QgZ2V0dGluZyB0aGUgYmVoYXZpb3IgSSB3YXMgaG9w
aW5nIGZvci48L2Rpdj48ZGl2PsKgPC9kaXY+PGRpdj5JIGNyZWF0ZSBhIG5ldyBncm91cCByb2xl
IGFuZCBhZGRlZCBtZW1iZXJzIHRvIHRoZSByb2xlIHRoZW4gZ3JhbnRlZCB0aGUgcm9sZSBhbGwg
cHJpdmlsZWdlcyB0byB0aGUgc2NoZW1hLsKgPC9kaXY+PGRpdj7CoDwvZGl2PjxkaXY+V2hlbiB1
c2VycyBjcmVhdGUgdGFibGVzIHRoZSB0YWJsZSBvd25lciBpdCBzZXQgdG8gdGhlIHVzZXIgaW5z
dGVhZCBvZiB0aGUgcm9sZSBhbmQgSSBoYXZlIHRvIGdyYW50IGVhY2ggbmV3bHkgY3JlYXRlZCB0
YWJsZSBiZWZvcmUgb3RoZXIgbWVtYmVycyBjYW4gYWNjZXNzIGl0LiDCoDwvZGl2PjxkaXY+wqA8
L2Rpdj48ZGl2PkkndmUgYWx0ZXJlZCB0aGUgZGVmYXVsdCBwcml2aWxlZ2VzIGhvd2V2ZXIgaXQg
c2VlbXMgdG8gbm90IGhhdmUgYW4gZWZmZWN0LiBTb21lb25lIHBvaW50ZWQgb3V0IHRoYXQgdXNl
cnMgd291bGQgbmVlZCB0byBTRVQgUk9MRSBiZWZvcmUgY3JlYXRpbmcgdGFibGVzLCBpcyB0aGlz
IHRoZSBvbmx5IHBvc3NpYmxlIHdheSBvciBpdCB0aGVyZSBhbm90aGVyIHdheSB0byBhY2hpZXZl
IHRoaXM/PC9kaXY+PGRpdj7CoDwvZGl2PjxkaXY+VGhhbmsgeW91IGluIGFkdmFuY2XCoDwvZGl2
PjwvZGl2PjwvYmxvY2txdW90ZT48L2Rpdj4=