Postgres Pro
Downloads
Home   >   mailing lists

Thread: Preventing access of user1 to user2's database

Preventing access of user1 to user2's database

From
Joao Miguel Ferreira
Date:
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]$

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

Re: Preventing access of user1 to user2's database

From
"Danjel Jungersen"
Date:
On 10 Jan 2006 at 12:01, Joao Miguel Ferreira wrote:

> 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....
I think so...
By memory, there is an option "sameuser" (or something like it), that
should do the trick.
I have to say that I have not yet managed to get my server running,
so I may not be all correct ;-)

Best of luck
Danjel

Re: Preventing access of user1 to user2's database

From
Tom Lane
Date:
Joao Miguel Ferreira <jmf@estg.ipvc.pt> writes:
> 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.

This is normal.  If you don't want a particular user to be able to
connect to a particular database at all, you should alter pg_hba.conf
to prevent it.  (As somebody suggested nearby, "sameuser" can be a
handy solution when your standard policy is that each user has a
database named after himself.)

As for what they can do after they've connected, the default behavior
is actually "not much" --- except that the "public" schema in each
database has public CREATE and USAGE permissions, so it's possible to
create tables within that schema.  If you prefer you can lock down
the public schema more, or even remove it altogether.  See the
discussion of privileges in the manual.

            regards, tom lane