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]$
-----------------------------------------
-----------------------------------------
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 |