Thread: Role based database access
Hello! I face a problem here with restricting access to databases to the owners of the database (Postgres 8.1.4). We would like any postgres user to have database access restricted to their own databases only. This is so, as we use postgres for educational purposes. I looked into pg_hba.conf, but unfortunately, when restricting database access in the database field, the NAME(!) of the database is concerned, which we do not restrict. Any user can create as many databases as needed, and the name may be choosen freely. The 'user' field does not help in this either. So is there any chance to achieve what we need without revoking the 'create database' permission and pre-creating the databases for all our users? Thanks, Thomas Mack
Thomas Mack wrote: > Hello! > > I face a problem here with restricting access to databases to > the owners of the database (Postgres 8.1.4). > > We would like any postgres user to have database access restricted > to their own databases only. This is so, as we use postgres for > educational purposes. > > I looked into pg_hba.conf, but unfortunately, when restricting > database access in the database field, the NAME(!) of the database > is concerned, which we do not restrict. Any user can create as > many databases as needed, and the name may be choosen freely. In the database name you can specify "@file", which points to a file containing a list of database names that the pg_hba.conf lines applies to. One idea is to have the database creation routine put a line into that file (which would be specific to each user). -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Am Donnerstag, 24. Mai 2007 15:57 schrieben Sie: > Thomas Mack wrote: > > Hello! > > > > I face a problem here with restricting access to databases to > > the owners of the database (Postgres 8.1.4). > > > > We would like any postgres user to have database access restricted > > to their own databases only. This is so, as we use postgres for > > educational purposes. > > > > I looked into pg_hba.conf, but unfortunately, when restricting > > database access in the database field, the NAME(!) of the database > > is concerned, which we do not restrict. Any user can create as > > many databases as needed, and the name may be choosen freely. > > In the database name you can specify "@file", which points to a file > containing a list of database names that the pg_hba.conf lines applies > to. One idea is to have the database creation routine put a line into > that file (which would be specific to each user). Ok, this looks reasonable. It probably also means, postgres cannot really restrict database access based on the ownership, which is not nice in this case. But probably no one cares in 'real world' situations. Thanks, Thomas Mack TU Braunschweig, Institut für Informationssysteme
Thomas Mack wrote: > Hello! > > I face a problem here with restricting access to databases to > the owners of the database (Postgres 8.1.4). > > We would like any postgres user to have database access restricted > to their own databases only. This is so, as we use postgres for > educational purposes. > > I looked into pg_hba.conf, but unfortunately, when restricting > database access in the database field, the NAME(!) of the database > is concerned, which we do not restrict. Any user can create as > many databases as needed, and the name may be choosen freely. > > The 'user' field does not help in this either. > > So is there any chance to achieve what we need without revoking > the 'create database' permission and pre-creating the databases > for all our users? > > Thanks, > Thomas Mack > Does not the sameuser database name work for you in pg_hba.conf? I.e. you can only log into the database that bears your name.
Hello, I'm not sure why wish not to pre-create a database for each student and limiting them to that database with the no create database priv. Depending on why you want to do that, I think you might be able to give each user a schema. I think access to schemas has to be explicitly granted. We've had students create other schemas instead of using "public" and it really causes problems when they want to grant users access to their databases. In your case, this might work to your advantage. Carol Walter On May 24, 2007, at 2:31 PM, Scott Marlowe wrote: > Thomas Mack wrote: >> Hello! >> >> I face a problem here with restricting access to databases to >> the owners of the database (Postgres 8.1.4). >> >> We would like any postgres user to have database access restricted >> to their own databases only. This is so, as we use postgres for >> educational purposes. >> >> I looked into pg_hba.conf, but unfortunately, when restricting >> database access in the database field, the NAME(!) of the database >> is concerned, which we do not restrict. Any user can create as >> many databases as needed, and the name may be choosen freely. >> >> The 'user' field does not help in this either. >> >> So is there any chance to achieve what we need without revoking >> the 'create database' permission and pre-creating the databases >> for all our users? >> >> Thanks, >> Thomas Mack >> > Does not the sameuser database name work for you in pg_hba.conf? > I.e. you can only log into the database that bears your name. > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings
> Thomas Mack wrote: >> Hello! >> >> I face a problem here with restricting access to databases to >> the owners of the database (Postgres 8.1.4). >> >> We would like any postgres user to have database access restricted >> to their own databases only. This is so, as we use postgres for >> educational purposes. >> >> I looked into pg_hba.conf, but unfortunately, when restricting >> database access in the database field, the NAME(!) of the database >> is concerned, which we do not restrict. Any user can create as >> many databases as needed, and the name may be choosen freely. >> >> The 'user' field does not help in this either. >> >> So is there any chance to achieve what we need without revoking >> the 'create database' permission and pre-creating the databases >> for all our users? >> >> Thanks, >> Thomas Mack >> > Does not the sameuser database name work for you in pg_hba.conf? I.e. > you can only log into the database that bears your name. > Well no. As soon as they own more than one database, or when they use a different database name, it does not work out anymore. And yes, we can force them by some means or the other to just use one database with the correct name. But it would be nice, if we wouldn't have to. The work around with a custom 'createdb' command looks nice at first, but as one can use 'create database' as an SQL command, it does not really help out. So currently, I think I will leave it with 'sameuser' or similar, and let the students know about the database name. Unfortunately, the pgAdmin III (version 1.4) likes to pop up a message for any database, the user does not have access to. So this will keep them a little busy on pgadmin3 startup, but anyway, they don't have to use this tool. I was initially thinking, there 'should' be some way to limit the access in the desired way. So now I know, there is not. Schemas might be a way out of the problem, but it's a little late now, to force them using different schemas and take care of access control themselves. Maybe next year. Thomas Mack TU Braunschweig, Institut für Informationssysteme
mack@ips.cs.tu-bs.de writes:
> I was initially thinking, there 'should' be some way to limit the access
> in the desired way. So now I know, there is not.
Well, if you were using 8.2 then the CONNECT privilege would help...
regards, tom lane