[general] Permissions

How do I set up my pg_hba.conf file to allow user "a" to only
have access to databases x, y, and z; and user "b" to only have
access to databases m, n, and o? The pgident authentication
mechanism looks like the best candidate, but it does not seem
to quite meet the requirements.

You have separate lines for databases x, y, z and m, n, and o. You
can't use 'trust' because that doesn't really know the user is who he
says he is. You can use secondary password files to specific users.

-- 
  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

PostgreSQL has, it seems, a somewhat weak permissions system, although it
definately gets the job done.

What you can do is have a seperate pg_hba.conf entry for every user/database
combination you would like to be able to connect. To make this work, make a
seperate password file for each user using pg_passwd. Lets say you call two
files a_passwd and b_passwd (in accordance with your example), then make the
lines:

local x crypt a_passwd
local y crypt a_passwd
local z crypt a_passwd
local m crypt b_passwd
local n crypt b_passwd
local o crypt b_passwd

Do not make a password record in a_passwd for b, and do not make a record in
b_passwd for a. Make sure to put the *_passwd files in the same directory as
pg_hba.conf (where they will be found).

Note: I did not actually try this, as that would require changing around all
of my permissions for my database. It should work, however. You can also make
these host-based lines in order to allow connections from another host.

Regards,
Jeff Davis

Thank you. This worked. In testing it out, I noticed that postgres only
considers the first eight charaters of the password. So

abcdefghijklm
abcdefghmlkji

are considered the same by postgres. Is that correct?

Travis

On Monday 12 November 2001 01:01, Jeff Davis enlightened me by saying:

PostgreSQL has, it seems, a somewhat weak permissions system, although it
definately gets the job done.

What you can do is have a seperate pg_hba.conf entry for every
user/database combination you would like to be able to connect. To make
this work, make a seperate password file for each user using pg_passwd.
Lets say you call two files a_passwd and b_passwd (in accordance with your
example), then make the lines:

. . .

Travis Bauer <trbauer@indiana.edu> writes:

Thank you. This worked. In testing it out, I noticed that postgres only
considers the first eight charaters of the password.

Not Postgres per se ... on most platforms the crypt() library routine
works that way.

regards, tom lane

On Monday 12 November 2001 03:16 pm, you wrote:

Thank you. This worked. In testing it out, I noticed that postgres only
considers the first eight charaters of the password. So

abcdefghijklm
abcdefghmlkji

are considered the same by postgres. Is that correct?

Well... sort of. It isn't so much Postgres as crypt(). crypt() only hashes
the first 8 characters. I wonder if postgres supports md5 encryption instead?
md5 is considered more secure and allows for longer passwords.

Regards,
Jeff

On Monday 12 November 2001 03:16 pm, you wrote:

Thank you. This worked. In testing it out, I noticed that postgres only
considers the first eight charaters of the password. So

abcdefghijklm
abcdefghmlkji

are considered the same by postgres. Is that correct?

Well... sort of. It isn't so much Postgres as crypt(). crypt() only hashes
the first 8 characters. I wonder if postgres supports md5 encryption instead?
md5 is considered more secure and allows for longer passwords.

7.2 will have MD5, and in all interfaces, including jdbc and odbc.

-- 
  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 16:00 12/11/01 -0800, you wrote:

Well... sort of. It isn't so much Postgres as crypt(). crypt() only hashes
the first 8 characters. I wonder if postgres supports md5 encryption instead?
md5 is considered more secure and allows for longer passwords.

7.2 does.