{"id":6631,"date":"2014-04-20T13:58:04","date_gmt":"2014-04-20T13:58:04","guid":{"rendered":"https:\/\/unknownerror.org\/index.php\/2014\/04\/20\/why-is-a-new-user-allowed-to-create-a-table-collection-of-common-programming-errors\/"},"modified":"2014-04-20T13:58:04","modified_gmt":"2014-04-20T13:58:04","slug":"why-is-a-new-user-allowed-to-create-a-table-collection-of-common-programming-errors","status":"publish","type":"post","link":"https:\/\/unknownerror.org\/index.php\/2014\/04\/20\/why-is-a-new-user-allowed-to-create-a-table-collection-of-common-programming-errors\/","title":{"rendered":"Why is a new user allowed to create a table?-Collection of common programming errors"},"content":{"rendered":"

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<\/code>:<\/p>\n

postgres=# \\l\n                                          List of databases\n     Name      |    Owner     | Encoding  |   Collate   |    Ctype    |       Access privileges       \n---------------+--------------+-----------+-------------+-------------+-------------------------------\n postgres      | postgres     | SQL_ASCII | C           | C           | \n project2_core | atm_project2 | UTF8      | de_DE.UTF-8 | de_DE.UTF-8 | project2=CTc\/project2\n template0     | postgres     | SQL_ASCII | C           | C           | =c\/postgres                  +\n               |              |           |             |             | postgres=CTc\/postgres\n template1     | postgres     | SQL_ASCII | C           | C           | =c\/postgres                  +\n               |              |           |             |             | postgres=CTc\/postgres\n(5 rows)\n<\/code><\/pre>\n
So far so good. Now I create a user:<\/p>\n
postgres=# CREATE ROLE dietrich ENCRYPTED PASSWORD 'md5XXX' LOGIN NOCREATEROLE NOCREATEDB NOSUPERUSER\n<\/code><\/pre>\n
Okay. When I try to connect to the database, the user is not allowed to do so:<\/p>\n
$ psql -h localhost -p 5432 -U dietrich -W project2_core\nPassword for user dietrich: \npsql: FATAL:  permission denied for database \"project2_core\"\nDETAIL:  User does not have CONNECT privilege.\n<\/code><\/pre>\n
This is what I expected. Now the strange stuff starts. I grant the user CONNECT<\/code>:<\/p>\n
postgres=# GRANT CONNECT ON DATABASE project2_core TO dietrich;\nGRANT\npostgres=# \\l\n                                          List of databases\n     Name      |    Owner     | Encoding  |   Collate   |    Ctype    |       Access privileges       \n---------------+--------------+-----------+-------------+-------------+-------------------------------\n postgres      | postgres     | SQL_ASCII | C           | C           | \n project2_core | atm_project2 | UTF8      | de_DE.UTF-8 | de_DE.UTF-8 | project2=CTc\/project2+\n               |              |           |             |             | dietrich=c\/project2\n template0     | postgres     | SQL_ASCII | C           | C           | =c\/postgres                  +\n               |              |           |             |             | postgres=CTc\/postgres\n template1     | postgres     | SQL_ASCII | C           | C           | =c\/postgres                  +\n               |              |           |             |             | postgres=CTc\/postgres\n(5 rows)\n<\/code><\/pre>\n
And without any further grants, the user is allowed to create a table:<\/p>\n
$ psql -h localhost -p 5432 -U dietrich -W project2_core\nPassword for user dietrich: \npsql (9.2.3)\nSSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)\nType \"help\" for help.\n\nproject2_core=> create table adsf ();\nCREATE TABLE\nproject2_core=> \\d\n        List of relations\n Schema | Name | Type  |  Owner   \n--------+------+-------+----------\n public | adsf | table | dietrich\n(1 row)\n<\/code><\/pre>\n
I would have expected that the user is not allowed to do anything before I explicitly do GRANT USAGE<\/code>on the schema and then GRANT SELECT<\/code> on the tables.<\/p>\n
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.<\/p>\n
I’m lost, and your help is greatly appreciated \ud83d\ude42<\/p>\n
EDIT<\/strong> Following the advice by @daniel-verite, I now revok all immediately after creating the database. The user dietrich<\/em> is not allowed to create a table any more. Good. BUT<\/strong>: Now, also the owner of the database, project2<\/em>, is not allowed to create a table. Even after issuing GRANT ALL PRIVILEGES ON DATABASE project2_core TO project2<\/code> and GRANT ALL PRIVILEGES ON SCHEMA public TO project2<\/code>, I get an error ERROR: no schema has been selected to create in<\/em>, and when I specifically try to CREATE TABLE public.WHATEVER ();<\/code>, I get ERROR: permission denied for schema public<\/em>. What am I doing wrong?<\/p>\n","protected":false},"excerpt":{"rendered":"
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 | […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-6631","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/6631","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/comments?post=6631"}],"version-history":[{"count":0,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/posts\/6631\/revisions"}],"wp:attachment":[{"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/media?parent=6631"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/categories?post=6631"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/unknownerror.org\/index.php\/wp-json\/wp\/v2\/tags?post=6631"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}