Re: Confusion about users and roles

> 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.

Advice taken... I don't really worry about it, mine is a very
small, personal environment that changes very little, so keeping
up with it isn't a problem. But, if I ever move into a larger
environment, I'll certainly do this.

>> 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.

OK, this makes a lot more sense now, especially when I see that it's
just CREATE on the public schema (and the new user cannot SELECT
from other tables). Thanks for the pointer. I did not at all
expect users to be able to CREATE tables in databases they did not
own. Is this a behaviour real DBAs expect? I'm just curious - I
am a hobby "DBA" and only play with databases for my own little pet
web applications, nothing more...

Thanks so much, Tom!

Benny

--
"Show me on the doll where the marketing touched you."
-- "Mally" on Fazed.net