SPIT to carry over default privileges

[qgib]

Author Name: Patrick Rickles (Patrick Rickles)
Original Redmine Issue: 5188
Affected QGIS version: 1.7.3

---

I am trying to set up a postgres/postgis database for multiple users (roles) to import shapefiles into and view/edit. I have default privileges set for the schema (public) so that users will have full permissions on all tables (existing and newly added ones). With this in place, I add a new shapefile to that database with SPIT logged in as one user; however, when I try to "Add PostGIS Layer" logged in as another user, I cannot see the shapefile the first user added. However, if I add a new table to the database inside of psql itself (create table), it does carry over the permissions.

Steps to Replicate:
Inside of psql (using version 9.1)

1. create database test1;
   (either make with postgis as template or do "createlang plpgsql test1" and run postgis.sql, postgis_comments.sql and spatial_ref_sys.sql afterwards to spatialise the database)
2. create role user1 with password 'user1';
   (afterwards, do "grant all on table geometry_columns to user1" and "grant all on table spatial_ref_sys to user1"; you may also need to do "alter role user1 login")
3. create role user2 with password 'user2';
   (afterwards, do "grant all on table geometry_columns to user2" and "grant all on table spatial_ref_sys to user2"; you may also need to do "alter role user2 login")
4. ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO user1;
5. ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO user2;

On the system

1. alter pg_hba.conf by adding "host all all 0.0.0.0/0 md5" to the end of the file to allow remote connections to postgres (this is standard setup).
   You may need to also edit postgresql.conf by changing listen_address to '*' and uncommenting the line "port = 5432", if it's commented out.
2. Restart psql.

In QGIS

1. Open SPIT and make the connection to the database (using the correct database/server credentials) as user1.
2. Import a shapefile (file1).
3. Click on "Add PostGIS Layer" and connect as user1. Verify that file1 is there.
4. Click on "Add PostGIS Layer" and change the connection so you are connecting as user2. You won't see file1 (even though the default privileges were set on the schema that should allow you to do so).

Back in psql

1. Log into the psql, connect to database test1.
2. Enter \dp to list the database in the database and check the Access privileges column. You will see that geometry_columns and spatial_ref_sys have full privileges for user1 and user2 (as we manually granted these earlier), but file1 will have no access privileges set.
3. Do "create table table1 (x int4);" to create a new table.
4. Enter \dp again. You will now see the new table created in your database AND the Access privileges have been properly set for the new table, giving user1 and user2 full privileges without having to manually do this. SPIT should do this as well (taking into account default access privileges).

(Apologies, if this has been listed incorrectly; I see this more as a bug rather than a feature)

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

SPIT is not mantained and it will be discontinued as soon as DB Manager (that is also a replacement of PostGIS Manager) will support multiple shapefiles upload.

[qgib]

Author Name: Patrick Rickles (Patrick Rickles)

---

Will the DB Manager carry over default access privileges? When will the DB Manager be available with a stable release? Thanks for any info :)

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

Patrick Rickles wrote:

Will the DB Manager carry over default access privileges? When will the DB Manager be available with a stable release? Thanks for any info :)

for now try PostGIS Manager (that has a tool to export shapefiles to PostGIS) and then report back if you have the same issue. Eventually we will leave a note to the DB Manager developer.

[qgib]

Author Name: Patrick Rickles (Patrick Rickles)

---

Hi Giovanni,

I've just downloaded PostGIS Manager (nice plugin!), but can verify the same issue with default access privileges not being carried over to imported shapefiles. Could you notify the DB Manager developer about this issue (or let me know how I can do so)? Cheers!

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

Patrick Rickles wrote:

Hi Giovanni,

Could you notify the DB Manager developer about this issue (or let me know how I can do so)? Cheers!

Hi Patrick,
just file a request ticket here

https://issues.qgis.org/projects/dbmanager

---

• resolution was changed from to invalid
• status_id was changed from Open to Closed