Re: Is there something like a limited superuser to give to a db-assistant?

On Fri, Oct 25, 2013 at 10:53 AM, Andreas <maps(dot)on(at)gmx(dot)net> wrote:
> Hi,
>
> how can I give a db-assistant the rights to create and drop schemas, tables,
> views ... BUT keep him out of certain existing schemas and tables?
Depending on what you want to do, you will have to use a combination
GRANT and REVOKE to authorize and to restrict access to multiple
database objects:
http://www.postgresql.org/docs/9.3/static/sql-grant.html
http://www.postgresql.org/docs/9.3/static/sql-revoke.html

> Our DB got a wee bit komplex with about 400 schemas and 5000 tables.
> So it would get rather ugly having to alter the rights for every db-object
> one by one manually.
So... First grant the access to all the objects for this new user, and
then use revoke on each object individually you want to restrict for
him. It would be better to do that at the schema level perhaps...
However the risk here is to forget to restrict the access to some
objects... So for safety you should do it the other way around.
Regards,
--
Michael