0

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:

enter image description here

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'.

2
  • 1
    That cannot be done directly. Please answer these questions: 1) How do you load data? With pg_restore, COPY or some other way? 2) Why don't you import the data as user upload?` Commented Sep 26 at 9:37
  • Maybe unix.stackexchange.com/questions/728313/… helps. Commented Sep 26 at 10:14

1 Answer 1

0

Thanks @laurenz-albe for your valuable feedback. If you say that this can not be done directly, I started searching for another solution.

Since I know the user and I'm running the import as postgres, I can do a set role user before the upload. With this, the table is created by the correct role and default privileges in place works as expected.

I've change the upload to (example):

raster2pgsql -s EPSG:3763 -q -d -I -C -F -M -t 512x512 -l 2,4,8  '/tmp/983693_Leixoes_8m.tiff' "data_externos"."983693_Leixoes_8m.tiff" | PSQLRC=<(echo "SET role 'upload'") psql atlantico -p 5632
1
  • 1
    Right, I had wanted to suggest something like that, but I needed to know what tools you are using to determine if it can be done and how. Commented Sep 26 at 10:39

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.