PostgreSQL/PHP custom CMS - multiple schemas within single database security.

Question

Hi. I am writing a CMS using PHP and PostgreSQL. The CMS is capable of running multiple sites from a single codebase, using a unique set of tables per site / domain, differeing depending on which features are enabled.

I understand that PostgreSQL supports multiple schemas within a single database, and that access permissions are then set by schema.

My question is whether there are any security implications using a single schema per site within the same database?

This question has been really helpful in understanding the pros and cons either way, however it does not mention security aspects. The chosen answer states that PostgreSQL "schema" is equivalent to MySQL "database". If this is the case I'm comfortable with this approach. However, are there any security issues I should be aware of? My understanding is that the granular permissions used by PostgreSQL should protect each schema within a given database, however I'm not 100% sure on this.

Will this setup also make it possible to query across multiple schemas within a single query for a user with permissions on multiple schemas?

Any thoughts would be greatly appreciated, thanks in advance.

Answer 1

Yes, a user with the appropriate permissions could query across schemas. Just make sure to prefix the table names with the schema name.

Security-wise, as long as you are using a separate user account for each schema which only has access to that schema, there shouldn't be a difference compared to using separate databases.

Answer 2

They will be able to see that the other schema and tables exists, but they won't be able to access them. The Postgres catalogs which contain such information can't be locked down like that. Its a known "limitation" of separation by schema.

Otherwise, as long as the security is setup correctly, they shouldn't be able to access any information by outside their schema.

And yes, you can make a user query across any schema/table/object that they have access to, so long as its either in the search_path or by explicitly qualifying it.