How do I get postgresql configured so I can actually use it?
I followed the wiki page here: Postgresql - openSUSE
Then I created a user (without a password) and attempted to connect via psql -U username which results in
psql: FATAL: Ident authentication failed for user “username”
So I searched some more and found: /dev/loki: PostgreSQL on openSUSE
Thought stopping postgresql and adding the md5 authentication methode might help. It did not.
What gives? And more importantly what do I need to do to create a database and be able to connect to it? Need it up and running to connect a java application to it.
It’s been a while since I set up postgresql but IIRC you need to configure authentication first before you can login to create databases. The config file to edit is somewhere in the postgresql data directory. ident means it’s checking your username against the user database on the local machine, you need an identd server running for this method. You can also use passwords, that’s what the md5 method is for. Look for some postgresql tutes, the info should be pretty much distro independent.
PS: remember there is a postgresql package and also a postgresql-server package. Like mysql you need the server running.
Yeah it’s mentioned in the first link above and do have both installed.
Seems to be running to, and the files do seem to be in /var/lib/pgsql just chmodded to something I can’t see as a normal user.
Obviously those files have to be edited as root since they are not visible to normal users as they contain sensitive config data. However be careful not to change the ownership or permissions of those files because postgresql runs in its own account so it needs to be able to read those files.
I’m used to at least seeing the files in my filebrowser, needing the proper rights to edit them is a given.
Well I finally managed to connect (via phpPgAdmin, hope my own program is soon to follow) although I’m not sure what I’ve been doing, nor do I care atm as my priority is having it run… security and everything else are of no importance at all (for now).
It’s not just security. Sometimes things won’t work properly with the wrong permissions. Obviously the cure to not being sure what you are doing is to get informed. It could save you wasted effort.
With this having to work pretty much on monday on mysql, postgresql and derby I really don’t have the time to be reading up on things. It was ment more as a warning than anything else as I’ve no clue if the article I linked to is actually a good one to follow, seemed a bit ‘too simplistic’ to me.
But I’m in no position to judge.
Once I’ve time and the need to set it up properly I’ll see about making a wiki page about it as the current article is severely lacking useful information.
If you want to connect to postgres from Java, you need to make it listen on a TCP port.
Make sure you edit the postgresql.conf file and enable the entries as follows:
listen_addresses = "*"
post = 50000
max_connections = 100
(You may specify the listen_addresses parameter to the network interface of your choice if you don’t want it to listen on all the interfaces. Also, choose the port number as per your choice).
In the JDBC driver, this port number has to be specified.
Also, make sure that you have edited the pg_hba.conf (go towards the end of the file) for allowing connection to the server from other machines.
An entry like the following will allow connections from other machines:
host all all 127.0.0.1/32 password
Please note that the default installation DOES NOT listen on any TCP ports.
I got this book “Beginning Databases with PostgreSQL” from Appress that has helped me a great deal. They provide examples of all the GUI development tools (PgAdminIII,etc) as well as entire chapters on connecting with Java, C, PHP, C#, and PERL.
Recently have used Embedded SQL with C for executing commands to the database.
I find myself using the command-line tool psql both for performance reasons and to become familiar with the postgres commands.
Did you install from the SUSE distro or from RPMs? It makes a difference because SUSE tends to distribute the installation into separate places.
For example, the databases are stored at /var/lib/pgsql/data. The postgres executable is stored in /usr/bin.
Not sure if this helps…what are you having trouble with?
The first two steps I took after I installed postgres was to ensure the postgres account was the owner of the data directory and initializing the database with initdb.
The book guides you through all this. The authors recommend you assume the identity of the postgres account in order to run the commands.
su - postgres
If you already know this, slap me and call me silly.rotfl!
If security is not a problem and I must reinforce this issue:
The start-up scenario for you is simple.
First: Create the user postgres.
This is fundamental.
Give that user access to the shell and set for a decent password for that user. (By decent I mean long and complex )
You can do this in yast. If postgresql is installed that user already exists, just change password and give it shell access.
You do not need to change Any permission on Any file as OpenSuSE does install postgresql correctly.
This is not a Secure setup!
It is only meant for access on a local computer. Not a networked one.
but it allows you to start the DB engine and work with no access problems on the computer where it is installed.
Also:
You only created the main root user for postgresql, postgres.
I strongly advice you to create Another user and make for that user a role on the database. The user could be a new superuser. Again you must plan you security settings and database usage/purpose.
Them make that user Own the databases you want to create and use.
And then you should use that user as the working user on the database.
Remember postgresql users must be System users. So when I said create a user it must be created on your OpenSuSE system. Go to yast and do it. It is also convenient to give that user shell access.
The above setup works on OpenSuSE 10.1, 10.3, 11.0 and 11.1.
Now your applications and jdbc/odbc drivers can work ok and access the DB you created on the specified users/roles.
Although the solution I just gave will jump-start your database I strongly advise you to take a look on postgresql security and access control.
It is actually a very good database in that concern.
)