Обсуждение: 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.

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

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