[ADMIN] Privileges question

[Kevin Grittner]

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

[Kevin Grittner]

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