Results 1 to 5 of 5

Thread: Need some help with PostgreSQL

  1. #1
    Join Date
    Jun 2008
    Location
    Cleveland, TN USA
    Posts
    412

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

    Code:
    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.
    "We must plan for freedom, and not only for security, if for no other reason than only freedom can make security more secure." Karl Popper

  2. #2
    Join Date
    Jun 2008
    Location
    UTC+10
    Posts
    9,686
    Blog Entries
    4

    Default Re: Need some help with PostgreSQL

    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.

  3. #3

    Default Re: Need some help with PostgreSQL

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

  4. #4
    Join Date
    Jun 2008
    Location
    Cleveland, TN USA
    Posts
    412

    Default Re: Need some help with PostgreSQL

    Quote Originally Posted by ken_yap View Post
    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.
    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.
    "We must plan for freedom, and not only for security, if for no other reason than only freedom can make security more secure." Karl Popper

  5. #5
    Join Date
    Jun 2008
    Location
    Cleveland, TN USA
    Posts
    412

    Default Re: Need some help with PostgreSQL

    Quote Originally Posted by ab@novell.com View Post
    -----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.
    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.
    "We must plan for freedom, and not only for security, if for no other reason than only freedom can make security more secure." Karl Popper

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •