pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedROLES and PRIVILEGES
5+ messages / 5 participants
[nested] [flat]
* ROLES and PRIVILEGES
@ 2006-07-26 13:50 Rodrigo Sakai <rodrigo.sakai@zanthus.com.br>
0 siblings, 1 reply; 5+ messages in thread
From: Rodrigo Sakai @ 2006-07-26 13:50 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: ROLES and PRIVILEGES
@ 2006-07-26 15:05 Tom Lane <tgl@sss.pgh.pa.us>
parent: Rodrigo Sakai <rodrigo.sakai@zanthus.com.br>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2006-07-26 15:05 UTC (permalink / raw)
To: rodrigo.sakai@zanthus.com.br; +Cc: pgsql-admin
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Roles and privileges?
@ 2008-07-25 14:30 Carol Walter <walterc@indiana.edu>
0 siblings, 0 replies; 5+ messages in thread
From: Carol Walter @ 2008-07-25 14:30 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Roles and Privileges
@ 2017-05-25 10:20 JC Putter <jcputter@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: JC Putter @ 2017-05-25 10:20 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Roles and Privileges
@ 2017-05-25 14:01 Samed YILDIRIM <samed@reddoc.net>
parent: JC Putter <jcputter@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Samed YILDIRIM @ 2017-05-25 14:01 UTC (permalink / raw)
To: pgsql-admin; JC Putter <jcputter@gmail.com>
<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>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2017-05-25 14:01 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-07-26 13:50 ROLES and PRIVILEGES Rodrigo Sakai <rodrigo.sakai@zanthus.com.br>
2006-07-26 15:05 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-07-25 14:30 Roles and privileges? Carol Walter <walterc@indiana.edu>
2017-05-25 10:20 Roles and Privileges JC Putter <jcputter@gmail.com>
2017-05-25 14:01 ` Re: Roles and Privileges Samed YILDIRIM <samed@reddoc.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