The owner can always create a new schema, and alter the database so that connecting users will use that schema. The main problem is that Postgresql poorly designed its defaults and permissions here: each new database has a public schema, which is by default what every user has access to read AND create objects in when connected to that database, and each new user's search-path defaults to include public.īeing a bit security conscious, one solution for the DBA is to modify template1 as follows: REVOKE CREATE ON SCHEMA public FROM public Īnd for the even more paranoid: REVOKE USAGE ON SCHEMA public FROM public īut in Postgresql's InfiniteWisdom™, revoking these also blocks permissions to the owner of the database. Without public USAGE or CREATE privileges Lack of a rolename before = means it's for all roles (=public) To check the privileges inside psql, use \dn+ public.īy default (shown with extended display \x for readability): When a user has USAGE privilege, but lacks CREATE privilege on the schema, it's a different error when trying to create an object: permission denied for schema public. If this REVOKE hasn't been done in that database, it may have happened in the template database by which new databases are modelled (see CREATE DATABASE). This is required when it's not desirable that people peek into other people schemas, even without selecting data from tables (which is granted through different privileges).
By default the pseudo-role public (all users) has this privilege on the publicschema, so this error happens only after revoking it explicitly with: revoke usage on schema public from public This happens when not having USAGE privilege on any of the schemas of search_path. Is it correct? How can I check if the permissions in template1 are correct (say, default values)? I think I should make the same modification on template1. I have a single user who can access the database, so I think this anyway cannot raise security risks. Thanks to the answer of Daniel Vérité I've solved with the following: grant usage on schema public to public Īre these the default permissions on the public schema? Here is a sample session with a newly created empty database: mydb=> CREATE TABLE distributors (ĮRROR: no schema has been selected to create in But apparently the schema exists, and anyway the problem is not solved.
I am working on an amazon RDS postgresql database where I know there had been some issue with the public schema (maybe it was dropped).
0 kommentar(er)
