Re: [ADMIN] Grant syntax

[Joe Conway]

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster