Is there a way to grant a user with all privileges on a database? Something 
like Informixs GRANT dba?

-- 
System Administration: It's a dirty job, 
but someone told I had to do it.
-----------------------------------------------------------------
Martï¿½n Marquï¿½s			email: 	martin@math.unl.edu.ar
Santa Fe - Argentina		http://math.unl.edu.ar/~martin/
Administrador de sistemas en math.unl.edu.ar
-----------------------------------------------------------------

Tue, 30 Jan 2001 10:08:08 -0300
"Martin A. Marques" <martin@math.unl.edu.ar>
grant privileges to a database
El Mar 30 Ene 2001 10:08, Martin A. Marques escribiï¿½:
> Is there a way to grant a user with all privileges on a database? Something
> like Informixs GRANT dba?

OK, I see that no one responded (except one person how made a personal 
responce), so I'll add a bit to it.
I seem to be aware that Postgres doesn't have a function to grant total 
access to a database, so is it posible to have this added to the todo list 
for further versions? Or is it posible to build a user-function that will let 
me do this:

postgres@ultra3:~ > psql horde

horde=# CREATE DATABASE test;
CREATE DATABASE
horde=# GRANT dba TO martin; -- here is the grant issue.
GRANT
horde=# \c test martin;
You are now connected to database test as user martin.
test=# CREATE TABLE (aid SERIAL, texto TEXT);
CREATE TABLE
test=#

Saludos... ;-)


-- 
System Administration: It's a dirty job, 
but someone told I had to do it.
-----------------------------------------------------------------
Martï¿½n Marquï¿½s			email: 	martin@math.unl.edu.ar
Santa Fe - Argentina		http://math.unl.edu.ar/~martin/
Administrador de sistemas en math.unl.edu.ar
-----------------------------------------------------------------

Wed, 31 Jan 2001 15:02:08 -0300
"Martin A. Marques" <martin@math.unl.edu.ar>
Re: grant privileges to a database
This is the closest thing to what you want:

GRANT ALL ON table TO user;

(see http://www.postgresql.org/users-lounge/docs/7.0/user/sql-grant.htm)

Michael Fork - CCNA - MCP - A+
Network Support - Toledo Internet Access - Toledo Ohio

On Wed, 31 Jan 2001, Martin A. Marques wrote:

> El Mar 30 Ene 2001 10:08, Martin A. Marques escribi�:
> > Is there a way to grant a user with all privileges on a database? Something
> > like Informixs GRANT dba?
> 
> OK, I see that no one responded (except one person how made a personal 
> responce), so I'll add a bit to it.
> I seem to be aware that Postgres doesn't have a function to grant total 
> access to a database, so is it posible to have this added to the todo list 
> for further versions? Or is it posible to build a user-function that will let 
> me do this:
> 
> postgres@ultra3:~ > psql horde
> 
> horde=# CREATE DATABASE test;
> CREATE DATABASE
> horde=# GRANT dba TO martin; -- here is the grant issue.
> GRANT
> horde=# \c test martin;
> You are now connected to database test as user martin.
> test=# CREATE TABLE (aid SERIAL, texto TEXT);
> CREATE TABLE
> test=#
> 
> Saludos... ;-)
> 
> 
> -- 
> System Administration: It's a dirty job, 
> but someone told I had to do it.
> -----------------------------------------------------------------
> Mart�n Marqu�s			email: 	martin@math.unl.edu.ar
> Santa Fe - Argentina		http://math.unl.edu.ar/~martin/
> Administrador de sistemas en math.unl.edu.ar
> -----------------------------------------------------------------
> 


Wed, 31 Jan 2001 13:53:04 -0500 (EST)
Michael Fork <mfork@toledolink.com>
Re: Re: grant privileges to a database
El Miï¿½ 31 Ene 2001 15:53, Michael Fork escribiï¿½:
> This is the closest thing to what you want:
>
> GRANT ALL ON table TO user;
>
> (see http://www.postgresql.org/users-lounge/docs/7.0/user/sql-grant.htm)

Yes, I'm aware of that. That's why I'm asking for an opinion of the 
developers on this issue.
I think it would be a nice feature, so that the postgres user creates the 
database and gives all kind of permissions on that database to another user, 
which doesn't have to be able to create databases.

Any comments?

-- 
System Administration: It's a dirty job, 
but someone told I had to do it.
-----------------------------------------------------------------
Martï¿½n Marquï¿½s			email: 	martin@math.unl.edu.ar
Santa Fe - Argentina		http://math.unl.edu.ar/~martin/
Administrador de sistemas en math.unl.edu.ar
-----------------------------------------------------------------

Wed, 31 Jan 2001 16:41:59 -0300
"Martin A. Marques" <martin@math.unl.edu.ar>
Re: Re: grant privileges to a database
You can do this in phpPgAdmin... it's a hack because it just pulls in all
the objects/relations and runs a single grant statement on them, but it
works.  It puts together a query like the following:

GRANT ALL ON table1, table2, table3, view1, view2, sequence1, sequence2 TO
user

Which I suppose you can do manually if you don't have phpPgAdmin installed.

It ain't the prettiest, but it works!

-Dan

: El Miï¿½ 31 Ene 2001 15:53, Michael Fork escribiï¿½:
: > This is the closest thing to what you want:
: >
: > GRANT ALL ON table TO user;
: >
: > (see http://www.postgresql.org/users-lounge/docs/7.0/user/sql-grant.htm)
:
: Yes, I'm aware of that. That's why I'm asking for an opinion of the
: developers on this issue.
: I think it would be a nice feature, so that the postgres user creates the
: database and gives all kind of permissions on that database to another
user,
: which doesn't have to be able to create databases.



Wed, 31 Jan 2001 14:32:27 -0700
"Dan Wilson" <phpPgAdmin@acucore.com>
Re: Re: grant privileges to a database
El Miï¿½ 31 Ene 2001 18:32, Dan Wilson escribiï¿½:
> You can do this in phpPgAdmin... it's a hack because it just pulls in all
> the objects/relations and runs a single grant statement on them, but it
> works.  It puts together a query like the following:
>
> GRANT ALL ON table1, table2, table3, view1, view2, sequence1, sequence2 TO
> user
>
> Which I suppose you can do manually if you don't have phpPgAdmin installed.
>
> It ain't the prettiest, but it works!

The problem is that this is not what I'm looking for. I want the user to be 
able to create new tables, views, sequences, etc on that database.

-- 
System Administration: It's a dirty job, 
but someone told I had to do it.
-----------------------------------------------------------------
Martï¿½n Marquï¿½s			email: 	martin@math.unl.edu.ar
Santa Fe - Argentina		http://math.unl.edu.ar/~martin/
Administrador de sistemas en math.unl.edu.ar
-----------------------------------------------------------------

Wed, 31 Jan 2001 19:18:12 -0300
"Martin A. Marques" <martin@math.unl.edu.ar>
Re: Re: grant privileges to a database
: El Miï¿½ 31 Ene 2001 18:32, Dan Wilson escribiï¿½:
: > You can do this in phpPgAdmin... it's a hack because it just pulls in
all
: > the objects/relations and runs a single grant statement on them, but it
: > works.  It puts together a query like the following:
: >
: > GRANT ALL ON table1, table2, table3, view1, view2, sequence1, sequence2
TO
: > user
: >
: > Which I suppose you can do manually if you don't have phpPgAdmin
installed.
: >
: > It ain't the prettiest, but it works!
:
: The problem is that this is not what I'm looking for. I want the user to
be
: able to create new tables, views, sequences, etc on that database.

Oh, if you want to do that, then you don't have to do any granting of
priviledges.  It seems that Postgres allows any user to create a table on a
database.  Even if the user is not the owner of the database.  AFAIK, there
are no acl's associated with the database.

I've posed this question before and have not received any response, but is
this an undocumented feature or a sercurity bug?  Personally, I don't think
anyone should be able to create relations on a database they do not own.

-Dan


Wed, 31 Jan 2001 15:39:46 -0700
"Dan Wilson" <phpPgAdmin@acucore.com>
Re: Re: grant privileges to a database
On Wed, Jan 31, 2001 at 03:39:46PM -0700, some SMTP stream spewed forth: 
> : El Miï¿½ 31 Ene 2001 18:32, Dan Wilson escribiï¿½:
> : > You can do this in phpPgAdmin... it's a hack because it just pulls in
> all
> : > the objects/relations and runs a single grant statement on them, but it
> : > works.  It puts together a query like the following:
> : >
> : > GRANT ALL ON table1, table2, table3, view1, view2, sequence1, sequence2
> TO
> : > user
> : >
> : > Which I suppose you can do manually if you don't have phpPgAdmin
> installed.
> : >
> : > It ain't the prettiest, but it works!
> :
> : The problem is that this is not what I'm looking for. I want the user to
> be
> : able to create new tables, views, sequences, etc on that database.
> 
> Oh, if you want to do that, then you don't have to do any granting of
> priviledges.  It seems that Postgres allows any user to create a table on a

Er, to delete anything, the user would need to be a superuser.
Else, nyet, not necessary.

> database.  Even if the user is not the owner of the database.  AFAIK, there
> are no acl's associated with the database.

For the heck of it, I will certify that this is correct.

> 
> I've posed this question before and have not received any response, but is
> this an undocumented feature or a sercurity bug?  Personally, I don't think
> anyone should be able to create relations on a database they do not own.

It is both, depending on how you use it. ;-)

I would and do consider it a blindingly silly security risk, but 
apparently nobody else does. I asked before, but...
Just why the hell would somebody want *any* user of *any* database to be 
able to *create* anything under *any* other database?!?


dan

;-)

> 
> -Dan
> 

Wed, 31 Jan 2001 17:44:28 -0600
GH <grasshacker@over-yonder.net>
Re: Re: grant privileges to a database