Marcus England wrote:
Works here:

regression=# select version();
version
-----------------------------------------------------------------
PostgreSQL 7.3.3 on i686-redhat-linux-gnu, compiled by GCC 2.96
(1 row)

regression=# GRANT ALL ON DATABASE regression TO GROUP grp1;
GRANT
regression=# GRANT ALL ON DATABASE regression TO user1;
GRANT

You need to be more specific in what you mean by "do not work". Do you
get an error? What exactly is not working?

Perhaps you expect more than you should -- re-read the docs,
specifically the section quoted here:

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

In the context of DATABASE, ALL means "CREATE & TEMPORARY & TEMP",
nothing more, nothing less. Further reading provides:

CREATE
For databases, allows new schemas to be created within the database.

TEMPORARY
TEMP
Allows temporary tables to be created while using the database.

Are these not working?

HTH,

Joe


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

On Mon, 2003-08-18 at 09:29, Joe Conway wrote:
I guess I meant the ability to grant permissions easily at the DB level.
It's trivial in SQL Server via Enterprise Manager - no SQL needed. I
assume DB2 and Oracle have similar facilities, not necessarily in SQL.
Perhaps pgadmin has this ability?

Thanks a lot for the help/clarification and the function,

Marcus



---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Marcus England wrote:
Again, I don't know what your definition of "most, if not all other
DBMS's" is, but a quick read through my MSSQL2000 manual indicates SQL
Server is no different from Postgres in this regard. Same for Oracle 9i.
I'd say that covers the majority of DBMS installations. I don't have a
DB2 manual handy to check.

True, it seems to come up reasonably frequently. But the docs are pretty
clear if you read them carefully.

And if you search the mailing list archives, you'll find more than one
script or function posted that allows GRANTs on all the tables in a
database, for instance (including one by me). The function is pretty
simple; here it is again for your convenience (not extensively tested --
use at your own risk, modify to suit, etc, etc):

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');


Joe


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Marcus England wrote:

the TODO for the next version.

Regards,
Andreas


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

On Mon, 2003-08-18 at 10:31, Andreas Pflug wrote:

I just used pgAdmin2's security wizard for this. Very nice. Just what I
needed.

Thanks,

Marcus


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