Thread: grant privileges to a database
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 -----------------------------------------------------------------
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 -----------------------------------------------------------------
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 > ----------------------------------------------------------------- >
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 -----------------------------------------------------------------
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 -----------------------------------------------------------------
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 >
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.
: 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