Some Stack Exchange site's I'd like to see come to fruition

• Unix & Linux: I think it'd be more used than Super User and ServerFault for some questions.
• Outer Join: A place to ask questions about databases and queries
• User Interface: because this is something we could all do better with.

Please consider following/committing. esp the unix/linux one (since I don't think Ubuntu should get to have its own, while leaving the rest of us in the cold.)

PostgreSQL initial setup (authentication) Part 2

I ran into so problems and lack of information with my last post on this topic. Firstly my syntax for local all all to local all all ident devel
doesn't seem to work in my current setup. It's possible that it has something to do with the configuration of the Debian/Ubuntu server I was basing that against, and now my targets are Arch Linux and Slackware.

So our goal here will be to provide an alternate user that can log in as postgres via ident. why would you want to do this? maybe you're tired of su - to the postgres unix user after logging in as your administrative user (root?).

First we need to open our pg_ident.conf and add the following line.

admin root postgres

So as I said in the last post on this: "admin" is an arbitrary identifier, root is the system user, and postgres is the database user. Now let's go ahead and make sure the postgres system user can still login.

admin postgres postgres

ok that's all we need to do in the pg_ident.conf. Go ahead and open pg_hba.conf and add this line above the default 3 lines in the file.

local all postgres ident map=admin

as a breakdown: local is for socket connections, all means all databases, ident means it's checking for local users, and map=admin says look at the users in the admin identifier in pg_ident.conf.

Now you should be able to run psql dname postgres as root. You will not be able to use the db postgres user as any system user not in the admin map.

Difference Between a Database and a Database Instance

I was prowling StackOverflow today and the question was asked when you would use 2 databases. I'm amazed at the number of answers of replication and sharding (some under the guise of some other description). The only answer I can think of to be correct is Different Applications/Services (shared hosting probably falls under those 2 in a weird way). I'm going to explain why a replicated database and/or a sharded database are not multiple databases.

Firstly, we must understand what a database is and is not. A database is not a computer program that runs and stores data, this is DBMS (Database Management System). A database simply is a collection of data. A database is an organized collection of data. Many Definitions of a Database include computers, however not all do, in fact Joe Celko argues that first database were precursors to written language in the Middle East (see Data and Databases Chapter 1). It may be true that database are organized for quick retrieval of information in a certain way, however they are not necessarily designed for quick retrieval of all information, in any way.

So what are some example's of a Non Computer Database? A phone book is a good example of a database. It's really fast to look someone up if you know there last name, but not if all you have is their phone number and you want their address. You'll Also note that it's got various ways of searching if you're looking for businesses. It is not a good example of a normalized database, but I'm not sure that's possible with a paper database that allows you to find the same information in multiple ways. A phone book is simply a collection of Names, Addresses, and Phone Numbers, and Categories (tags).

So wouldn't two phone books be two databases? well if they are published by different vendors, or at different times, then yes. However, if you have two copies of the same phone book, then you have two instances of that database, that have been 'replicated' from a 'master' copy. You can't update one of these 'slave' copy's and expect them to replicate, you must update the 'master' and publish the updates. Unlike computer Phone books are extremely slow to push updates. Let's say you want to call a friend, Steven, over for pizza, and your roommate, John, wants to try a new pizza place. John can look at one copy of the phone book to look up the addresses and phone numbers of to the new pizza places you haven't tried, while you look up Steven's number in your copy of the book. This is exactly what happens with replication, different requesters doing lookups from different databases.

Now let's talk sharding, phone books aren't sharded right? wrong! they are phone books are sharded by location. The entire phone book database for even 1 state would be too big for them to distribute, so they split it up into smaller regions. Only the phone book company has the entire list. So that makes it kind of a bad example. Let's talk about encyclopedia's instead. They are a much better example since you don't generally buy one encyclopedia, you buy the whole set (which is also replicated and you can think of it's replication in the same way as phone books). Each encyclopedia volume tells on the side which letters it contains, so when you open it you know you're in the right vicinity. This is how a shard works, your database got too big an unmanageable as a whole so you split it up into 'shards' or 'partitions' to make it faster to search and easier to handle and store.

Now to clarify my answer to that original question. You wouldn't put a phone number (phone entry not included) in an encyclopedia even though I'm sure Bill Clinton has one, you might list it in the phone book though. So now you see when you would use a different database. Very clearly you would use one when the type of data is different enough, when it's for different organizations, or when it's for kind of shared service.

I hope these analogies to computer databases have helped explain the concepts.

PostgreSQL initial setup (authentication)

If you followed my last initial setup post on creating users and are running a default install of postgres you currently should be able to access the database on your cli. However, many distributions do not install postgres in a default manner. A great many distributions make the default authentication for sockets ident sameuser. This is technically much more secure than trust which is the default. However, you may find yourself locked out of your database on the local cli if you make the database name different. I personally was confused about this after first encountering this setting.

So, we don't want trust but we want to use a db username other than our shell login, most likely because we have more than one database. There are several options but I personally think ident is a good one. However, since we don't want to use the same shell login name we have to modify pg_ident.conf and pg_hba.conf, locations vary by distribution.

In pg_ident.conf you have to create a line with the following formatting.

# MAPNAME IDENT-USERNAME PG-USERNAME

I think it's mostly straightforward. In case it isn't, MAPNAME is an arbitrary identifier, sameuser is actually the mapname in ident sameuser A quick example from mine would be

devel xenoterracide webdevwhere my unix username is xenoterracide but I created the database user webdev. If you wanted you could add another devel mapname with another user or the same unix account different db account, or even a different unix account same db account, etc.

After you add all the various mappings you need to add or change the ident in pg_hba.conf. You can only have one method per type/database/user/address combination. so in pg_hba.conf you want to change

local all all to local all all ident devel

If you want postgres to ask for a password use md5 instead of ident further information can be found at http://www.postgresql.org/docs/current/interactive/auth-methods.html.

if you have any problems you might want to see part 2.

postgresql initial setup (users and databases)

This guide assumes that you've managed to install and start the postgres server. I'm ignoring these because they are well documented in several places, probably including your distribution, however, what to do next isn't.

first you need to connect to the postgres database with psql as postgres, on gentoo* distributions you must be in the postgres group to do this. In the event this doesn't work you may have to su to the postgres unix user account somehow and run psql from there.

psql postgres postgres

you now have connected to what is essentially the 'root' user for postgres. as with all 'super user' accounts be careful and know what you are doing. also, as with all 'super user' accounts you don't normally want to run as them, so we need to create another user. I recommend creating the user with the same name as whatever shell account you may be connecting to it from (if applicable) because then you don't have to specify the username when typing psql.

CREATE USER xenoterracide WITH PASSWORD 'YeahLikeIdTellYou';

next you need to give the user a database. like the user account if you make it the same name as the shell account you won't have to pass it to psql.

CREATE DATABASE xenoterracide WITH OWNER xenoterracide;

now you're done with the setting up your users account so quit psql by tying \q at the prompt. now you can simply log in from your users shell by typing psql and SQL-ing away.