Doctrine2 PostgreSQL schema restriction

Question

I have a PostgreSQL database with PostGIS extension installed. It has 3 other schemas (tiger, tiger_data, topology) and the public. My tables should be in the public schema, but right now i can't run doctrine:schema:update, because of this error:

[PDOException]
SQLSTATE[42501]: Insufficient privilege: 7 ERROR:  permission denied for schema topology
LINE 1: SELECT min_value, increment_by FROM "topology"."topology_id_...

So i can't generate my tables from my entities (or just dump the DDL sql).

Can i restrict the doctrine somehow to analyse only public scheme and skip the others?

Answer 1

After creating the extension by superuser you can grant privileges on the required schemas to your default user.

-- tables and views
GRANT SELECT ON ALL TABLES IN SCHEMA topology TO some_username;
ALTER DEFAULT PRIVILEGES
IN SCHEMA topology
GRANT SELECT
ON TABLES
TO some_username;

-- sequences
GRANT SELECT, USAGE ON ALL SEQUENCES IN SCHEMA topology TO some_username;
ALTER DEFAULT PRIVILEGES
IN SCHEMA topology
GRANT SELECT, USAGE
ON SEQUENCES
TO some_username;

-- usage
GRANT USAGE ON SCHEMA topology TO PUBLIC;

Here's my template for Ansible to prepare the database to be used with PostGIS: https://gist.github.com/leafnode/5b416efe87ed152b12bb24b1dc4f5f14 Some privileges could be tightened if you use the database in a shared environment.