Thread: Database Grants Bug
========================================================================
===
POSTGRESQL BUG REPORT TEMPLATE
========================================================================
====
Your name : Marcus England
Your email address : marcus.england@nospam.noaa.gov
System Configuration
---------------------
Architecture (example: Intel Pentium) : Intel Pentium
Operating System (example: Linux 2.0.26 ELF) : Linux 2.4.20 Redhat
PostgreSQL version (example: PostgreSQL-7.3.4): PostgreSQL-7.3.4
Compiler used (example: gcc 2.95.2) : gcc 3.2.2
Please enter a FULL description of your problem:
------------------------------------------------
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;
No errors are reported.
Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
Using any database with any user or group should do the trick.
If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
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
On Sun, 2003-08-17 at 22:48, Joe Conway wrote:
> 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
>
>
> ---------------------------(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: > 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:
> 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
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