11

I am new to Postgres and I am trying to understand how schemas fit into everything and what is the benefit of a non-'public' schema. For example, does it provide security, access control benefits or something else?

The setup I am working with is one where postgres owns the database and tables, but grants permissions to another user to select, insert, update, and delete. Would creating the tables in the 'public' schema vs 'myotherschema' make much of a difference?

1

1 Answer 1

8

what is the benefit of a non-'public' schema.

A schema provides a standardized level of logical organization. If you've never used them, it's like asking what's the value of directories. You don't technically need to have them, but you can use them to

  • Organize & Componentize
  • Control access
  • Reduce namespace-collisions

Also worth mentioning, the query planner can plan across schema boundaries. In the same sense that tools that operate on files can work across directories.

Perhaps more to the point of the Question you could explain that there is nothing special about the schema named public, just an arbitrarily chosen name for the default schema. – Basil Bourque 1 min ago

^ Valid point too. If you don't use your own customized schema, you're still using schema. It's just named public and placed in your search_path by default.

2
  • If a user only has connect access to a single database, is there any benefit of providing more granular access control, by creating a non-public schema, assuming each database has its own public schema?
    – Andre M
    Oct 19, 2017 at 17:50
  • "A single database" isn't very specific. If it's more than one /thing/ then you may or may not find another unit of organization to be more useful. If I give you 100 csv's -- that's a database. If you want the ability to organize it further, that could make use of a schema. Oct 19, 2017 at 17:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.