Installing Postgresql and PgAdmin3 on Linux

Question

I use Postgesql and PgAdmin on Windows without any problems but am struggling to get it working on Linux (I'm a Linux N00b). I have installed the 64bit version on my Fedora box following these instructions (except substituting version 9.3 for 9.2). Everything works well until I get to launching pgsql with:

sudo -u postgres psql template1

Here I get an error "Permission denied" as is attempts to change to my home directory.

I can change the password for 'postgres' as per the instructions and I presume this is just so I can use 'md5' rather than 'trust' in pg_hba.conf (if not, then I don't understand why I need this step).

When I fire up PgAdmin3, initially I have no connections. Attempting to connect to the server gives me an authentication error for user 'postgres'. I have tried resetting pg_hba.conf to 'trust' but that doesn't work either.

I would appreciate an explanation of the 'postgres' user in Linux as it appears to be a separate actual user on the Linux system (unlike Windows where it is just a 'disembodied' user relevant only to Postgress). Secondly, help in getting PgAdmin speaking to Postgresql would be gratefully received.

Cefull Lo · Accepted Answer · 2013-11-24 21:41:48Z

1

psql -U postgres template1

It will prompt for password if you set md5 in pg_hba.conf

answered Nov 24, 2013 at 21:41

Cefull Lo

912 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MappaGnosis Over a year ago

Thanks for the suggestion. I don't get the Linus 'Permission Denied' error but unfortunately when I set the password for 'postgres' in this way I get a psql error 'FATAL: password authentication failed for user "postgres"' when I try to connect.

Erwin Brandstetter · Accepted Answer · 2013-11-25 23:47:44Z

0

After much digging about I found that most of the documentation is incomplete. I needed a combination of connecting via Cefull Lo's solution (+1) and NOT (as in most of the documentation):

sudo -U postgres psql

Then to change the password I need a more explicit SQL command as follows

template1=# ALTER USER postgres with encrypted password ‘yourpassword’;

I presume that I need to explicitly state 'with encrypted password' because I am using md5, though again, 90% of the documentation fails to point this out and advises a basic change of password together with md5. Maybe this is specific to Fedora, who knows, but now I can connect via PgAdmin3.

edited Nov 25, 2013 at 23:47

Erwin Brandstetter

669k160 gold badges1.2k silver badges1.3k bronze badges

answered Nov 25, 2013 at 17:19

MappaGnosis

1,1691 gold badge12 silver badges27 bronze badges

1 Comment

Erwin Brandstetter Over a year ago

Several misunderstandings . The sudo part is only needed if you are not postgres already, and only possible if your system has sudo installed, of course. The authentication method trust allows connections without password, but only if you specified the appropriate connections (and reloaded). Often local and host are mixed up. And ALTER USER works completely independent of your connection settings, like any other SQL command.

Collectives™ on Stack Overflow

Installing Postgresql and PgAdmin3 on Linux

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related