Обсуждение: Authorization problem
Greetings! I have 2 PostgreSQL users created. I'd like access to a database restricted only to one of these users. As it is, in pg_hba.conf, any PostgreSQL user can connect locally to any database whatsoever: # TYPE DATABASE IP_ADDRESS MASK AUTHTYPE MAP local all trust host all 127.0.0.1 255.255.255.255 trust I have TCP/IP based connections disabled. What I would like would be something like: # TYPE DATABASE IP_ADDRESS MASK AUTHTYPE MAP local test_db ident test_db_map The problem is that the ident AUTHTYPE can only be used for TCP/IP connections. How do I tell PostgreSQL that I only want to allow postgres_user_1 to connect to test_db, and not every user? And how do I do this without using passwords and TCP/IP connections? Thanks in advance. -- ______________________________________________________________________ Pedro Fonseca (pedro.fonseca@iscte.pt) Mob.: (+351)964598357 http://www.pedrofonseca.com ADETTI/ISCTE (Instituto Superior de Ci�ncias do Trabalho e da Empresa)
On Wed, Oct 03, 2001 at 02:17:15PM +0100, Pedro Fonseca wrote: > Greetings! > > I have 2 PostgreSQL users created. I'd like access to a database restricted > only to one of these users. As it is, in pg_hba.conf, any PostgreSQL user > can connect locally to any database whatsoever: > > # TYPE DATABASE IP_ADDRESS MASK AUTHTYPE MAP > local all trust > host all 127.0.0.1 255.255.255.255 trust > > I have TCP/IP based connections disabled. What I would like would be > something like: > > # TYPE DATABASE IP_ADDRESS MASK AUTHTYPE MAP > local test_db ident test_db_map > > The problem is that the ident AUTHTYPE can only be used for TCP/IP > connections. How do I tell PostgreSQL that I only want to allow > postgres_user_1 to connect to test_db, and not every user? And how do I do > this without using passwords and TCP/IP connections? I think the solution is: 1- create a passwd file for this database (test_db_passwd, for example), with pg_passwd test_db_passwd, and put here withpg_passwd the user's needed. 2- put this in your pg_hba.conf: # TYPE DATABASE IP_ADDRESS MASK AUTHTYPE MAP local test_db password test_db_passwd test_db_map I'm very "newbie" in postgresql, but I think this can solve your problem. Sorry for my bad english, if you speak spanish, you can write to me directly. -- Manuel Trujillo manueltrujillo@dorna.es Technical Engineer http://www.motograndprix.com Dorna Sports S.L. +34 93 4702864
Hi Manuel! Yo no hablo el espa�ol mui bien... :) Your suggestion is just fine, but that way the authentication will be password based! And I don't want that... The same for Felipe's suggestion! In short: I'd like this database accessible only by *one* PostgreSQL user (instead of all), I don't want this user to have to authenticate through a password and I don't want to use TCP/IP. I think that the AUTHTYPE ident (in pg_hba.conf) is just fine for this case, together with a corresponding map, but that can only be used for TCP/IP connections... Any more suggestions? I mean, this is possible, right? -- ______________________________________________________________________ Pedro Fonseca (pedro.fonseca@iscte.pt) Mob.: (+351)964598357 http://www.pedrofonseca.com ADETTI/ISCTE (Instituto Superior de Ci�ncias do Trabalho e da Empresa) "Manuel Trujillo" <manueltrujillo@dorna.es> wrote in message news:20011003161206.A560@klingon.dorna.es... > On Wed, Oct 03, 2001 at 02:17:15PM +0100, Pedro Fonseca wrote: > > > I have 2 PostgreSQL users created. I'd like access to a database restricted > > only to one of these users. As it is, in pg_hba.conf, any PostgreSQL user > > can connect locally to any database whatsoever: > > > > # TYPE DATABASE IP_ADDRESS MASK AUTHTYPE MAP > > local all trust > > host all 127.0.0.1 255.255.255.255 trust > > > > I have TCP/IP based connections disabled. What I would like would be > > something like: > > > > # TYPE DATABASE IP_ADDRESS MASK AUTHTYPE MAP > > local test_db ident test_db_map > > > > The problem is that the ident AUTHTYPE can only be used for TCP/IP > > connections. How do I tell PostgreSQL that I only want to allow > > postgres_user_1 to connect to test_db, and not every user? And how do I do > > this without using passwords and TCP/IP connections? > > > I think the solution is: > 1- create a passwd file for this database (test_db_passwd, for example), with pg_passwd test_db_passwd, and put here with pg_passwd the user's needed. > 2- put this in your pg_hba.conf: > # TYPE DATABASE IP_ADDRESS MASK AUTHTYPE MAP > local test_db password test_db_passwd test_db_map > > I'm very "newbie" in postgresql, but I think this can solve your problem. > > Sorry for my bad english, if you speak spanish, you can write to me directly.
"Pedro Fonseca" <pedro.fonseca@netcabo.pt> writes:
> In short: I'd like this database accessible only by *one* PostgreSQL user
> (instead of all), I don't want this user to have to authenticate through a
> password and I don't want to use TCP/IP.
Possibly setting restrictive permissions on the socket file is the way
to go (see the unix_socket parameters in postgresql.conf). pg_hba will
certainly not help you here, but if you can set up an /etc/group entry
with only the authorized users as its members, you're set.
regards, tom lane
Do you have pgaccess installed? Run it from unix prompt, select the database, and put in only the user(s) you want to have access. phppgadmin is another program that allows you to set users for each database. Tom On 3 Oct 2001, at 16:48, Pedro Fonseca wrote: > Hi Manuel! > > Yo no hablo el español mui bien... :) > > Your suggestion is just fine, but that way the authentication will be > password based! And I don't want that... The same for Felipe's suggestion! > > In short: I'd like this database accessible only by *one* PostgreSQL user > (instead of all), I don't want this user to have to authenticate through a > password and I don't want to use TCP/IP. I think that the AUTHTYPE ident (in > pg_hba.conf) is just fine for this case, together with a corresponding map, > but that can only be used for TCP/IP connections... > > Any more suggestions? I mean, this is possible, right? > -- > ______________________________________________________________________ > Pedro Fonseca (pedro.fonseca@iscte.pt) > Mob.: (+351)964598357 > http://www.pedrofonseca.com > ADETTI/ISCTE (Instituto Superior de Ciências do Trabalho e da Empresa) > > > "Manuel Trujillo" <manueltrujillo@dorna.es> wrote in message > news:20011003161206.A560@klingon.dorna.es... > > On Wed, Oct 03, 2001 at 02:17:15PM +0100, Pedro Fonseca wrote: > > > > > I have 2 PostgreSQL users created. I'd like access to a database > restricted > > > only to one of these users. As it is, in pg_hba.conf, any PostgreSQL > user > > > can connect locally to any database whatsoever: > > > > > > # TYPE DATABASE IP_ADDRESS MASK AUTHTYPE MAP > > > local all trust > > > host all 127.0.0.1 255.255.255.255 trust > > > > > > I have TCP/IP based connections disabled. What I would like would be > > > something like: > > > > > > # TYPE DATABASE IP_ADDRESS MASK AUTHTYPE MAP > > > local test_db ident > test_db_map > > > > > > The problem is that the ident AUTHTYPE can only be used for TCP/IP > > > connections. How do I tell PostgreSQL that I only want to allow > > > postgres_user_1 to connect to test_db, and not every user? And how do I > do > > > this without using passwords and TCP/IP connections? > > > > > > I think the solution is: > > 1- create a passwd file for this database (test_db_passwd, for example), > with pg_passwd test_db_passwd, and put here with pg_passwd the user's > needed. > > 2- put this in your pg_hba.conf: > > # TYPE DATABASE IP_ADDRESS MASK AUTHTYPE > MAP > > local test_db password test_db_passwd > test_db_map > > > > I'm very "newbie" in postgresql, but I think this can solve your problem. > > > > Sorry for my bad english, if you speak spanish, you can write to me > directly. > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > We have changed our ISP, now xprt.net - not jps.net Tom Hodges, hodges@xprt.net or tom_hodges@yahoo.com ICQ 10149621, YahooMessenger tom_hodges Mail: 14314 SW Allen Blvd, #317; Beaverton OR 97005 USA
> Possibly setting restrictive permissions on the socket file is the way > to go (see the unix_socket parameters in postgresql.conf). pg_hba will > certainly not help you here, but if you can set up an /etc/group entry > with only the authorized users as its members, you're set. Hi Tom, thanks for replying! You mean there's no way to do this in pg_hba.conf? Geez, incredible... Am I looking the wrong way here? How is it for everyone that hasn't got TCP/IP PostgreSQL connections and passwords enabled? Can every user in those systems connect to every database as any PostgreSQL user? I mean, this is what the 'trust' AUTHTYPE does! Isn't this a bad thing? -- ______________________________________________________________________ Pedro Fonseca (pedro.fonseca@iscte.pt) Mob.: (+351)964598357 http://www.pedrofonseca.com ADETTI/ISCTE (Instituto Superior de Ci�ncias do Trabalho e da Empresa)
<hodges@xprt.net> wrote in message news:3BBC0096.18900.4AB4F2@localhost... > Do you have pgaccess installed? Run it from unix prompt, > select the database, and put in only the user(s) you want > to have access. phppgadmin is another program that allows > you to set users for each database. Well, I have pgaccess installed, but the users in pgaccess are the PostgreSQL users. I mean, it's still possible for anyuser in the system to log in to any database, using any PostgreSQL user. Let me give an example: let us say that only the system users 'postgres' and 'test_db_user' have matching PostgreSQL users. All other system users don't own corresponding PostgreSQL users. However every user in the system can connect to any database as any of the existing PostgreSQL users. Let us say that I'm the user 'regular_user' (doesn't have a corresponding PostgreSQL user). I can connect to the database 'test_db' using the PostgreSQL user 'test_db_user' simply by doing this: /usr/local/pgsql/bin/psql test_db test_db_user You see? Every user in the system, even if he doesn't own a PostgreSQL user, can connect to any database, as any PostgreSQL user! This is very bad policy! This is what happens with the 'trust' AUTHTYPE in pg_hba.conf. Again, one way around this would be to use the AUTHTYPE 'ident', and then create a map. But this AUTHTYPE only works for TCP/IP connections, and I don't want to use that because everything is happening on the same machine. Also, it's a bummer to have to authenticate everytime with passwords... Isn't there a way to solve this? It would be cool if one would be able to have a map for the 'local' TYPE, like the one we can have for the 'host' TYPE, in pg_hba.conf. Please, any ideas? -- ______________________________________________________________________ Pedro Fonseca (pedro.fonseca@iscte.pt) Mob.: (+351)964598357 http://www.pedrofonseca.com ADETTI/ISCTE (Instituto Superior de Ci�ncias do Trabalho e da Empresa)
"Pedro Fonseca" <pedro.fonseca@netcabo.pt> writes:
> I mean, this is what the 'trust' AUTHTYPE does!
Quite.
> Isn't this a bad thing?
If you don't trust the users on your local machine, you can't use
"trust" authtype for local connections. It's as simple as that.
The reason why there's not an equivalent of "ident" auth for local
socket connections is that most platforms don't provide any way to
find out who owns the other end of a local socket connection.
You're wasting your breath to complain to the Postgres developers
about an OS-level deficiency. I'd suggest using ident and TCP/IP.
You can set PGHOST=127.0.0.1 in your environment so you don't need to
think about what kind of connection you are using.
BTW, PG 7.2 will support ident-like auth on local connections for
a small number of platforms where there is such a facility. But
that doesn't help you today, and won't ever help you if you're not
on one of those platforms.
regards, tom lane
OK Tom! Many thanks! Best regards. -- ______________________________________________________________________ Pedro Fonseca (pedro.fonseca@iscte.pt) Mob.: (+351)964598357 http://www.pedrofonseca.com ADETTI/ISCTE (Instituto Superior de Ci�ncias do Trabalho e da Empresa) "Tom Lane" <tgl@sss.pgh.pa.us> wrote in message news:17419.1002305476@sss.pgh.pa.us... > "Pedro Fonseca" <pedro.fonseca@netcabo.pt> writes: > > I mean, this is what the 'trust' AUTHTYPE does! > > Quite. > > > Isn't this a bad thing? > > If you don't trust the users on your local machine, you can't use > "trust" authtype for local connections. It's as simple as that. > > The reason why there's not an equivalent of "ident" auth for local > socket connections is that most platforms don't provide any way to > find out who owns the other end of a local socket connection. > You're wasting your breath to complain to the Postgres developers > about an OS-level deficiency. I'd suggest using ident and TCP/IP. > You can set PGHOST=127.0.0.1 in your environment so you don't need to > think about what kind of connection you are using. > > BTW, PG 7.2 will support ident-like auth on local connections for > a small number of platforms where there is such a facility. But > that doesn't help you today, and won't ever help you if you're not > on one of those platforms. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster