Need some help with PostgreSQL

I don’t know if there is anyone here that is fimiliar with PostgreSQL, I am new to it and wanting to test it out and maybe switch from MySQL to it if I like it, I am having trouble figuring out how to login into psql using a username that is different from the username I am loged into my computer on. I know the documentation for it says that I need to use the -U username switch for psql but I can’t get it to work, but when I switch to the postgres user I can login.

vendion@linux-9rf6:~> psql -U postgres
psql: FATAL:  Ident authentication failed for user "postgres"
vendion@linux-9rf6:~> su
Password: 
linux-9rf6:/home/vendion # su postgres
postgres@linux-9rf6:/home/vendion> 

From there I can run psql and it works.

Like mysql, you have to add other users to postgres’ authentication database if you want to use an alternate account. However that database may be one of several forms: it could be a hash stored in the config file, it could chain off the Linux passwd file, and so forth. Look at the docs for adding users to postgres.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Along with ken yap’s suggestions I’d check the ‘pg_hba.conf’ file
somewhere in your postgresql/data directory or something like that. This
may be something like /var/lib/pgsql/data… I’m not positive on the
location. Perhaps find it like this:

rpm -qal | grep pg_hba.conf

Anyway, once you find it go to the bottom and look at how the server is
setup to allow authentication. There are multiple ways… md5, ident,
trust. Check the docs for what they mean but basically you can have a
server setup to ONLY authenticate via the server’s usernames (you become
the user and then you are the equivalent user in PostgreSQL) which is
really nice sometimes when setup. md5 is the typical password method you
are familiar with in MySQL, but it means you need to set the user’s
password in PostgreSQL. trust is, in my opinion, evil, but I may be alone
in my paranoia so make your own decision.

Good luck.

On 11/12/2010 08:06 AM, ken yap wrote:
>
> Like mysql, you have to add other users to postgres’ authentication
> database if you want to use an alternate account. However that database
> may be one of several forms: it could be a hash stored in the config
> file, it could chain off the Linux passwd file, and so forth. Look at
> the docs for adding users to postgres.
>
>
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v2.0.15 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iQIcBAEBAgAGBQJM3VsJAAoJEF+XTK08PnB5bmcQAM0O1n27wWTlBxl6dEAs235t
Ym24YDv1fTlRGF2J12uf8JplqGH4y4rP4BAiVYmiG7tP+Q/CvRGq5vDZx5VOijDy
Pc7ZHyAeaUQ5fL4Sw0MIOl4vWg/09WrkHGGKl2+4fUs4j4Ffw7spCRrqNeVlbk8k
knORVOKvXI1R6huTx3IcKR0IsvuA8ODsW5KsUM7g2rRs3h+dzM0VrSvyCKaP41Bm
KtTJ1Y3ahwoQHGfkdeweP4NfcxXTqb8mKs4F0kUuf8sVXNhoGWd1Bm7TDtqEsjwV
+SBH9TKs1/ExJ+k57mziChcVwXXkl11H3sgfgo5ct1ZAVFPWCulyBZT5yStbXoyK
rBbDYJV2vycW7bjkEuQklSjoLJQVnTVLAla0Lh7JV8SypnxLzUxzHkWiporKTwBm
T1LDVArr5clMOoTS94qktICGmzXqlUELaYlh6h8pw9Y4xck/itQxIY01IEb/Hvob
bHC/Zn/7ImwlQpPXsCPgKjxGHb8uYy5rnn8f0AwsI3swLPppCy1g//+a+Mhu/gaZ
M6BnSyK89DMBigZ8qJRHQuQA1g9gcjUhGywSZqQpPu0BMkkVHxS2RtV3sXrsqzJT
+ZTT2VSnU/Opac6OHV+gp7htUjbN5Q5CmR7U3MUdpbWQceMJf8p/oT20rsimjGVR
csuKelrB7E76STq1ScJo
=R6dO
-----END PGP SIGNATURE-----

I guess I should have mentioned that I already created the roles (users) that I want to use and gave them permission to login (LOGIN), create databases (CREATEDB) and create other roles (CREATEROLE). With the LOGIN option I can login to psql if I created a role called “vendion” and it will work that way but what I am wanting to use PostgreSQL for I would like to use a different username for the database login, although it is not that important just don’t want to give out my login credentals to my user account to people who will need access to the database.

Thanks for that suggestion, I’ll look into that once I get back to that system. The server username option would be good in some occations, IE when one person/username needs access to the database but I don’t think that would work. I have a script that needs to access the database with its own credentails so I have a login that I can share that is a “junk” login.