Database Grants Bug

Marcus England wrote:

Grants do not work at the database level using the syntax mentioned in
the documentation. i.e.:

GRANT ALL ON DATABASE dbname TO GROUP groupname;

Or

GRANT ALL ON DATABASE dbname TO username;

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

Thank you Joe.

IMHO, this is confusing and limiting for Administrators who wish to
grant privileges beyond CREATE, TEMPORARY, and TEMP across all tables in
a database. Something I believe most, if not all other DBMS's do. "ALL"
isn't very consistent.

Reading the comments in the documentation, apparently I'm not the only
one who's confused about ALL.

Please correct me if I'm missing something here.

Marcus

Marcus England wrote:

IMHO, this is confusing and limiting for Administrators who wish to
grant privileges beyond CREATE, TEMPORARY, and TEMP across all tables in
a database. Something I believe most, if not all other DBMS's do. "ALL"
isn't very consistent.

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.

Reading the comments in the documentation, apparently I'm not the only
one who's confused about ALL.

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

On Mon, 2003-08-18 at 09:29, Joe Conway wrote:

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.

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

Marcus England wrote:

On Mon, 2003-08-18 at 09:29, Joe Conway wrote:

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.

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?

AFAIR pgAdmin2 does have a grant utility for this. pgAdmin3 has this on
the TODO for the next version.

Regards,
Andreas

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

AFAIR pgAdmin2 does have a grant utility for this. pgAdmin3 has this on
the TODO for the next version.

Regards,
Andreas

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

Thanks,

Marcus