Confusion about users and roles

From: "C(dot) Bensend" <benny(at)bennyvision(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Confusion about users and roles
Date: 2010-03-01 01:05:37
Message-ID: da005f626200831f7eff5e7eb849d9f6.squirrel@webmail.stinkweasel.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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(at)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(at)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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Terry 2010-03-01 01:07:50 Re: continuous copy/update one table to another
Previous Message Terry 2010-03-01 00:40:09 Re: continuous copy/update one table to another