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