How do I login and authenticate to Postgresql afte

问题:

Did a new install of postgres 8.4 on mint ubuntu. How do I create a user for postgres and login using psql?

When I type psql, it just tells me

psql: FATAL: Ident authentication failed for user "my-ubuntu-username"

回答1:

There are two methods you can use. Both require creating a user and a database.

1. Using createuser and createdb,

   $ sudo -u postgres createuser -s $USER
   $ createdb mydatabase
   $ psql -d mydatabase
   

2. Using the SQL administration commands, and connecting with a password over TCP

   $ sudo -u postgres psql postgres
   

   And, then in the psql shell

   CREATE ROLE myuser LOGIN PASSWORD 'mypass';
   CREATE DATABASE mydatabase WITH OWNER = myuser;
   

   Then you can login,

   $ psql -h localhost -d mydatabase -U myuser -p <port>
   

   If you don't know the port, you can always get it by running the following, as the postgres user,

   SHOW port;
   

   Or,

   $ grep "port =" /etc/postgresql/*/main/postgresql.conf
   

Sidenote: the postgres user

I suggest NOT modifying the postgres user.

1. It's normally locked from the OS. No one is supposed to "log in" to the operating system as postgres. You're supposed to have root to get to authenticate as postgres.
2. It's normally not password protected and delegates to the host operating system. This is a good thing. This normally means in order to log in as postgres which is the PostgreSQL equivalent of SQL Server's SA, you have to have write-access to the underlying data files. And, that means that you could normally wreck havoc anyway.
3. By keeping this disabled, you remove the risk of a brute force attack through a named super-user. Concealing and obscuring the name of the superuser has advantages.

回答2:

by default you would need to use the postgres user:

sudo -u postgres psql postgres

回答3:

The error your are getting is because your-ubuntu-username is not a valid Postgres user.

You need to tell psql what database username to use

psql -U postgres

You may also need to specify the database to connect to

psql -U postgres -d <dbname>

回答4:

If your database client connects with TCP/IP and you have ident auth configured in your pg_hba.conf check that you have an identd installed and running. This is mandatory even if you have only local clients connecting to "localhost".

Also beware that nowadays the identd may have to be IPv6 enabled for Postgresql to welcome clients which connect to localhost.

回答5:

you can also connect to database as "normal" user (not postgres):

postgres=# \connect opensim Opensim_Tester localhost;

Password for user Opensim_Tester:    

You are now connected to database "opensim" as user "Opensim_Tester" on host "localhost" at port "5432"