Preventing access of user1 to user2's database

From: Joao Miguel Ferreira <jmf(at)estg(dot)ipvc(dot)pt>
To: pgsql-novice(at)postgresql(dot)org
Subject: Preventing access of user1 to user2's database
Date: 2006-01-10 17:01:02
Message-ID: 1136912462.2348.27.camel@sandoval
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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(at)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(at)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(at)cebola sql]$

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

[jmf(at)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(at)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(at)cebola sql]$

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Michael Fuhr 2006-01-10 17:38:59 Re: translating this SQL query from a different dialect
Previous Message Michael Fuhr 2006-01-10 16:40:09 Re: Error Returned by A Function