Preventing access of user1 to user2's database - Mailing list pgsql-novice

Hello all,


I created 2 users (user01 and user02) with passwords.

I created 2 databases (user01db and user02db) owned by each of the
users.

Nevertheless, user02 can connect to the database of user01 (and
vice-versa), create tables, select's, inserts, etc.

I read the manuals and did not find anything to explain this
''overriding of authorizations''....

I also read something about 'pg_hba.conf' being related to this matter
but I did not quite get the point.

I must say that my pg_hba.conf contains 2 lines (one for 127.0.0.1 and
another for 10.0.24.x) with the 'all' word for databases and tables....
could this be the reason ? Could this file override user priviliges ?
...This file is still confusing for me....


bellow you can find a copy of my experiments...

thx

jmf





-----------------------------------------
-----------------------------------------

[jmf@cebola sql]$ createuser -e -P -h batata -U jmf user01
Enter password for new user:
Enter it again:
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
Password:
CREATE USER user01 PASSWORD 'user01pwd' NOCREATEDB NOCREATEUSER;
CREATE USER

[jmf@cebola sql]$ createuser -e -P -h batata -U jmf user02
Enter password for new user:
Enter it again:
Shall the new user be allowed to create databases? (y/n) n
Shall the new user be allowed to create more new users? (y/n) n
Password:
CREATE USER user02 PASSWORD 'user02pwd' NOCREATEDB NOCREATEUSER;
CREATE USER
[jmf@cebola sql]$

[jmf@cebola sql]$ createdb -e -h batata -U jmf -O user01 user01db
Password:
CREATE DATABASE user01db OWNER user01;
CREATE DATABASE
[jmf@cebola sql]$ createdb -e -h batata -U jmf -O user02 user02db
Password:
CREATE DATABASE user02db OWNER user02;
CREATE DATABASE
[jmf@cebola sql]$

[jmf@cebola sql]$ psql -h batata -l
Password:
         List of databases
    Name    |  Owner   | Encoding
------------+----------+-----------
 jmf_DB1    | jmf      | SQL_ASCII
 regression | postgres | SQL_ASCII
 template0  | postgres | SQL_ASCII
 template1  | postgres | SQL_ASCII
 user01db   | user01   | SQL_ASCII
 user02db   | user02   | SQL_ASCII
(12 rows)

[jmf@cebola sql]$ psql -h batata -U user02 --password user01db
Password:
Welcome to psql 7.4.6, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

user01db=> create table test (n int not null);
CREATE TABLE
user01db=> insert into test values (123);
INSERT 17270 1
user01db=> select * from test;
  n
-----
 123
(1 row)

user01db=> \q
[jmf@cebola sql]$

-----------------------------------------
-----------------------------------------