Postgres Pro
Downloads
Home   >   mailing lists

Thread: Grant on Database?

Grant on Database?

From
David Wheeler
Date:
Hey All,

We have a need to grant privileges on entire databases to users and/or
groups. It looks like GRANT just grants on tables and sequences, but I'd
like to know if there's a more direct way to do it. What I'm doing now
is getting a list of tables and sequences and calling grant for each one
in turn. How am I getting this list (I'm user Perl, not psql)? With this
query:

        SELECT relname
        FROM   pg_class
        WHERE  relkind IN ('r', 'S')
               AND relowner IN (
                   SELECT usesysid
                   FROM   pg_user
                   WHERE  LOWER(usename) = 'myuser')

Anyway, pointers to any shortcuts for this would be greatly appreciated.

Thanks,

David

--
David Wheeler
Software Engineer
Creation Engines, Inc.                             ICQ:   15726394
david@creationengines.com                          AIM:   dwTheory

Re: Grant on Database?

From
"Dan Wilson"
Date:
> Hey All,
>
> We have a need to grant privileges on entire databases to users and/or
> groups. It looks like GRANT just grants on tables and sequences, but I'd
> like to know if there's a more direct way to do it. What I'm doing now
> is getting a list of tables and sequences and calling grant for each one
> in turn. How am I getting this list (I'm user Perl, not psql)? With this
> query:
>
>         SELECT relname
>         FROM   pg_class
>         WHERE  relkind IN ('r', 'S')
>                AND relowner IN (
>                    SELECT usesysid
>                    FROM   pg_user
>                    WHERE  LOWER(usename) = 'myuser')
>
> Anyway, pointers to any shortcuts for this would be greatly appreciated.

First pointer, phpPgAdmin (http://www.greatbridge.org/project/phppgadmin)
has this built into it.  It will automatically get the list of tables,
sequences and views and run a grant statment on them.

Second pointer.  GRANT will take multiple "relations" seperated by commas:

GRANT ALL ON table1, table1, seq1, seq2, view1, view2 TO my_user;

-Dan

Re: Grant on Database?

From
David Wheeler
Date:
On Wed, 21 Feb 2001, Dan Wilson wrote:

> First pointer, phpPgAdmin (http://www.greatbridge.org/project/phppgadmin)
> has this built into it.  It will automatically get the list of tables,
> sequences and views and run a grant statment on them.

Nice. I need something that works from the shell, though. My perl script
is doing the trick for now - I just wanted to make sure that there wasn't
an official way I was missing. BTW, did my query of the pg_class table
look right? I know I was missing views - we don't have any yet!

> Second pointer.  GRANT will take multiple "relations" seperated by commas:
>
> GRANT ALL ON table1, table1, seq1, seq2, view1, view2 TO my_user;

Cool, that saves me a lot of query overhead - I can grant them all at
once!

Thanks,

David

--
David Wheeler
Software Engineer
Salon Internet                                     ICQ:   15726394
david@salon.com                                    AIM:   dwTheory