Re: Grant syntax

Naomi Walker wrote:
>
> I need to grant access to all tables for all users on a particular
> database.  I've tried:
>
> GRANT ALL ON databasename to public;
>
> But it complained the databasebase (relation) does not exist.  Do I have to
> grant on each table in a separate statement?  I'm guessing not.
>

The syntax for grant on a database is this:

GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] }
      ON DATABASE dbname [, ...]
      TO { username | GROUP groupname | PUBLIC } [, ...]

but it doesn't appear that's what you were hoping for.

If you are trying to GRANT privileges to tables, I'm afraid you do have
to do them one at a time, or write a function to automate it for you.

Here's a function that I've posted previously:

CREATE OR REPLACE FUNCTION grant_all(text) RETURNS TEXT AS '
DECLARE
   rel record;
   sql text;
BEGIN
   FOR rel IN SELECT pg_catalog.quote_ident(c.relname) AS relname FROM
pg_catalog.pg_class c WHERE c.relkind = ''r'' AND c.relnamespace NOT IN
(select oid from pg_catalog.pg_namespace where nspname like ''pg\_%'')
AND pg_catalog.pg_table_is_visible(c.oid) LOOP
     sql := ''grant all on '' || rel.relname || '' to '' || $1;
     RAISE NOTICE ''%'', sql;
     EXECUTE sql;
   END LOOP;
   RETURN ''OK'';
END;
' LANGUAGE 'plpgsql';

create user foo;
select grant_all('foo');

HTH,

Joe