Skip site navigation (1) Skip section navigation (2)

Re: How do I grant access to entire database at

From: Oliver Elphick <olly(at)lfix(dot)co(dot)uk>
To: Randall Perry <rgp(at)systame(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: How do I grant access to entire database at
Date: 2004-07-18 20:41:51
Message-ID: 1090183311.25749.98.camel@linda (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
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:

\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
  ORDER BY  n.nspname, c.relname;
\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.)

Oliver Elphick                                          olly(at)lfix(dot)co(dot)uk
Isle of Wight                    
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
     "For God so loved the world, that he gave his only 
      begotten Son, that whosoever believeth in him should 
      not perish, but have everlasting life."     John 3:16 

In response to


pgsql-admin by date

Next:From: Scott MarloweDate: 2004-07-18 22:37:06
Subject: Re: Help - Error in createdb
Previous:From: Simon RiggsDate: 2004-07-18 20:20:52
Subject: Re: [HACKERS] Point in Time Recovery

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group