Postgresql & read only user

Help me!
1

I’m using a postgresql database with 3 users and 3 schema.

The users are :

  • data1
  • data2
  • forestadmin_ro

The schema are :

  • public
  • data1
  • data2

The data1 schema is the one that we use in production.
We use the data2 schema for our clients integration tests. The db structure in data1 and data2 are identical.

The user data1 have a read/write access to public schema and data1 schema.
The user data2 have a read acces to public schema and a read/write access to data2 schema.
The user forestadmin_ro have a read only access to public, data1 and data2 schema.

The documentation seem invalid / incomplete :

The db structure with the forestadmin_ro and the following command is incorrect :
DATABASE_SCHEMA=data1 forest schema:update

Because it uses some PG views where data are only valid for the table owner or users that have more than SELECT ON. Exemple “table_constraints” : https://www.postgresql.org/docs/9.5/infoschema-table-constraints.html used in the Forest Admin CLI “src/services/schema/update/analyser/sequelize-table-constraints-getter.js”.
The dump is incorrect because it doesn’t add the “primary key” information.

If i have the 3 schema in my database and i try to dump the data, it throw this exception “more than one row returned by a subquery used as an expression”. More info on that here : Multiple postgres schema with the same table name · Issue #354 · ForestAdmin/toolbelt · GitHub
Maybe because the schema data1 and data2 have some table with the same name.

Do someone have is configuration and difficulties too ?
Is there is a workaround ?

2

Hi, @florianb,

It seems there are two issues:

  • About having a forest project with a read-only user:
    • you should be able to do this (check PS below)
  • About having multiple schemas in one project
    • for the moment we do not support multiple schemas at the same time.
    • workaround: having two different forest projects: production and integration.
    • workaround2: having two schemas but managing models manually.
    • I am creating a product board to ask for multi-schema support to be implemented.

PS: a minimal user that can generate/update a project:

CREATE USER forestro noinherit;
ALTER USER forestro WITH ENCRYPTED PASSWORD 'secret';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO forestro;

Regards

3

OK, thanks for the reply.