Re: Confusion about users and roles

"C. Bensend" <benny(at)bennyvision(dot)com> writes:
> I'm playing around with putting some of my email system's config
> into PostgreSQL, and I ran into some behavior I didn't expect today.

> ...

> I added the postfix user to pg_hba.conf and reloaded PostgreSQL:

Generally speaking you don't want to make per-user entries in
pg_hba.conf; it's just too much of a PITA for maintenance, unless
you really need different auth mechanisms for different users.
I'd suggest using "all" for the hba database and user columns whenever
possible. If you want control over who can connect to which DB,
the "GRANT CONNECT ON DATABASE ..." privilege is much easier to
manage than a pile of custom hba entries.

> Um... What did I miss? Why would the default permissions given
> to a new user and a new database allow this new user to create
> tables? Or am I being an idiot here?

A lot of people are surprised by this, but fewer than would be surprised
if we prevented it. The privilege in question is not per-database
anyway; rather, it's CREATE privilege on the "public" schema. You can
revoke that, or even remove the "public" schema altogether, depending
on how draconian you want to be and how much naive code you're willing
to break.

This is all covered in the docs. Now that you know what to look for,
you might want to reread
http://www.postgresql.org/docs/8.4/static/ddl-schemas.html
as well as the GRANT reference page.

regards, tom lane