Author Name: Matt Yoder (@yomatters)
Original Redmine Issue: 20096
Redmine category:data_provider/postgis
The "Discover Relations" feature is a great time-saver, but for PostGIS layers, it only works when the database user is the owner of the table. I believe that's because the PostGIS provider looks up the constraints in @information_schema.referential_constraints@, which is only available to the table owner.
It should be possible for non-owner users to get constraint information using @pg_get_constraintdef@, as "demonstrated here":https://dba.stackexchange.com/questions/36979/retrieving-all-pk-and-fk. (However, the provider will need to parse the returned SQL to get the constraint information.) This would be a useful enhancement for workflows where users are routinely accessing and editing tables that they do not own.
Author Name: Matt Yoder (@yomatters) Original Redmine Issue: 20096
Redmine category:data_provider/postgis
The "Discover Relations" feature is a great time-saver, but for PostGIS layers, it only works when the database user is the owner of the table. I believe that's because the PostGIS provider looks up the constraints in @information_schema.referential_constraints@, which is only available to the table owner.
It should be possible for non-owner users to get constraint information using @pg_get_constraintdef@, as "demonstrated here":https://dba.stackexchange.com/questions/36979/retrieving-all-pk-and-fk. (However, the provider will need to parse the returned SQL to get the constraint information.) This would be a useful enhancement for workflows where users are routinely accessing and editing tables that they do not own.