Thread: Confusion about users and roles
Hey folks,
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'm sure this is just misunderstanding on my part, but reading the
documentation hasn't cleared it up for me yet.
This is PostgreSQL 8.4.2 on OpenBSD, FYI.
I created a user for the Postfix system to connect to the database:
CREATE USER postfix WITH PASSWORD 'xxxxxxxx';
I then create a database for this configuration stuff:
CREATE DATABASE email WITH OWNER benny;
I added the postfix user to pg_hba.conf and reloaded PostgreSQL:
host email postfix 127.0.0.1/32 password
local email postfix password
I can now log in as the database owner, and create a table:
[benny@fusion]$ psql email
email=> CREATE TABLE foo() ;
CREATE TABLE
email=> \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | foo | table | benny
(1 row)
email=>
I can also log in as the postfix user, due to the settings I
gave the user in pg_hba.conf:
[benny@fusion ~]$ psql -U postfix email
Password for user postfix:
psql (8.4.2)
Type "help" for help.
email=> \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
public | foo | table | benny
(1 row)
email=>
Here's the part I didn't expect:
email=> CREATE TABLE foo2() ;
CREATE TABLE
email=> \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+---------
public | foo | table | benny
public | foo2 | table | postfix
(2 rows)
email=>
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?
Thanks!
Benny
--
"Show me on the doll where the marketing touched you."
-- "Mally" on Fazed.net
"C. Bensend" <benny@bennyvision.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
> 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