pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPrivileges question
4+ messages / 2 participants
[nested] [flat]
* Privileges question
@ 2011-10-18 16:15 Alexander James Spence [axs] <axs@aber.ac.uk>
0 siblings, 1 reply; 4+ messages in thread
From: Alexander James Spence [axs] @ 2011-10-18 16:15 UTC (permalink / raw)
To: pgsql-admin
Hi All,
I will have to create around 200+ users and user databases for student project use. I have tried all sorts of GRANT and REVOKE combinations but for the life of me cannot stop other users from creating tables in a database that is not their own.
EXAMPLE:
Create userone with a database userone
Create usertwo with a database usertwo
Login as usertwo and change to use userone database
Can now create tables in userone database
We are running version 9.1 anyone out there who can show me how can I stop this behaviour?
A bonus would also be nice if I can stop the user from deleting their own database.
Thanks in advance.
Regards,
Sandy Spence
Department of Computer Science
Aberystwyth University
Penglais Campus
Llandinam Building
Aberystwyth
Ceredigion
SY23 3DB
Tel: 01970-622433
Fax: 01970-628536
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Privileges question
@ 2011-10-18 16:27 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Alexander James Spence [axs] <axs@aber.ac.uk>
0 siblings, 1 reply; 4+ messages in thread
From: Kevin Grittner @ 2011-10-18 16:27 UTC (permalink / raw)
To: Alexander James Spence [axs] <axs@aber.ac.uk>; pgsql-admin
"Alexander James Spence [axs]" <axs@aber.ac.uk> wrote:
> will have to create around 200+ users and user databases for
> student project use. I have tried all sorts of GRANT and REVOKE
> combinations but for the life of me cannot stop other users from
> creating tables in a database that is not their own.
test=# create user xxx;
CREATE ROLE
test=# create database xxx owner xxx;
CREATE DATABASE
test=# \c xxx
You are now connected to database "xxx" as user "kgrittn".
xxx=# revoke create on database xxx from public;
REVOKE
xxx=# revoke create on schema public from public;
REVOKE
xxx=# grant create on schema public to xxx;
GRANT
You might also want to limit connection rights in pg_hba.conf
-Kevin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Privileges question
@ 2011-10-18 17:18 Alexander James Spence [axs] <axs@aber.ac.uk>
parent: Kevin Grittner <Kevin.Grittner@wicourts.gov>
0 siblings, 1 reply; 4+ messages in thread
From: Alexander James Spence [axs] @ 2011-10-18 17:18 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; pgsql-admin
Thanks Kevin,
That worked, head not quite so sore now ;¬).
If I wanted to set this as a default for all new databases and users how/where do I alter/create a schema other than public?
Regards,
Sandy Spence
Department of Computer Science
Aberystwyth University
Penglais Campus
Llandinam Building
Aberystwyth
Ceredigion
SY23 3DB
Tel: 01970-622433
Fax: 01970-628536
-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Kevin Grittner
Sent: 18 October 2011 17:27
To: Alexander James Spence [axs]; 'pgsql-admin@postgresql.org'
Subject: Re: [ADMIN] Privileges question
"Alexander James Spence [axs]" <axs@aber.ac.uk> wrote:
> will have to create around 200+ users and user databases for
> student project use. I have tried all sorts of GRANT and REVOKE
> combinations but for the life of me cannot stop other users from
> creating tables in a database that is not their own.
test=# create user xxx;
CREATE ROLE
test=# create database xxx owner xxx;
CREATE DATABASE
test=# \c xxx
You are now connected to database "xxx" as user "kgrittn".
xxx=# revoke create on database xxx from public;
REVOKE
xxx=# revoke create on schema public from public;
REVOKE
xxx=# grant create on schema public to xxx;
GRANT
You might also want to limit connection rights in pg_hba.conf
-Kevin
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Privileges question
@ 2011-10-18 21:03 Kevin Grittner <Kevin.Grittner@wicourts.gov>
parent: Alexander James Spence [axs] <axs@aber.ac.uk>
0 siblings, 0 replies; 4+ messages in thread
From: Kevin Grittner @ 2011-10-18 21:03 UTC (permalink / raw)
To: Alexander James Spence [axs] <axs@aber.ac.uk>; pgsql-admin
"Alexander James Spence [axs]" <axs@aber.ac.uk> wrote:
> If I wanted to set this as a default for all new databases and
> users how/where do I alter/create a schema other than public?
You could do the REVOKEs against the template1 database (or whatever
database you use for creating these if you don't take the default).
The rest would need to be scripted, so I would probably just leave
the template alone and cover the whole thing in my script.
-Kevin
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2011-10-18 21:03 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-10-18 16:15 Privileges question Alexander James Spence [axs] <axs@aber.ac.uk>
2011-10-18 16:27 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2011-10-18 17:18 ` Alexander James Spence [axs] <axs@aber.ac.uk>
2011-10-18 21:03 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
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