[postgis-users] Permissions issues after AWS RDS upgrade

[Timothy Asquith]

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
t...@redronin.io
www.redronin.io

[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

Hi folks,

_______________________________________________
postgis-users mailing list
postgi...@lists.osgeo.org
http://lists.osgeo.org/mailman/listinfo/postgis-users

[Timothy Asquith]

Hi Regina,

Thanks for letting me know the score, and for the tips on working around the bug.

I've tried the GRANT SELECT and ALTER EXTENSION POSTGIS UPDATE methods you've described, but to no avail. It appears that the rds_superuser role - the most-privileged user role available to me on RDS - still can't reclaim permissions on the geometry_columns table.

At this point, I think a hard upgrade in a brand new database is my main option. Or, perhaps, emptying my current DB and reinstalling PostGIS  from scratch.

I'll keep an eye on the ticket. Thanks.