From: | Joe Conway <mail(at)joeconway(dot)com> |
---|---|
To: | Naomi Walker <nwalker(at)eldocomp(dot)com> |
Cc: | "pgsql-admin(at)postgresql(dot)org" <pgsql-admin(at)postgresql(dot)org> |
Subject: | Re: Grant syntax |
Date: | 2003-07-07 21:44:57 |
Message-ID: | 3F09E9D9.60104@joeconway.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
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
From | Date | Subject | |
---|---|---|---|
Next Message | scott.marlowe | 2003-07-07 22:01:12 | Re: PostgreSQL settings for 12GB RAM |
Previous Message | Naomi Walker | 2003-07-07 18:33:32 | Grant syntax |