Security choices...

Philip Warner <pjw@rhyme.com.au> writes:

Is there any reason that a security model does not exist for psql that
allows Unix user 'fred' to log in as PG user 'fred' with no password etc,
but any user trying to log on as someone other than themselves has to
provide a password?

Define "log in as themselves" --- in particular, how will you check?

regards, tom lane

At 12:25 28/07/00 -0400, Tom Lane wrote:

Philip Warner <pjw@rhyme.com.au> writes:

Is there any reason that a security model does not exist for psql that
allows Unix user 'fred' to log in as PG user 'fred' with no password etc,
but any user trying to log on as someone other than themselves has to
provide a password?

Define "log in as themselves" --- in particular, how will you check?

I was thinking (in a very blinkered way) of client processes on the same
machine as the postmaster. Admitedly, I had not considered network access etc.

Even in this limited form, it does not seem too bad; is there a way the
postmaster can tell (a) the client is on the same machine, and (b) what the
process username is?

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Philip Warner writes:

At 12:25 28/07/00 -0400, Tom Lane wrote:

Philip Warner <pjw@rhyme.com.au> writes:

Is there any reason that a security model does not exist for psql that
allows Unix user 'fred' to log in as PG user 'fred' with no password etc,
but any user trying to log on as someone other than themselves has to
provide a password?

Define "log in as themselves" --- in particular, how will you check?

I was thinking (in a very blinkered way) of client processes on the same
machine as the postmaster. Admitedly, I had not considered network access etc.

Even in this limited form, it does not seem too bad; is there a way the
postmaster can tell (a) the client is on the same machine, and (b) what the
process username is?

You can tell whether someone is connecting via a Unix domain
socket (client on same machine) or an Internet domain socket
(remote machine or someone using localhost loopback or similar
for some reason). If it's a Unix domain socket then modern Unices
usually provide a way to find out who (i.e. the uid/gid which can
then be mapped to a username/groupname). The problem is that there's
no standard API to do so. Some ways need the cooperation of the
client to send a special "tell the peer who I am" thingy (SCM_CRED
message); some ways don't (Linux getsockopt SO_PEER_CRED and the
STREAMS way). I submitted an example patch (using the simple Linux
API) a month or few back to implement "only let username connect to
own database" but it wasn't accept or worked on because of lack of
portability. On the plus side, I found a Linux kernel bug in that
area (socketpair sockets didn't initialise SO_PEER_CRED) so I got
one bug fix patch accepted out of it :-)

--Malcolm

--
Malcolm Beattie <mbeattie@sable.ox.ac.uk>
Unix Systems Programmer
Oxford University Computing Services

Philip Warner writes:

Is there any reason that a security model does not exist for psql that
allows Unix user 'fred' to log in as PG user 'fred' with no password etc,
but any user trying to log on as someone other than themselves has to
provide a password?

Short of someone sitting down and making it happen I don't see any. You'd
only need to implement some sort of fall-through in `pg_hba.conf', which
in my estimate can't be exceedingly hard.

--
Peter Eisentraut Sernanders v�g 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden

[ Charset ISO-8859-1 unsupported, converting... ]

Philip Warner writes:

Is there any reason that a security model does not exist for psql that
allows Unix user 'fred' to log in as PG user 'fred' with no password etc,
but any user trying to log on as someone other than themselves has to
provide a password?

Short of someone sitting down and making it happen I don't see any. You'd
only need to implement some sort of fall-through in `pg_hba.conf', which
in my estimate can't be exceedingly hard.

How do you know Fred is Fred without a password?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

At 18:34 4/08/00 -0400, Bruce Momjian wrote:

[ Charset ISO-8859-1 unsupported, converting... ]

Philip Warner writes:

Is there any reason that a security model does not exist for psql that
allows Unix user 'fred' to log in as PG user 'fred' with no password etc,
but any user trying to log on as someone other than themselves has to
provide a password?

Short of someone sitting down and making it happen I don't see any. You'd
only need to implement some sort of fall-through in `pg_hba.conf', which
in my estimate can't be exceedingly hard.

How do you know Fred is Fred without a password?

The idea was to apply only on the matchine on which the postmaster runs;
then ideally you get the username of the client process. It's kind of like
IDENT, except it works only for local connections, and asks for passwords
for non-local connections.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

At 23:59 4/08/00 +0200, Peter Eisentraut wrote:

Philip Warner writes:

Is there any reason that a security model does not exist for psql that
allows Unix user 'fred' to log in as PG user 'fred' with no password etc,
but any user trying to log on as someone other than themselves has to
provide a password?

Short of someone sitting down and making it happen I don't see any. You'd
only need to implement some sort of fall-through in `pg_hba.conf', which
in my estimate can't be exceedingly hard.

I'd prefer not to overrule pg_hba.conf; I was thinking along the lines of
adding another security type which falls back to password auth. if it cant
get the username, or if the client process is not a valid user.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

At 18:34 4/08/00 -0400, Bruce Momjian wrote:

[ Charset ISO-8859-1 unsupported, converting... ]

Philip Warner writes:

Is there any reason that a security model does not exist for psql that
allows Unix user 'fred' to log in as PG user 'fred' with no password etc,
but any user trying to log on as someone other than themselves has to
provide a password?

Short of someone sitting down and making it happen I don't see any. You'd
only need to implement some sort of fall-through in `pg_hba.conf', which
in my estimate can't be exceedingly hard.

How do you know Fred is Fred without a password?

The idea was to apply only on the matchine on which the postmaster runs;
then ideally you get the username of the client process. It's kind of like
IDENT, except it works only for local connections, and asks for passwords
for non-local connections.

I am not aware of any way to determine the PID at the other end of a
unix domain socket.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

On Fri, 4 Aug 2000, Bruce Momjian wrote:

At 18:34 4/08/00 -0400, Bruce Momjian wrote:

[ Charset ISO-8859-1 unsupported, converting... ]

Philip Warner writes:

Is there any reason that a security model does not exist for psql that
allows Unix user 'fred' to log in as PG user 'fred' with no password etc,
but any user trying to log on as someone other than themselves has to
provide a password?

Short of someone sitting down and making it happen I don't see any. You'd
only need to implement some sort of fall-through in `pg_hba.conf', which
in my estimate can't be exceedingly hard.

How do you know Fred is Fred without a password?

The idea was to apply only on the matchine on which the postmaster runs;
then ideally you get the username of the client process. It's kind of like
IDENT, except it works only for local connections, and asks for passwords
for non-local connections.

I am not aware of any way to determine the PID at the other end of a
unix domain socket.

You actually don't need the PID on the other end, what you are interested
are the credentials of a process on the other end.

Unfortunately, every OS implemented it in very different way. Linux has
SO_PEERCREDS option, solaris has doors, xBSD have SCM_CREDS or LOCAL_CREDS

see:

http://metalab.unc.edu/pub/Linux/docs/HOWTO/Secure-Programs-HOWTO
http://www.whitefang.com/sup/work.html
http://cr.yp.to/docs/secureipc.html

this kinda has a hole in it also.. our database server only has about 5
uesrs on it , all are employee acounts, not clients.

jeff

On Sat, 5 Aug 2000, Philip Warner wrote:

At 18:34 4/08/00 -0400, Bruce Momjian wrote:

[ Charset ISO-8859-1 unsupported, converting... ]

Philip Warner writes:

Is there any reason that a security model does not exist for psql that
allows Unix user 'fred' to log in as PG user 'fred' with no password etc,
but any user trying to log on as someone other than themselves has to
provide a password?

Short of someone sitting down and making it happen I don't see any. You'd
only need to implement some sort of fall-through in `pg_hba.conf', which
in my estimate can't be exceedingly hard.

How do you know Fred is Fred without a password?

The idea was to apply only on the matchine on which the postmaster runs;
then ideally you get the username of the client process. It's kind of like
IDENT, except it works only for local connections, and asks for passwords
for non-local connections.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Jeff MacDonald,

-----------------------------------------------------
PostgreSQL Inc | Hub.Org Networking Services
jeff@pgsql.com | jeff@hub.org
www.pgsql.com | www.hub.org
1-902-542-0713 | 1-902-542-3657
-----------------------------------------------------
Fascimile : 1 902 542 5386
IRC Nick : bignose

where is the hole? don't you trust your employees? *raised eyebrows*

On Tue, 15 Aug 2000, Jeff MacDonald wrote:

this kinda has a hole in it also.. our database server only has about 5
uesrs on it , all are employee acounts, not clients.

jeff

On Sat, 5 Aug 2000, Philip Warner wrote:

At 18:34 4/08/00 -0400, Bruce Momjian wrote:

[ Charset ISO-8859-1 unsupported, converting... ]

Philip Warner writes:

Is there any reason that a security model does not exist for psql that
allows Unix user 'fred' to log in as PG user 'fred' with no password etc,
but any user trying to log on as someone other than themselves has to
provide a password?

Short of someone sitting down and making it happen I don't see any. You'd
only need to implement some sort of fall-through in `pg_hba.conf', which
in my estimate can't be exceedingly hard.

How do you know Fred is Fred without a password?

The idea was to apply only on the matchine on which the postmaster runs;
then ideally you get the username of the client process. It's kind of like
IDENT, except it works only for local connections, and asks for passwords
for non-local connections.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Jeff MacDonald,

-----------------------------------------------------
PostgreSQL Inc | Hub.Org Networking Services
jeff@pgsql.com | jeff@hub.org
www.pgsql.com | www.hub.org
1-902-542-0713 | 1-902-542-3657
-----------------------------------------------------
Fascimile : 1 902 542 5386
IRC Nick : bignose

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org

only those that
1 : are named after cartoon dogs
2 : they are named after software developers who tend to stay alone..

rofl..

course , i didn't say "my employees" i said
employees.. :)

jeff

On Tue, 15 Aug 2000, The Hermit Hacker wrote:

where is the hole? don't you trust your employees? *raised eyebrows*

On Tue, 15 Aug 2000, Jeff MacDonald wrote:

this kinda has a hole in it also.. our database server only has about 5
uesrs on it , all are employee acounts, not clients.

jeff

On Sat, 5 Aug 2000, Philip Warner wrote:

At 18:34 4/08/00 -0400, Bruce Momjian wrote:

[ Charset ISO-8859-1 unsupported, converting... ]

Philip Warner writes:

Is there any reason that a security model does not exist for psql that
allows Unix user 'fred' to log in as PG user 'fred' with no password etc,
but any user trying to log on as someone other than themselves has to
provide a password?

Short of someone sitting down and making it happen I don't see any. You'd
only need to implement some sort of fall-through in `pg_hba.conf', which
in my estimate can't be exceedingly hard.

How do you know Fred is Fred without a password?

The idea was to apply only on the matchine on which the postmaster runs;
then ideally you get the username of the client process. It's kind of like
IDENT, except it works only for local connections, and asks for passwords
for non-local connections.

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.C.N. 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Jeff MacDonald,

-----------------------------------------------------
PostgreSQL Inc | Hub.Org Networking Services
jeff@pgsql.com | jeff@hub.org
www.pgsql.com | www.hub.org
1-902-542-0713 | 1-902-542-3657
-----------------------------------------------------
Fascimile : 1 902 542 5386
IRC Nick : bignose

Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org

Jeff MacDonald,

-----------------------------------------------------
PostgreSQL Inc | Hub.Org Networking Services
jeff@pgsql.com | jeff@hub.org
www.pgsql.com | www.hub.org
1-902-542-0713 | 1-902-542-3657
-----------------------------------------------------
Fascimile : 1 902 542 5386
IRC Nick : bignose

At 23:46 15/08/00 -0300, Jeff MacDonald wrote:

course , i didn't say "my employees" i said
employees.. :)

As distinct from the unemployed?

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

that's right, if you don't have a job..
you don't get an account... we're elitists :)

JOKING..

ok, this thread is dead.. i replied to a 10 day
old message anyway.

ciao.

On Wed, 16 Aug 2000, Philip Warner wrote:

At 23:46 15/08/00 -0300, Jeff MacDonald wrote:

course , i didn't say "my employees" i said
employees.. :)

As distinct from the unemployed?

----------------------------------------------------------------
Philip Warner | __---_____
Albatross Consulting Pty. Ltd. |----/ - \
(A.B.N. 75 008 659 498) | /(@) ______---_
Tel: (+61) 0500 83 82 81 | _________ \
Fax: (+61) 0500 83 82 82 | ___________ |
Http://www.rhyme.com.au | / \|
| --________--
PGP key available upon request, | /
and from pgp5.ai.mit.edu:11371 |/

Jeff MacDonald,

-----------------------------------------------------
PostgreSQL Inc | Hub.Org Networking Services
jeff@pgsql.com | jeff@hub.org
www.pgsql.com | www.hub.org
1-902-542-0713 | 1-902-542-3657
-----------------------------------------------------
Fascimile : 1 902 542 5386
IRC Nick : bignose