The problem (short version)
I have to import data from files to PostgreSQL, and this has to be done on the server side. Files are uploaded to the PostgreSQL server and then imported to the database by the postgres role. As soon as the tables are created, I change the ownership to the original role that started the import data process on the client side.
The default privileges I have in place for the roles that can import data are not applied when I change the ownership.
Role upload can create tables; role citizen has access to upload tables given by default privileges.
If I create a table with postgres, assign it to upload, citizen has no access to it.
Details
I have a schema upload and a role upload. The role upload can create tables in the schema upload.
I have another role citizen that should be able to read all tables in the schema upload.
create user citizen with password 'password';
-- Current tables
GRANT USAGE ON SCHEMA upload TO citizen;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA upload TO citizen;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA upload TO citizen;
-- Future tables
ALTER DEFAULT PRIVILEGES FOR ROLE upload IN SCHEMA upload GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO citizen;
ALTER DEFAULT PRIVILEGES FOR ROLE upload IN SCHEMA upload GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO citizen;
Check permission on existing tables: perfect
The following query shows that citizens have access to all existing tables.
SELECT table_catalog, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'citizen' AND table_schema = 'upload';
Check citizen permission on future tables: perfect, as expected
set role upload;
create table upload.x (name text);
-- Check citizen permission on newly created tables: perfect!
SELECT table_catalog, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'citizen' AND table_schema = 'upload';
What does not work
If I create a table as postgres and then assign the ownership to upload, the default privileges are not applied.
set role postgres;
create table upload.y (name text);
-- Check citizen permission on newly created tables by postgres: citizen has no access, as expected
-- so far, so good
ALTER TABLE upload.y OWNER TO upload;
-- Check citizen permission on the table owned by upload: citizen has no access, not as expected
SELECT table_catalog, table_schema, table_name, privilege_type
FROM information_schema.role_table_grants
WHERE grantee = 'citizen' AND table_schema = 'upload';
Question
How do I apply default privileges when changing ownership?
The default privileges are there (in pg_default_acl), and maybe I could have some procedure to iterate over the default privileges and assign them. But PostgreSQL logic could do that too.
SELECT nspname, defaclobjtype, defaclacl FROM pg_default_acl a JOIN pg_namespace b ON a.defaclnamespace=b.oid;
nspname|defaclobjtype|defaclacl |
-------+-------------+---------------------+
upload |r |{citizen=arwd/upload}|
upload |S |{citizen=rwU/upload} |
How do I create the tables
End users use a QGIS plugin that I developed to upload and create a raster table on the server.
Here is a screenshot of the interface:
The data is uploaded to the server, and then I run the following command on the server:
raster2pgsql -s EPSG:3763 -q -d -I -C -F -M -t 512x512 -l 2,4,8 '/tmp/1410577_pocos_braga_modified_p.tif' "data_externos"."pocos_braga_modified" | psql atlantico -p 5432.
This runs on the server. I know that the user role started the process on the application, but I can not use psql -U user since I don't know his credentials.
Maybe I could try something like:
raster2pgsql -s EPSG:3763 -q -d -I -C -F -M -t 512x512 -l 2,4,8 '/tmp/1410577_pocos_braga_modified_p.tif' "data_externos"."pocos_braga_modified" | psql atlantico -p 5432 -c 'set role user'.

pg_restore,COPYor some other way? 2) Why don't you import the data as userupload?`