Massive can't find schema when tables belong to a group (the user belongs to)

[jaecktec]

Hey, we have a current setup where the user belongs to a group. All the tables belong to that group, thus the user has access to the tables.

However the tables.sql script does not find those tables.

Steps how to reproduce: create user u_1, create group g_1,

assign u_1 to g_1, ALTER TABLE my_table OWNER TO g_1,

when you execute \z you will see:

Schema |      Name      | Type  | Access privileges | Column privileges | Policies
-------+----------------+-------+-------------------+-------------------+----------
public |    my_table    | table |                   |                   |

Is there any way to get my setup working?

[jaecktec]

also I have tried:

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public to u_1 GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO u_1

It ended up in

Schema |      Name      | Type  | Access privileges | Column privileges | Policies
-------+----------------+-------+-------------------+-------------------+----------
public |    my_table    | table |  g_1/u_1          |                   |

but still does not work :(

[jaecktec]

Proposal (can create a pull request, but I am not very experienced in postgres)

Add another union to the tables.sql

  SELECT t.table_schema AS schema,
      t.table_name AS name,
      NULL AS parent,
      NULL AS pk,
      CASE t.is_insertable_into WHEN 'YES' THEN TRUE ELSE FALSE END AS is_insertable_into,
      array_agg(c.column_name::text) AS columns
    FROM information_schema.tables t
    JOIN information_schema.columns c
      ON c.table_schema = t.table_schema
      AND c.table_name = t.table_name
    where t.table_schema not in ('pg_catalog', 'information_schema')
     and t.table_schema not like 'pg_toast%'
    GROUP BY t.table_schema, t.table_name, t.is_insertable_into

do you have any objections with this?

[dmfay]

I followed your steps:

create user mygroup;
create user myuser;
grant mygroup to myuser;

create table mytable (id serial primary key);
alter table mytable owner to mygroup;

and I found that Massive did in fact find and load mytable when connecting as myuser, although it appeared as "public.mytable" instead of "mytable" as myuser's current_schema was null. You might check this first, but the real issue was that neither mygroup nor myuser had the correct permissions for the public schema. grant usage on schema public to mygroup fixed that and mytable loaded as expected without the explicit path.

[vitaly-t]

In case somebody wants to access multiple schemas by default whilst dynamically, i.e. without changing the database configuration, it can be done with initialization option schema of pg-promise:

const initilizationOptions = {
    schema: ['schema-1', 'schema-2', ...]
};

so you have multiple default schemas. And keep in mind that if your schemas suddenly contain a table with the same name that you are trying to access by default, it will be used from the schema that's defined first on the list you specified.

[jaecktec]

Ok cool, I'll try that tomorrow!

[jaecktec]

Ok I am 100% confused. When I am doing a ssh tunnel from my macbook into the server and tunnel the database port to my local machine I have access to all of the schemas. However on the server (linux) it is not working... Feels like this is not a massive.js issue... But anyways help would be awesome 🙈

Node version (local and remote) is 9.11.1

Things that are different on the server:

• Node Modules is not within the application folder but on a different path.
• Mac has case a case insensitive filesystem

[dmfay]

You might check your pg_hba.conf, but I think that'd just determine whether you could log in or not as opposed to what you have access to. Past that all I've got is the basic list: am I really connecting to the same database, as the same user, etc.

[jaecktec]

Ok I could solve it but I couldn't find a solution. I deleted all the tables and created a new database instance (vm) created all the tables with a static user and handed it over to the shared group

Then it was working. Probably it had something to do with the case sensitivity of unix / insensitivity of mac... I don't know. However thanks for the suggestions and helps, I learned some things on the way!