Thread: Database permissions

Hi,

I would like to create permissions on databases. I have a server, where
different applications from different accounts use my database. It would
be nice, if a user can access his database, but not the others. I know,
that I can set up table-permissions, but no database permissions. It
seems not to be possible to prevent users creating tables in wrong
databases.

I have followed the development of postgresql and read some mails in the
mailinglist. There was a discussion about 'Granting Permissions to User
To Access Database', but this was in january 2001 and it seems, that
nothing has happend.

I would like to be able to set 'revoke all on database from username'.
Am I the only one, who needs this?

In mysql there are chances to set permissions on database. It is the
only feature, wich I found until now, which mysql does better than
postgresql.


Tommi Mäkitalo

Tommi Mäkitalo wrote:

> Hi,
>
> I would like to create permissions on databases. I have a server,
> where different applications from different accounts use my database.
> It would be nice, if a user can access his database, but not the
> others. I know, that I can set up table-permissions, but no database
> permissions. It seems not to be possible to prevent users creating
> tables in wrong databases.
>
> I have followed the development of postgresql and read some mails in
> the mailinglist. There was a discussion about 'Granting Permissions to
> User To Access Database', but this was in january 2001 and it seems,
> that nothing has happend.
>
> I would like to be able to set 'revoke all on database from username'.
> Am I the only one, who needs this?
>
> In mysql there are chances to set permissions on database. It is the
> only feature, wich I found until now, which mysql does better than
> postgresql.
>
>
> Tommi Mäkitalo
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
Have you looked at pg_hba.conf at all?
There you can setup which user, from which machine, conect to which
database.

--
Dado Feigenblatt                                 Wild Brain, Inc.
Technical Director                               (415) 216-2053
dado@wildbrain.com                               San Francisco, CA.

Am Freitag, 7. Dezember 2001 17:02 schrieb Dado Feigenblatt:
...
>
> Have you looked at pg_hba.conf at all?
> There you can setup which user, from which machine, conect to which
> database.

Ah! That's (almost) what I've missed. I have looked at pg_hba.conf, but not
not that much. I thought pg_hba.conf does authentication but no authorisation.

The way to create users for db1 and other users for db2 is to create these
users in the database and set up pg_hba.conf to look up some maps and put
these users in these maps, right?

I feel, that authorisation should be done at the engine, not outside (I
consider pg_hba.conf as outside).

What about usergroups? It would be much easier to put the users to groups and
assign connect-authorisation to these groups.

And what about performance and administration with many users? The maps,
pg_hba.conf references are flat files. They are slow to parse and hard to
administer. That's what we have a database for. Is there any chance to put
connect authorisation to the database?

Tommi Mäkitalo

Tommi Mäkitalo wrote:

>Am Freitag, 7. Dezember 2001 17:02 schrieb Dado Feigenblatt:
>...
>
>>Have you looked at pg_hba.conf at all?
>>There you can setup which user, from which machine, conect to which
>>database.
>>
>
>Ah! That's (almost) what I've missed. I have looked at pg_hba.conf, but not
>not that much. I thought pg_hba.conf does authentication but no authorisation.
>
>The way to create users for db1 and other users for db2 is to create these
>users in the database and set up pg_hba.conf to look up some maps and put
>these users in these maps, right?
>
That's one way.
I'm not very experienced with pg_hba.conf myself.
My application will run on an intranet. It is still under development so
I'm not very concerned about security right now.
I have just a few users on the DB, the developers, and the user that the
web server will use to conect.
I had just IP authentication. Recently I had to add ident authorization
and a map so that my backup/restore script could login as different
users when restoring different databases. That method is used only for
localhost connections.

>I feel, that authorisation should be done at the engine, not outside (I
>consider pg_hba.conf as outside).
>
The process that carries the authentication *is* the engine.
Whether it gets the information from the database or from a flat file is
a different story.
You wouldn't like to screw up your permissions and find yourself locked
outside of the database.
With a flat file you can still edit the file and regain access.
I don't know if that's the reason the developers decided on the external
file or if there's something else.

>What about usergroups? It would be much easier to put the users to groups and
>assign connect-authorisation to these groups.
>
I don't know if there's a way of configuring pg_hba.conf or writing maps
in such a way that you'd had that functionality.
The database offers groups internally, but that has nothing to do with
the pg_hba.conf authorization.

>And what about performance and administration with many users? The maps,
>pg_hba.conf references are flat files. They are slow to parse and hard to
>administer. That's what we have a database for. Is there any chance to put
>connect authorisation to the database?
>
I'm not sure about reading and parsing a flat file being so much slower
than accesing the database.
There's an overhead for every new connection to the database, and either
way there would be a disk access.
The advantages of databases is all the other functionality it offers
over flat files.
For data sets that are very small and simple you're better off using
flat files, sed, grep, awk, PERL,
or whatever text manipulation tool you're more confortable with.
Any guru would like to comment on this?

Anyway, I think postgreSQL, as most applications offers a very simple
implementation of user/group authentication/authorization.
I forsee I'll have to develop my own RBAC (Role Based Access Control),
which will live in the database and might be what you're looking for.
For me that's still is a multi headed beast. But one thing I know (I
think). The connecting process (Apache) will use the RBAC to decide what
data it can serve, but the process itself will always connect as the
same user. So I can keep my pg_hba.conf very simple and the entire logic
will be in my own application. I would welcome comments on this too.

I hope I was able to help, but I would look for a second opinion, after
all my experience in these areas is very limited :)

--
Dado Feigenblatt                                 Wild Brain, Inc.
Technical Director                               (415) 216-2053
dado@wildbrain.com                               San Francisco, CA.