I have an application which uses a postgres database. I have a superadmin user. Now I need two more users: One "application-user" with CRUD-privileges and one with ALTER and CREATE-privileges (to apply migrations). These are all users I need, because the application has its own User-Access management and it is not at all planned to change that.

I want something like: GRANT SELECT, INSERT, UPDATE, DELETE ON DATABASE MyDatabase TO myuser

I've read here that postgres provides pre defined roles. This is good - but these roles apply globally (as pointed out in one comment). MyDatabase is on public schema which becomes problematic because some system tables are on public too - and I don't want myuser to be able to read from or write to these.

I'd be fine with GRANT pg_read_all_data, pg_write_all_data ON DATABASE MyDatabase TO myuser but this doesn't work.

As I'll not change these privileges often I'd even be fine with GRANT pg_read_all_data ON MyDatabase.MyTable TO myuser as well. But this doesn't work either.

Any ideas on this?

CodePudding user response：

There are no ALTER and CREATE privileges in PostgreSQL. The database user that should be able to run ALTER and CREATE statements will have to be the owner of the database objects. If you already have objects owned by a different user, you will have to change the ownership.

For the other user, you will have to grant privileges on each and every object. Privileges on the database won't help – there is no inheritance of privileges between objects. Don't forget to grant USAGE on the schemas.

I recommend that you create more schemas than public. If you have a separate schema for your application's objects, you can use statements like

GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA myapp TO someuser;