Thread: Handling users

Hi all,

I am writing a small piece of software based on postgresql for an issue
tracking system. I am using DB connection pooling in C++/libpq.

I have couple of questions.

1) I plan to deligate handling user management to postgresql. i.e. I will be
creating database users instead of maintening my own user/password stuff. I
have 500 users right now and the number will grow. Will that cause any
problem fro indexing/performance etc. POV? (I know this is kinda stupid but
just confirming)

2) The connection pool will be initiated as database superuser so that it can
do set session authorisation and revert back on the fly.

In this scheme, how can I authenticate user without fiddling with postgresql
system catalogs? I mean I have and established connection as database
superuser, a username and a password. Now I want to authenticate. How do I do
it?

TIA..

 Shridhar

On Tue, Feb 18, 2003 at 18:30:59 +0530,
  "Shridhar Daithankar<shridhar_daithankar@persistent.co.in>" <shridhar_daithankar@persistent.co.in> wrote:
>
> In this scheme, how can I authenticate user without fiddling with postgresql
> system catalogs? I mean I have and established connection as database
> superuser, a username and a password. Now I want to authenticate. How do I do
> it?

You can use ident authentication and allow the user the application runs
under to connect as any of the users that it is supposed to be able to.
If the application software is running on the same box as the database
server and the OS supports it, you can use local (domain socket) connections
and use ident without running an ident server.

On Tuesday 18 Feb 2003 7:22 pm, you wrote:
> On Tue, Feb 18, 2003 at 18:30:59 +0530,
>
>   "Shridhar Daithankar<shridhar_daithankar@persistent.co.in>"
<shridhar_daithankar@persistent.co.in> wrote:
> > In this scheme, how can I authenticate user without fiddling with
> > postgresql system catalogs? I mean I have and established connection as
> > database superuser, a username and a password. Now I want to
> > authenticate. How do I do it?
>
> You can use ident authentication and allow the user the application runs
> under to connect as any of the users that it is supposed to be able to.
> If the application software is running on the same box as the database
> server and the OS supports it, you can use local (domain socket)
> connections and use ident without running an ident server.

I don't want to do that unless that is last option. And yes, the application
and database are on same machine and it will be unix domain socket only for
security reasons. ( I mean just trying to be paranoid. The application is on
company intranet but why take chance?)

 Shridhar

On Tue, Feb 18, 2003 at 19:13:51 +0530,
  "Shridhar Daithankar<shridhar_daithankar@persistent.co.in>" <shridhar_daithankar@persistent.co.in> wrote:
>
> I don't want to do that unless that is last option. And yes, the application
> and database are on same machine and it will be unix domain socket only for
> security reasons. ( I mean just trying to be paranoid. The application is on
> company intranet but why take chance?)

If you are using domain sockets and the OS supports it, why wouldn't you
want to use ident authentication?

The other option is to use password authentication and make sure you keep
the binaries secret so people can't find out what the passwords are. In
most cases I would expect password authentication to be higher risk than
ident authentication when you only allow connections through domain sockets.

On Tuesday 18 Feb 2003 7:47 pm, you wrote:
> On Tue, Feb 18, 2003 at 19:13:51 +0530,
>
>   "Shridhar Daithankar<shridhar_daithankar@persistent.co.in>"
<shridhar_daithankar@persistent.co.in> wrote:
> > I don't want to do that unless that is last option. And yes, the
> > application and database are on same machine and it will be unix domain
> > socket only for security reasons. ( I mean just trying to be paranoid.
> > The application is on company intranet but why take chance?)
>
> If you are using domain sockets and the OS supports it, why wouldn't you
> want to use ident authentication?

Correct me if I am wrong, but when I run ident authentication, I have to
create OS users right? And with sshd enabled on box for admin reasons, I
don't want to let everybody login. I agree I can set their login to
/sbin/false on linux or nologin on BSD.

but under full load I expect around 50 concurrent users. With 15-20
connections shuffling thr. users with set authorisation for each web request,
I don't want to trust /etc/passwd for performance at hundreds of users.

> The other option is to use password authentication and make sure you keep
> the binaries secret so people can't find out what the passwords are. In
> most cases I would expect password authentication to be higher risk than
> ident authentication when you only allow connections through domain
> sockets.

Which binaries? My application? I am silghtly confused here..

I want to use password authentication. But my problem is set session
authorisation does not support a password clause.

I have not installed a test database as yet. This is out of documentation. May
be experimenting with things might turn out few things..

Again, any help using password authentication?

 Shridhar

On Tue, Feb 18, 2003 at 19:41:38 +0530,
  "Shridhar Daithankar<shridhar_daithankar@persistent.co.in>" <shridhar_daithankar@persistent.co.in> wrote:
>
> Correct me if I am wrong, but when I run ident authentication, I have to
> create OS users right? And with sshd enabled on box for admin reasons, I
> don't want to let everybody login. I agree I can set their login to
> /sbin/false on linux or nologin on BSD.

Well yes, but the app has to run as some user. What you are probably missing
is that your don't have to use sameuser with ident authentication. You can
give a list of which database users an OS user is allowed to connect as.
So that the app OS user can be allowed connect to a specific list of DB
users without using a password.

On Tue, 18 Feb 2003 19:13:51 +0530 "Shridhar Daithankar<shridhar_daithankar@persistent.co.in>"
<shridhar_daithankar@persistent.co.in>wrote: 
> I don't want to do that unless that is last option. And yes, the
> application
> and database are on same machine and it will be unix domain socket only
> for
> security reasons. ( I mean just trying to be paranoid. The application
> is on
> company intranet but why take chance?)

actually, this is perfectly reasonable. the vast majority of penetrations
are really inside jobs.

richard
--
Richard Welty                                         rwelty@averillpark.net
Averill Park Networking                                         518-573-7592
              Unix, Linux, IP Network Engineering, Security