TEMP: Create temporary objects, including but not limited to temp tables.These are actions on the database object its self: When you grant ALL on a database, you're granting CREATE, CONNECT, TEMP. ALL means all privileges for this object not all privileges for this object and all contained objects. The crucial thing to understand here is that privileges are not heirachical and are not inherited from containing objects. Regarding this error: ERROR: no schema has been selected to create in, it happens when trying to create an object without schema qualification (as in create table foo(.)) while lacking the permission to create it in any schema of the search_path. will grant CONNECT and CREATE and TEMP, but CREATE in this context relates to schemas, not permanent tables. On the other hand, GRANT ALL PRIVILEGES ON DATABASE. It appears that for a schema it means CREATE and USAGE. To know what ALL means for a schema, we must refer to GRANT in the doc, (in PG 9.2 there are no less than 14 forms of GRANT statements that apply to different things.). Assuming a non-superuser foo_user should be granted this privilege, this should be done with: GRANT ALL ON schema public TO foo_user To remove this possibility, you may issue immediately after the database creation: REVOKE ALL ON schema public FROM public Įdit: after the above command, only a superuser may create new objects inside the public schema, which is not practical. When you create a new database, any role is allowed to create objects in the public schema. 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. BUT: Now, also the owner of the database, project2, is not allowed to create a table. The user dietrich is not allowed to create a table any more. I'm lost, and your help is greatly appreciated :)ĮDIT Following the advice by I now revok all immediately after creating the database. 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 would have expected that the user is not allowed to do anything before I explicitly do GRANT USAGEon the schema and then GRANT SELECT on the tables. SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Project2_core | atm_project2 | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 | project2=CTc/project2 Īnd without any further grants, the user is allowed to create a table: $ psql -h localhost -p 5432 -U dietrich -W project2_core I grant the user CONNECT: postgres=# GRANT CONNECT ON DATABASE project2_core TO dietrich Psql: FATAL: permission denied for database "project2_core"ĭETAIL: User does not have CONNECT privilege. 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 Now I create a user: postgres=# CREATE ROLE dietrich ENCRYPTED PASSWORD 'md5XXX' LOGIN NOCREATEROLE NOCREATEDB NOSUPERUSER Template1 | postgres | SQL_ASCII | C | C | =c/postgres Template0 | postgres | SQL_ASCII | C | C | =c/postgres Project2_core | atm_project2 | UTF8 | de_DE.UTF-8 | de_DE.UTF-8 | project2=CTc/project2 Postgres | postgres | SQL_ASCII | C | C | Name | Owner | Encoding | Collate | Ctype | Access privileges I have one database, project2_core: postgres=# \l I'm wondering why a newly created user is allowed to create a table after connecting to a database.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |