Thread: User/login questions

I wish to implement a web-based login system where the user logs in and gets
a list of available databases. This involves several things that I have
questions on:
1) Connecting to the DB and logging in (Must the user connect to a
database?)
2) Finding out what databases the user has access to (How?) without having
to maintain a copy myself. (Can it be pulled from some system table, or join
of system tables? (i.e. SELECT db_name FROM something WHERE user='me' AND
granted_perm='SELECT')

Also, I'm stuck in 7.2 land, with a planned upgrade to 7.4, when it comes
out.


Thanks!

Jason Hihn
Paytime Payroll

Jason Hihn <jhihn@paytimepayroll.com> writes:
> 2) Finding out what databases the user has access to (How?) without having
> to maintain a copy myself. (Can it be pulled from some system table, or join
> of system tables? (i.e. SELECT db_name FROM something WHERE user='me' AND
> granted_perm='SELECT')

I'm afraid not --- that info is only available from the postmaster's
pg_hba.conf file, which is not accessible as a table.  The typical setup
though is that any user can connect to any database.

            regards, tom lane

Ok, so anyone can connect to any database, however they need to be granted
insert, update, select and delete perms on each table...?

If that's not a correct assesment, then please correct me.

Let me ask it this way: can I find out what databases and tables and methods
have been granted to a user (once connected)?

Thanks again

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Thursday, August 14, 2003 9:50 AM
> To: Jason Hihn
> Cc: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] User/login questions
>
>
> Jason Hihn <jhihn@paytimepayroll.com> writes:
> > 2) Finding out what databases the user has access to (How?)
> without having
> > to maintain a copy myself. (Can it be pulled from some system
> table, or join
> > of system tables? (i.e. SELECT db_name FROM something WHERE
> user='me' AND
> > granted_perm='SELECT')
>
> I'm afraid not --- that info is only available from the postmaster's
> pg_hba.conf file, which is not accessible as a table.  The typical setup
> though is that any user can connect to any database.
>
>             regards, tom lane
>

Jason Hihn <jhihn@paytimepayroll.com> writes:
> Ok, so anyone can connect to any database, however they need to be granted
> insert, update, select and delete perms on each table...?

That's the out-of-the-box security configuration, yes.

            regards, tom lane