fixed explanation about the error message
Source Link
Daniel Vérité
  • 26.4k
  • 3
  • 57
  • 69

When you create a new database, any role is allowed to create objects in the public schema. To remove this possibility, you may issue immediately after the database creation:

REVOKE ALL ON schema public FROM public;

Edit: after the above command, only a superuser may create new objects inside the public schema, which is not practical. 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 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...). It appears that for a schema it means CREATE and USAGE.

On the other hand, GRANT ALL PRIVILEGES ON DATABASE... will grant CONNECT and CREATE and TEMP, but CREATE in this context relates to schemas, not permanent tables.

Regarding this error: ERROR: no schema has been selected to create in, I don't think it can result from only the REVOKE/GRANT couple of commands above. I think something else is amiss here, like maybe dropped schemas. Maybe the result ofhappens when trying to create an object without schema qualification (as in \dn+create table foo(...)) while lacking the permission to create it in any schema of the psqlsearch_path would help.

When you create a new database, any role is allowed to create objects in the public schema. To remove this possibility, you may issue immediately after the database creation:

REVOKE ALL ON schema public FROM public;

Edit: after the above command, only a superuser may create new objects inside the public schema, which is not practical. 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 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...). It appears that for a schema it means CREATE and USAGE.

On the other hand, GRANT ALL PRIVILEGES ON DATABASE... will grant CONNECT and CREATE and TEMP, but CREATE in this context relates to schemas, not permanent tables.

Regarding this error: ERROR: no schema has been selected to create in, I don't think it can result from only the REVOKE/GRANT couple of commands above. I think something else is amiss here, like maybe dropped schemas. Maybe the result of \dn+ in psql would help.

When you create a new database, any role is allowed to create objects in the public schema. To remove this possibility, you may issue immediately after the database creation:

REVOKE ALL ON schema public FROM public;

Edit: after the above command, only a superuser may create new objects inside the public schema, which is not practical. 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 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...). It appears that for a schema it means CREATE and USAGE.

On the other hand, GRANT ALL PRIVILEGES ON DATABASE... will grant CONNECT and CREATE and TEMP, but CREATE in this context relates to schemas, not permanent tables.

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.

added 1026 characters in body
Source Link
Daniel Vérité
  • 26.4k
  • 3
  • 57
  • 69

When you create a new database, any role is allowed to create objects in the public schema. To remove this possibility, you may issue immediately after the database creation:

REVOKE ALL ON schema public FROM public;

Edit: after the above command, only a superuser may create new objects inside the public schema, which is not practical. 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 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...). It appears that for a schema it means CREATE and USAGE.

On the other hand, GRANT ALL PRIVILEGES ON DATABASE... will grant CONNECT and CREATE and TEMP, but CREATE in this context relates to schemas, not permanent tables.

Regarding this error: ERROR: no schema has been selected to create in, I don't think it can result from only the REVOKE/GRANT couple of commands above. I think something else is amiss here, like maybe dropped schemas. Maybe the result of \dn+ in psql would help.

When you create a new database, any role is allowed to create objects in the public schema. To remove this possibility, you may issue immediately after the database creation:

REVOKE ALL ON schema public FROM public;

When you create a new database, any role is allowed to create objects in the public schema. To remove this possibility, you may issue immediately after the database creation:

REVOKE ALL ON schema public FROM public;

Edit: after the above command, only a superuser may create new objects inside the public schema, which is not practical. 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 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...). It appears that for a schema it means CREATE and USAGE.

On the other hand, GRANT ALL PRIVILEGES ON DATABASE... will grant CONNECT and CREATE and TEMP, but CREATE in this context relates to schemas, not permanent tables.

Regarding this error: ERROR: no schema has been selected to create in, I don't think it can result from only the REVOKE/GRANT couple of commands above. I think something else is amiss here, like maybe dropped schemas. Maybe the result of \dn+ in psql would help.

Source Link
Daniel Vérité
  • 26.4k
  • 3
  • 57
  • 69

When you create a new database, any role is allowed to create objects in the public schema. To remove this possibility, you may issue immediately after the database creation:

REVOKE ALL ON schema public FROM public;