pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Sam Barnett-Cormack <s.barnett-cormack@lancaster.ac.uk>
To: Randall Perry <rgp@systame.com>
Cc: olly@lfix.co.uk
Cc: pgsql-admin@postgresql.org
Subject: Re: How do I grant access to entire database at
Date: Mon, 26 Jul 2004 20:26:30 +0100 (BST)
Message-ID: <Pine.LNX.4.58.0407262023240.1995@localhost.localdomain> (raw)
In-Reply-To: <BD2ACAC3.7F3D2%rgp@systame.com>
References: <BD2ACAC3.7F3D2%rgp@systame.com>

On Mon, 26 Jul 2004, Randall Perry wrote:

> Thanks, I'll use it.
>
> But, if the developer's are listening -- this is really obtuse. MySQL
> administration is much easier. Please consider simplifying the GRANT process
> for future revs.

I do agree with this, actually - in fact, let me expand.

GRANT out to have a varying degree of granularity, IMO. So you can
specify individual sequences or tables, a glob (handy for
table+sequence), possibly an intelligent way of including anything
requisite for a given table, a whole DB, a whole namespace, a whole
cluster (although then you could just make the user a superuser), all of
the members of a DB/namespace that are of a certain type (say,
tables+sequences, or functions-only, or whatever).

This isn't just useful for easing the way one grants access to prevent
unauthorised access, but also for creating users that prevent an
individual from doing things accidentally - so a given developer might
have several accounts with different privs - a bit like the way we only
su to root, not log in as root.

It's one of these little niggles that, for me, prevents Postgres being
unassailably the best FOSS database.

> on 7/18/04 4:41 PM, Oliver Elphick at olly@lfix.co.uk wrote:
>
> > On Sun, 2004-07-18 at 20:52, Randall Perry wrote:
> >> This is a pain. Couldn't we gave something simple like
> >> GRANT ALL ON database.* TO JOE;
> >>
> >> Which would grant full access to all objects in the database to JOE for all
> >> time?
> >
> > You can do it like this in psql:
> >
> > \a
> > \t
> > \o /tmp/grant.sql
> > SELECT      'GRANT ALL ON ' || n.nspname || '.' || c.relname ||
> >           ' TO joe;'
> > FROM      pg_catalog.pg_class AS c
> >           LEFT JOIN pg_catalog.pg_namespace AS n
> >                ON n.oid = c.relnamespace
> > WHERE     c.relkind IN ('r','v','S') AND
> >           n.nspname NOT IN ('pg_catalog', 'pg_toast') AND
> >           pg_catalog.pg_table_is_visible(c.oid)
> > ORDER BY  n.nspname, c.relname;
> > \o
> > \i /tmp/grant.sql
> >
> >
> > The above could be put in a script and run from a Unix command prompt.
> >
> > (The SQL used above is adaated from that used by psql's \d command.)
>
>

-- 

Sam Barnett-Cormack
Software Developer                           |  Student of Physics & Maths
UK Mirror Service (http://www.mirror.ac.uk)  |  Lancaster University



view thread (11+ messages)  latest in thread

Message-ID: <Pine.LNX.4.58.0407262023240.1995@localhost.localdomain>
Permalink:  ../Pine.LNX.4.58.0407262023240.1995@localhost.localdomain/
Also on:    postgresql.org/message-id/Pine.LNX.4.58.0407262023240.1995@localhost.localdomain

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: s.barnett-cormack@lancaster.ac.uk, rgp@systame.com, olly@lfix.co.uk
  Subject: Re: How do I grant access to entire database at
  In-Reply-To: <Pine.LNX.4.58.0407262023240.1995@localhost.localdomain>
  References: <BD2ACAC3.7F3D2%rgp@systame.com> <Pine.LNX.4.58.0407262023240.1995@localhost.localdomain>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox