Thread: Authorization problem

Authorization problem

From
"Pedro Fonseca"
Date:
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)



Re: Authorization problem

From
Manuel Trujillo
Date:
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

Re: Authorization problem

From
"Pedro Fonseca"
Date:
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.




Re: Authorization problem

From
Tom Lane
Date:
"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

Re: Authorization problem

From
hodges@xprt.net
Date:
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

Re: Authorization problem

From
"Pedro Fonseca"
Date:
> 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)



Re: Authorization problem

From
"Pedro Fonseca"
Date:
<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)



Re: Authorization problem

From
Tom Lane
Date:
"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

Re: Authorization problem

From
"Pedro Fonseca"
Date:
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