Postgresql is and avanced DBMS. I'm just learning about it, and I'm quite impressed. So I decided to give it a try, caveot emptor, these are new lands, not Redmond anymore

Configure Postgresql 9.3 in Ubuntu 12.04

WTF, Postgres?

Well, I'm just starting experimentig (for profit, obviously) with postgresql. Installation under Ubuntu is quite straight so you shouldn't have any problem. Go on and follow the instructions from this postgresql wiki page.

Postgres is so advanced, compared to SqlServer that I don't even where to start from, so let's start from the beginning:

Configuration

As you can imagine, postgresql has quite the cli interface. Do not worry, actually this gives us a lot of scripting ease. -maybe we'll talk about that later.

When you installed Postgresql, it created a system user called postgres. For now, these is the only system user who can connect to postgres. This is a good thing, security out of the box.

So our first step will be:

Create a postgres user login for our system user

You won't be able to run postgresql administrative right now with your current system user (I mean, your linux-windows-osx user) so you'll have to start a session using the provided postgres user. Do this with:

$sudo su postgres

Once logged in as postgres, you can run the administravie task to create a postgres user matching your system user. Do that by:

$createuser --interactive fabrizio
$createdb fabrizio
$createdb test

Now, you can logout of postgres session doing:

$exit

Now you can simply start a postgresql client and be logged inmediatly. (let's test creating a database since you need it to login anyways):

$psql

You're ready to go. Exit psql pressing CTRL+D.

User Permission

As you could notice, you were able to login into postgresql without a password. This is a bad thing. We need to inmediatly set a password for our user. We can do this with:

alter user fabrizio with password ‘yourPassWordHere’;

Ok, now let's try conne at cting again:

$psql -d DATABASENAME

(by default, postgresql will try to connect to a Data Base with the same name as the user that is trying to connect).

Remote Access

Remote access is configured in a file called pg_hba.conf. Here we defines rules for which users. connectig from which places, can connect to which databases. For simplicity we’ll say that everyone connecting with an encrypted password may connect to any database that they have permissions to. We could say that accessing certain databases is forbidden from the internet, or certain users may not connect from the internet. There are a lot of combinations, and I’d encourage you to read up about them on The Postgres Website. In Ubuntu 12.04 you can find pg_hba.confat /etc/postgresql/9.1/main/pg_hba.conf

# Database administrative login by Unix domain socket
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# “local” is for Unix domain socket connections only
local   all             postgres                                peer
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
host    all             all             0.0.0.0/0               md5

In order to see the changes, restart your postgres with:

$sudo service postgresql restart

Remember, method md5 is preferred over password. A homework or the user here is guessing why ;) (I hope Future Fabzter will remember, but maybe he won't because he is an asswhole and Past Fabzter is da shit).

Enable remote connection

This one is easy. Remember pg_hba.confat /etc/postgresql/9.1/main/pg_hba.conf? Well, edit it and be sure to add the line

host all all  0.0.0.0/0 md5

To allow all users from all host to connect to all databases. Now, we just have to tell postgres to actually listen for connections. This is done with another file: postgresql.confat /etc/postgresql/9.1/main/postgresql.conf. Be sure to change the value listen_addresses to accept all addresses with:

listen_addresses = '*'

Save both files and restart your postgres. Now you can connect remotely!

First steps

Ok, now we have made a basicly secured server. Let's start stretching our fingers at postgres. Let's connect using psql to the test database.

$psql -d test

Enter your password and come along.

Create a table

Let's create a table. I hope you get the structure by just watching the create statement.

$CREATE TABLE Person (Id serial PRIMARY KEY NOT NULL, Name varchar(50), Confirmed bool, SignupDate date);

Now, you can list tables if you execute:

\dt


I really hope this post was helpful to anyone. Be good and Happy Coding!

Posted by: fabzter
Last revised: 30 Nov, 2013 09:31 PM History

Comments

Daniel
Daniel
13 Aug, 2015 02:38 PM

Thank you, that tutorial was really helpful to me!

Your Comments

Used for your gravatar. Not required. Will not be public.
Posting code? Indent it by four spaces to make it look nice. Learn more about Markdown.

Preview