Thread: Is there something like a limited superuser to give to a db-assistant?
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?
This position of an restricted db assistant is new for us and it wasn't considered in our access rights until now.
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.
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?
This position of an restricted db assistant is new for us and it wasn't considered in our access rights until now.
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.
Re: Is there something like a limited superuser to give to a db-assistant?
From
"Tomas Vondra"
Date:
On 25 Říjen 2013, 3:53, Andreas wrote: > Hi, > > how can I give adb-assistant the rights to create and drop schemas, > tables, views ... BUT keep him out of certain existing schemas and tables? > > This position of an restricted db assistant is new for us and it wasn't > considered in our access rights until now. > > 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. Hi Andreas, I think you should read this: http://www.postgresql.org/docs/9.3/static/sql-grant.html You don't need superuser privileges to create schemas and so on, and by definition "superuser" means "unrestricted". Just create a regular user and grant him CREATE on the database. You may also grant him access only to selected schemas/tables. regards Tomas
Re: Is there something like a limited superuser to give to a db-assistant?
From
Michael Paquier
Date:
On Fri, Oct 25, 2013 at 10:53 AM, Andreas <maps.on@gmx.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