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
-----------------------------------------------------------------

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 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
-----------------------------------------------------------------

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.

--
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.

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

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

;-)

MySQL has this feature. I run a multi-user system and require shared MySQL
And PostgreSQL dbs. I have mysql fine. Users have their DBs and can only
access their DBs... but theres no real way to do this in Postgres. I can
restrict tables, but I can still create tables in other poeples DBs. Maybe
only allowed to acess DBs you create or are assigned permission to

--
Mike

From: "Martin A. Marques" <martin@math.unl.edu.ar>
To: Michael Fork <mfork@toledolink.com>
CC: pgsql-hackers@postgresql.org, pgsql-general@postgresql.org
Subject: Re: Re: grant privileges to a database
Date: Wed, 31 Jan 2001 16:41:59 -0300

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
-----------------------------------------------------------------

_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.

El Mi� 31 Ene 2001 21:32, Mike Miller escribi�:

MySQL has this feature. I run a multi-user system and require shared MySQL
And PostgreSQL dbs. I have mysql fine. Users have their DBs and can only
access their DBs... but theres no real way to do this in Postgres. I can
restrict tables, but I can still create tables in other poeples DBs. Maybe
only allowed to acess DBs you create or are assigned permission to

This is what I'm talking about (sort of) and I find it a handy thing that
informix has since at least 1995.
Lets say I have a database server and I create databases for diferent groups
of people to populate. Thtas what grant dba would be usefull for.

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
-----------------------------------------------------------------

On Wed, Jan 31, 2001 at 07:18:12PM -0300, Martin A. Marques wrote:

El Mi� 31 Ene 2001 18:32, Dan Wilson escribi�:

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

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.

This is nbow what people call security. Normaly only the dba (database administrator)
is allowed to create tables. But everyone can create tables, views and
sequences. The objects are than only accessible to that user. The user who
created the table has also set the security on that object.

I large company where i work, developers arent allowed to create objects on
the database, this because developers make way to soon objects or change
current tables, so the whole application doesn't work anymore. We have an
user 'postgres'. Only 4 people (there are working 72 people here) have the
ssh-key to login as postgres on the database. User postgres is the owner of
all the objects in every database. User postgres give the proper security
settings for each remote user (normaly websites, applications, etc).

I now, i looks bad, but it's really a good thing

Dave Mertens
Unix System Administrator

Hello

A few weeks ago I was interested in this question. My results were:
- Yes, this is a sorrowful but true fact that if you enable access to
someone to a database, she is automatically enabled to create
objects in it.
- Yes, the developers know it, and they said: there is a patch existing
to workaround it.
- No, they don't include it in 7.1. The reason: if you use that patch,
pg_dumpall will not work. If somebody will have the strength in
him to fix it, than it will be considered to include it in the base.

After collecting these informations from more experienced people,
I calmed down. Since I am in the beginning of creating my project,
I think for the time when I will need it, it will be ready.

Anyway, I do not know where this patch is. If you don't bother
about pg_dumpall, ask a developer (a am only a wannabe developer)
about it.

If anyone detects that I wrote silly things, please do correct me.

Bye,
Baldvin

El Lun 05 Feb 2001 16:13, Kovacs Baldvin escribi�:

Hello

A few weeks ago I was interested in this question. My results were:

I was thinking myself, and thought about this:
can't the dba access be controled from the hda.conf file for each database?

Saludos... :-)

- Yes, this is a sorrowful but true fact that if you enable access to
someone to a database, she is automatically enabled to create
objects in it.
- Yes, the developers know it, and they said: there is a patch existing
to workaround it.
- No, they don't include it in 7.1. The reason: if you use that patch,
pg_dumpall will not work. If somebody will have the strength in
him to fix it, than it will be considered to include it in the base.

After collecting these informations from more experienced people,
I calmed down. Since I am in the beginning of creating my project,
I think for the time when I will need it, it will be ready.

Anyway, I do not know where this patch is. If you don't bother
about pg_dumpall, ask a developer (a am only a wannabe developer)
about it.

If anyone detects that I wrote silly things, please do correct me.

Bye,
Baldvin

--
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
-----------------------------------------------------------------

Hrm- I'd love to know where this patch is. I don't see how that quite
breaks PG_DUMPALL though. Really if your logged in as a superuser
(postgres) you should be able to use all the databases and dump all of the
data. Am I the only one that doesn't see where the problem is? How about a
patch that says 'if the user that created the database is not the current
user, then reject- otherwise accept'. I could go for that. Though access
control would be nice, I could log in as a superuser, make a user with the
ability to make databases, login as that user, make the databases I need,
then login as postgres and revoke the privilages of creating databases.
Suddenly you can only access databases you created and its as easy as that
(a few PHP lines if you ask me) to make new databases. Wouldn't it just be
a simple IF statement to see if the current user is the database owner [or
if they have the superuser ID set]?

Am I not seeing the big picture?

--
Mike

From: Kovacs Baldvin <kb136@hszk.bme.hu>
To: Mike Miller <temp6453@hotmail.com>
CC: martin@math.unl.edu.ar, mfork@toledolink.com,
pgsql-hackers@postgresql.org, pgsql-general@postgresql.org
Subject: Re: [HACKERS] Re: Re: grant privileges to a database [URGENT]
Date: Mon, 5 Feb 2001 20:13:38 +0100 (MET)

Hello

A few weeks ago I was interested in this question. My results were:
- Yes, this is a sorrowful but true fact that if you enable access to
someone to a database, she is automatically enabled to create
objects in it.
- Yes, the developers know it, and they said: there is a patch existing
to workaround it.
- No, they don't include it in 7.1. The reason: if you use that patch,
pg_dumpall will not work. If somebody will have the strength in
him to fix it, than it will be considered to include it in the base.

After collecting these informations from more experienced people,
I calmed down. Since I am in the beginning of creating my project,
I think for the time when I will need it, it will be ready.

Anyway, I do not know where this patch is. If you don't bother
about pg_dumpall, ask a developer (a am only a wannabe developer)
about it.

If anyone detects that I wrote silly things, please do correct me.

Bye,
Baldvin

_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.

A step in the right direction for this to have the system catalog have
pg_user_* views. So dor databases we have:

create view pg_user_database as
select * from pg_database where pg_get_userbyid(datdba) = CURRENT_USER

Of course, this doesn't account for superusers, but I'm sure there is a way
the gurus can accomplish that.

-Dan

----- Original Message -----
From: "Mike Miller" <temp6453@hotmail.com>
To: <kb136@hszk.bme.hu>
Cc: <martin@math.unl.edu.ar>; <mfork@toledolink.com>;
<pgsql-hackers@postgresql.org>; <pgsql-general@postgresql.org>
Sent: Monday, February 05, 2001 8:04 PM
Subject: [GENERAL] Re: [HACKERS] Re: Re: grant privileges to a database
[URGENT]

Hrm- I'd love to know where this patch is. I don't see how that quite
breaks PG_DUMPALL though. Really if your logged in as a superuser
(postgres) you should be able to use all the databases and dump all of the
data. Am I the only one that doesn't see where the problem is? How about

a

patch that says 'if the user that created the database is not the current
user, then reject- otherwise accept'. I could go for that. Though access
control would be nice, I could log in as a superuser, make a user with the
ability to make databases, login as that user, make the databases I need,
then login as postgres and revoke the privilages of creating databases.
Suddenly you can only access databases you created and its as easy as that
(a few PHP lines if you ask me) to make new databases. Wouldn't it just

be