Thread: On default privilege/access behavior and how to change it?

Hey all!

I have a postgresql 8.0 installation we're using to give database
classes. By default we create a linux user and a postgresql user (with
create database permissions) and use ident authentication.

Users initially use psql to  connect to the template1 database; from
there they create their own database, connect to that and start working.

The problem I'm having is, if the user forgets to connect to their own
database, they stay on template1 and are able to create tables and
generally mess the database around.

Further, any user is able to connect to any other user's database and
create his own tables there. So if user A connects to B's database he
can create a table, and B can't remove it even though he owns the database.

I'd like to configure things so that:

a) users are unable to modify template1; for that matter, I'm planning
on having them connect to the "postgres" database first, because if
people are connected to template1, they can't create their own
databases. The problem persists since the "postgres" database is
similarly affected and anyone can create tables there.

b) Users can't connect to databases other than the ones they created
(and perhaps the postgres database, for initial connection purposes). At
the very least, they should be able to connect and perhaps have
read-only access, but *NOT* create new tables on databases they didn't
create.

Could anyone help me in doing this? if it's easier/better on a newer
postgresql version, I'm willing to upgrade.

Thanks in advance!

- Daniel

"Ing. Daniel Manrique" <dmanriqu@correo.fca.unam.mx> writes:
> I'd like to configure things so that:
> a) users are unable to modify template1;

If you're on a PG version where template1 isn't the default connection
target, you could just disallow connections to it.  But that might just
mean that your problem moves over to the "postgres" database.

> b) Users can't connect to databases other than the ones they created
> (and perhaps the postgres database, for initial connection purposes).

See CONNECT privilege (as of 8.2) and/or modify pg_hba.conf.

            regards, tom lane