Create database administrator role

Question

I'm using postgres 9.6. I have database with owner of postgres role. I need another user that would be the administrator on this database. This user can do anything like dml or ddl queries. Also I'm using entity framework code first and new schemas and objects in it would be arrised in my database in future. So how can I create this role?

I see documentation of postgres security and I don't understand why that simple thing is to difficult to implement.

I need to alter database owner or grant all in all tables, ok (but new objects could be created either by my user or by postgres). There is "Alter default privileges" command but it used only for specific schema(not all) So how can i implement this scenario?

Answer 1

Only the owner of an object or a superuser can perform DDL statements on an object.

So the only way to create a second user that can do that no matter who created an object would be to make that second user a superuser.

That's quite simple, but of course you should never let an application connect with a superuser.

When it comes to security, you have a choice:

1. You don't want to spend any thought on it and you don't care if you have a security problem.

2. You think about it and come up with a reasonable security concept.