[postgis-users] Permissions issues after AWS RDS upgrade

[Regina Obe]

Timothy,

Sorry about this.  I heard someone complain about this on IRC as well.
I think it's a bug in our UPGRADE machinery and not RDS specific.  So I've ticketed it.

https://trac.osgeo.org/postgis/ticket/3680

The create extension has the GRANT .. logic for views, but our extension upgrade scripts appear to be missing it.

I'm not sure how you get around it in AWS RDS unfortunately.  

I think the reason I've never run into the issue is I usually have default permissions for tables on the schema I install into.  So the views naturally just inherited those permissions.

You could try this:

ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema 
GRANT SELECT ON TABLES TO PUBLIC;

Where my_schema replace with the schema you have PostGIS installed in.
Now this won't fix the view issue, but you can trigger a re-upgrade by using the developer upgrade hack.

ALTER EXTENSION POSTGIS UPDATE TO '2.2.2next';
ALTER EXTENSION POSTGIS UPDATE TO '2.2.2';

That should force a recreate of the views, and the recreation should take on the default privilege settings.

If that doesn't work, best I can do is do a new micro release with it and contact Amazon to provide the new release.


Thanks for reporting and hope the above helps,
Regina
http://www.postgis.us
http://postgis.net






From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of Timothy Asquith
Sent: Friday, December 16, 2016 9:59 PM
To: postgis-users at lists.osgeo.org
Subject: [postgis-users] Permissions issues after AWS RDS upgrade

Hi folks,

I'm running Postgres / PostGIS on AWS RDS, and I recently upgrade from Postgres 9.4 to 9.5, and from Postgis 2.1 to 2.2.

The Postgres upgrade was carried out via AWS's managed upgrade process. The PostGIS upgrade was carried out with ALTER EXTENSION POSTGIS UPDATE TO '2.2.2'.

Following the upgrade, I have lost permissions on the geometry_columns and raster_columns views. Certain actions, such as an ogr2ogr export, fail with the message:

ERROR:  permission denied for relation geometry_columns

I have tried to use GRANT to recover the permissions, but I receive the same error message. I cannot see the content of this view, and it no longer appears in information_schema.tables

Has anyone experienced this issue when upgrading AWS RDS Postgres and, if so, how did you get around it?

I appreciate this is a limitation of RDS, as I could use GRANT if I had the required superuser access. Nonetheless, I thought the PostGIS community might have experience in this area.


▬▬▬ι═══════════ﺤ
Timothy Asquith // Red Ronin
tim at redronin.io
www.redronin.io