StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<blockquote> <p>I am new to linux so I am unable to figure out why I was able to create user but while creating database there is connection error with postgres. And also error for ident authentication for user.</p> </blockquote> <p><strong>ident</strong> is an authentication schema that relies on the <em>currently logged in</em> user. If you've su -s to postgres and then try to login as another user, ident will fail (as it's not the currently logged in user). You can solve this issue in two ways, I tend to use the latter.</p> <p><strong>solution</strong>: simply make sure the currently logged in user is the user with which you would like to log in to postgres:</p> <pre><code>postgres@machine:~$ createuser -S -D -R myUser postgres@machine:~$ exit machine:~# su - myUser myUser@machine:~$ psql myDatabase </code></pre> <p><strong>better solution</strong>: change pg_hba.conf (probably located in /etc/postgresql/8.4/main/ or similar), and make sure you add the "md5" authentication schema to the list of options. Here's my pg_hba.conf on my development box, without comments:</p> <pre><code>berry@berry-pc:~$ sudo cat /etc/postgresql/8.4/main/pg_hba.conf | grep -v "^#" | grep -v "^$" local all postgres ident local all all md5 host all all 127.0.0.1/32 md5 host all all ::1/128 md5 </code></pre> <p>This tells PostgreSQL that postgres can login with <em>ident</em>, and all other users can login using the md5 authentication. That way, you can use the -U switch to the psql binary to denote which user you wish to become, so this actually works:</p> <pre><code>berry@berry-pc:~$ psql -U myUser myDatabase. </code></pre> <p>That said, I tend to use the postgres superuser to create databases. I then grant permissions on the newly created database to the newly created user, as such:</p> <pre><code>postgres@debian:~$ psql template1 template1=# CREATE USER myUser WITH PASSWORD 'myPassword'; CREATE ROLE template1=# CREATE DATABASE myDatabase; CREATE DATABASE template1=# GRANT ALL PRIVILEGES ON DATABASE myDatabase TO myUser; GRANT template1=# \q </code></pre> <p>Hope that helps.</p> <p><strong>ADDENDUM</strong>: Once you've altered the pg_hba.conf, you will have to restart the PostgreSQL server to make sure it reads the configuration again. You can do so by issuing this command:</p> <pre><code>root@debian:~$ /etc/init.d/postgresql-8.4 restart </code></pre> <p>The script might be called "postgresql" instead of "postgresql-8.4", depending on OS and method of installation.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload