I'm wondering why a newly created user is allowed to create a table after connecting to a database. I have one database, project2_core
:
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
---------------+--------------+-----------+-------------+-------------+-------------------------------
postgres | postgres | SQL_ASCII | C | C |
project2_core | atm_project2 | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 | project2=CTc/project2
template0 | postgres | SQL_ASCII | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | SQL_ASCII | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)
So far so good. Now I create a user:
postgres=# CREATE ROLE dietrich ENCRYPTED PASSWORD 'md5XXX' LOGIN NOCREATEROLE NOCREATEDB NOSUPERUSER
Okay. When I try to connect to the database, the user is not allowed to do so:
$ psql -h localhost -p 5432 -U dietrich -W project2_core
Password for user dietrich:
psql: FATAL: permission denied for database "project2_core"
DETAIL: User does not have CONNECT privilege.
This is what I expected. Now the strange stuff starts. I grant the user CONNECT
:
postgres=# GRANT CONNECT ON DATABASE project2_core TO dietrich;
GRANT
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
---------------+--------------+-----------+-------------+-------------+-------------------------------
postgres | postgres | SQL_ASCII | C | C |
project2_core | atm_project2 | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 | project2=CTc/project2+
| | | | | dietrich=c/project2
template0 | postgres | SQL_ASCII | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | SQL_ASCII | C | C | =c/postgres +
| | | | | postgres=CTc/postgres
(5 rows)
And without any further grants, the user is allowed to create a table:
$ psql -h localhost -p 5432 -U dietrich -W project2_core
Password for user dietrich:
psql (9.2.3)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.
project2_core=> create table adsf ();
CREATE TABLE
project2_core=> \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | adsf | table | dietrich
(1 row)
I would have expected that the user is not allowed to do anything before I explicitly do GRANT USAGE
on the schema and then GRANT SELECT
on the tables.
Where is my mistake? What am I doing wrong? How can I achieve what I want (that a new user is not allowed to do anything before explicitly granting her the appropriate rights.
I'm lost, and your help is greatly appreciated :)
EDIT Following the advice by @daniel-verite, I now revok all immediately after creating the database. The user dietrich is not allowed to create a table any more. Good. BUT: Now, also the owner of the database, project2, is not allowed to create a table. Even after issuing GRANT ALL PRIVILEGES ON DATABASE project2_core TO project2
and GRANT ALL PRIVILEGES ON SCHEMA public TO project2
, I get an error ERROR: no schema has been selected to create in, and when I specifically try to CREATE TABLE public.WHATEVER ();
, I get ERROR: permission denied for schema public. What am I doing wrong?