LDAP CONFIGURATION FOR USER AND GROUP CENTRALISED AUTHENTICATION ON UBUNTU 12.04 LTS – PART-1

I’m breaking down the tutorial into three parts, where the first part (PART-1) explains the basic user and group centralization, the second part (PART-2) explains how to setup automatic mounting of ldap user home directory using nfs and autofs, setting up disk quotas for ldap users and allowing an ldap user to change his own password and the third part (PART-3) helps to configure TLS (SSL cert based) LDAP authentication.

PART-1

Before going directly into the configurations, I’ll explain the basic concepts of LDAP. LDAP stands for Lightweight Directory Access Protocol. If you want to make a directory information available over the network, this is the way to do it. What makes LDAP so useful is that it works over TCP/IP networks, so the information can be accessed through LDAP by anyone with an internet connection as well. It is also an open protocol, which means directories can be stored on any type of machine (i.e. Windows 2000, Red Hat Linux, Ubuntu, Mac OS X). The protocol is client-server related, where the client makes requests to the server and the server addresses those requests. So basically LDAP is a protocol that is designed to access information.

One thing to keep in mind is that LDAP is a directory not a database. So wherever I use the term database, I’m referring LDAP directory. One major advantage of LDAP directory over database is that directories are designed for reads while a database is really designed to read and write. This makes directories much faster for finding information. You will need to have a clear idea about object oriented programming model for better understanding about the LDAP Directory structure in detail.

Object Oriented Model was introduced to make the application development in a more structural and organized way. This basically works on the concept of objects and classes. I’ll explain the basics of a class and an object here using a real life example. If I want to develop an application to store the details of certain cars, I’ll create a class named cars. Then I’ll define attributes like color, no: of gears, model etc to the class. Once the class attributes are defined, I’ll create a new object called Mercedes-Benz under car class. Then we can assign values to the newly created object using the attributes of class cars. So a class is a collection of objects having similar attribute types. Basics of C++/Java or any other OOPs programming language is an added advantage for learning the concepts of Object Oriented Programming and LDAP.

LDAP works on the same principle. All the data or information are stored in directories/databases as variables and objects in a tree structure. The properties of the objects or variables are defined by the class it belongs to.

Before going further we should know what a SCHEMA is:

Schemas are something that packages or combines almost similar objectClasses and attributes. It is basically the the “design” of our directory structure. An objectClass is an attribute that helps to assign classes to objects. It defines the name of the class an object belongs to. ObjectClasses contain zero or more attributes. Attributes have names (and sometimes abbreviations or aliases) and typically contain data/information. An objectClass will have a name and set of mandatory and optional attributes.

For example an objectClass named organizationalPerson, defines sn and cn as mandatory attributes and userPassword, telephoneNumber, seeAlso and description as optional fields. A couple of those attributes probably need explaining. sn is surname and cn is common name, which we can use to store the person’s full name.

So an object defined in this class will share the attribute of the objectClasses defined. The objectClass may be part of a hierarchy in which it inherits all the characteristics of its parent objectClasses. There are a plenty of objectClasses available in the default LDAP default package which contains the attributes for almost all common applications. We can create custom objectClasses and add attributes to it based on the application we develop. But that are advanced configurations which I’m not covering in this tutorial.

Data is represented in an LDAP enabled directory as a hierarchy of objects, each of which is called an entry. The resulting tree structure is called a Data Information Tree (DIT). The top of the tree is commonly called the root or base DN.

Each entry in the tree has one parent entry (object) and zero or more child entries (objects). Each child entry (object) is a sibling of its parent’s other child entries. Most important thing to remember is that an entry(object) can be composed of (is an instance of) one or more objectClasses.

As I mentioned that LDAP has a tree directory structure a root node(object) must exist, which is called the base DN or root (DN= Distinguished Name). Base DN is used to uniquely identify the root of the DIT(Directory Information Tree) which is helpful in fetching data by starting the search from the root of ldap directory. DN is used to identify a particular object uniquely. A DIT is the complete tree structure of our LDAP directory tree.

We now need to know how to add objects/data to an LDAP directory. Here is where we learn about LDIF(LDAP Data Interchange Format) files.

The tree structure and initial population of data is performed by adding entries (with their associated objectClasses and attributes) starting from the root of the DIT and progressing down the hierarchy. Thus, a parent entry must always have been added before attempting to add any child entries. Adding entries may be done in a variety of ways, one of which is using LDIF files which I’m explaining in this tutorial. LDIFs are text files that describe the tree hierarchy (Directory Information Tree (DIT)) and the data to be added to each attribute. I’ll explain how the data is added to the LDAP directory as and when I’m explaining the configuration of LDAP on Ubuntu server LTS 12.04 below:

Let us now get into the actual configuration of LDAP for user centralization. I’m explaining the concepts of LDAP needed to set-up centralized user and groups for remote login on ubuntu 12.04.

For using LDAP as authentication of user accounts, we can use the posixAccount objectClass. This is an auxiliary class (class that inherits another class properties) and adds cn, uid, uidNumber, gidNumber and homeDirectory mandatory attributes and userPassword, loginShell, gecos and description as optional attributes.

The LDAP directory is a tree structure with a root node or the admin node under which all other nodes reside. We will begin with a basic tree containing two nodes below the root: (note that the node I’m referring is the Root directory object.

“People” node where your users will be stored

“Groups” node where your groups will be stored

Because posixAccount objectClass is auxiliary, we can add it to our People object for people/users that we want to authenticate.

I’m using my primary base DN as mentioned below:

dc=int,dc=sages,dc=com

so my FQDN is int.sages.com which is my root object.

Here dc is Domain Component. This is also part of a DN, and is often used to define the name of your top-level-domain/directory.

LDAP SERVER CONFIGURATION:

With the assumption that Ubuntu 12.04 LTS Server is already installed and hostname set to ldapserver.int.sages.com. You can use your desired hostname. But make sure that the hostname resolves correctly to the the ldap server. You can use the server IP(192.168.1.xxx) to configure LDAP if the DNS is not set for the hostname.

First login to the ubuntu server 12.04 LTS as root:

root@ubuntuserver:]#

make sure all the packages are updated and upgraded by running the following command

root@ubuntuserver:]# apt-get update && apt-get upgrade -y

Once all the packages are updated we are ready to install the LDAP packages and the necessary utils. You can install by the following command :

root@ubuntuserver:]# apt-get install slapd ldap-utils -y

During the installation you will be prompted for a ldap admin password twice which needs to be set. (screenshot shown below) I’m using the password as “sages123” in this tutorial.

(click to enlarge all screenshots)

Re-enter you Password once again.

Some additional information needs to be configured for slapd. Run the following command to reconfigure slapd

 

root@ubuntuserver:]# dpkg-reconfigure slapd

 

You will get a curses based CUI to reconfigure slapd. Configure slapd using the details mentioned below:

-> Omit OpenLDAP server configuration? No

 

->DNS domain name: The installation will ask for the DNS domain name. The DNS domain name is used to construct the base DN of the LDAP directory. My DNS Domain name will be int.sages.com. Configure the base DNS domain accordingly for your domain. The base DN will be the root directory of your LDAP server.

-> Organization Name: Enter the name of the organization to use in the base DN of your LDAP directory. I’m using Support Sages as my Organization name. You can use your’s.

-> Administrator password: You will be prompted to enter the LDAP admin password twice. I’m using sages123 as the password as I mentioned earlier.

Re-enter the password once again:

->Database backend to use: Select HDB as the backend databse to use.

 

Answer a few questions more:

 

->Do you want the database to be removed when slapd is purged? NO

->Move old database? Yes

->Allow LDAPv2 protocol? NO

Make sure that the following entries are set correctly in /etc/ldap/ldap.conf. Please note that this is the config file for LDAP server whereas it is /etc/ldap.conf for an LDAP client.

==============================================================

BASE dc=int,dc=sages,dc=com

URI ldap://ldapserver.int.sages.com

SIZELIMIT 12

TIMELIMIT 15

DEREF never

=============================================================

Once these are configured the LDAP server is ready for use.

The slapd configuration is stored as a special LDAP directory with a predefined schema and DIT. There are specific objectClasses used to carry global configuration options, schema definitions, backend and database definitions etc.

The slapd configuration tree has a very specific structure. The root of the tree is named cn=config and contains global configuration settings. Slapd is designed to be configured within slapd itself by specifying  a separate DIT for that purpose. This allows one to dynamically configure slapd without the need to restart the service. This configuration database consists of a collection of text-based LDIF files located under /etc/ldap/slapd.d. This way of working is known by several names: the slapd-config method, the RTC method (Real Time Configuration), or the cn=config method.
The slapd installation process set up two DITs. One for slapd-config(cn=config) and one for our own data
(dc=int,dc=sages,dc=com).

First lets add some logging level for cn=config. The olcLogLevel directive specifies the level at which debugging statements and operation statistics should be syslogged. This logging helps in troubleshooting common issues. For example all the LDAP authentication logs are in /var/log/auth.log by default.

Create a new file log.ldif and the entries as mentioned below:

root@ubuntuserver:]# vi log.ldif
================================
dn: cn=config
changetype: modify
add: olcLogLevel
olcLogLevel: stat
================================

Then use the ldapmodify command to modify cn=config entry(object) as shown below.

root@ubuntuserver:]# ldapmodify -Q -Y EXTERNAL -H ldapi:/// -f log.ldif

You will see an output like:

================================
modifying entry “cn=config”
================================

Once the loglevel is defined, we shall add some indices to ldap database to ease the directory information lookup using olcDbIndex directive.
So what are indicies? They are known ad LDAP indexes. As the name defines it helps to identify entries matching search criteria in an LDAP directory using indexes. Normally in LDAP there are three basic index types implemented:

Equality or Value indexes are used to identify entries containing an attribute value that exactly matches a given value.
Presence indexes are used to identify entries that contain at least one value for a given attribute.
Substring indexes are used to identify entries that contain an attribute value matching a given substring.

Let see how these are implemented in an LDAP server. Open a new file indices.ldif and the entries as mentioned below:

root@ubuntuserver:]# vi indices.ldif
================================
dn: olcDatabase={1}hdb,cn=config
changetype: modify
add: olcDbIndex
olcDbIndex: uid eq,pres,sub
================================

The fourth line above makes present, equality, and substring indices to be maintained for uid attribute types. The olcDbIndex directive specifies the indices to maintain for the given attribute. The index keywords correspond to the common types of matches that may be used in an LDAP search filter.

Then use the ldapmodify command as shown below:

root@ubuntuserver:]# ldapmodify -Q -Y EXTERNAL -H ldapi:/// -f indices.ldif

You will see an output as shown below:

=======================================
modifying entry “olcDatabase={1}hdb,cn=config”
=======================================

You can see the added indices by typing the following command:

root@ubuntuserver:]# ldapsearch -Q -LLL -Y EXTERNAL -H ldapi:/// -b cn=config ‘(olcDatabase={1}hdb)’ olcDbIndex

You should see the following output:

================================
‘(olcDatabase={1}hdb)’ olcDbIndex
dn: olcDatabase={1}hdb,cn=config
olcDbIndex: objectClass eq
olcDbIndex: uid eq,pres,sub
================================

That is it, we have configured our ldap server successfully and is ready to authenticate user.

It is now time to populate our LDAP directory so that we can authenticate  a user. LDAP directory uses LDIF data format. The LDAP Data Interchange Format (LDIF) is used to represent LDAP entries in a simple text format as I mentioned earlier. We can create LDIF files and then use the ldapadd command to add data. The ldap-utils package installed earlier helps to use common LDAP commands like ldapadd, ldapsearch, ldapmodify etc.

Below is an ldif file format for adding one OU named Users for storing user information and another OU named Groups under our base DN dc=int,dc=sages,dc=com

Create a new file named base.ldif and add the entries as given below:

root@ubuntuserver:]#  vi base.ldif

================================

# Create top-level object in domain

dn: dc=int,dc=sages,dc=com
objectClass: top
objectClass: dcObject
objectclass: organization
o: Example Organization
dc: Example
description: LDAP Example

#Creating User object

dn: ou=Users,dc=int,dc=sages,dc=com
objectClass: organizationalUnit
ou: Users

#Creating Group object

dn: ou=Groups,dc=int,dc=sages,dc=com
objectClass: organizationalUnit
ou: Groups

#Creating our first ldapuser “ldapuser1″ with uidnumber=10000 and gidnumber=10000 as user under Users OU

dn: uid=ldapuser1,ou=Users,dc=int,dc=sages,dc=com
objectClass: o3. ssl cert authenticationrganizationalPerson
objectClass: person
objectClass: top
objectClass: inetOrgPerson
objectClass: posixAccount
objectClass: shadowAccount
uid: ldapuser1
sn: Sages
givenName: Support
cn: Support Sages
displayName: Support Sages
uidNumber: 10000
gidNumber: 10000
userPassword: sages123
gecos: Support Sages
loginShell: /bin/bash
homeDirectory: /home/ldapuser1
mail: supportsages@gmail.com
telephoneNumber: 000-000-0000
st: NY
manager: uid=ldapuser1,ou=Users,dc=int,dc=sages,dc=com
shadowExpire: -1
shadowFlag: 0
shadowWarning: 7
shadowMin: 8
shadowMax: 999999
shadowLastChange: 10877
title: System Administrator

# Here we are naming the group with gid=10000 as ldapgroup1 under Groups OU

dn: cn=ldapgroup1,ou=Groups,dc=int,dc=sages,dc=com
objectClass: posixGroup
cn: ldapgroup1
gidNumber: 10000

================================

We define all the required attributes like cn(canonical name), homeDirectory, loginShell etc. Make sure to use a gid and uid number such that it doesn’t get conflicted with any of the system created users uid or gid. Also the userPassword attribute must be set correctly. As mentioned earlier,  I’m using sages123 as my slapd admin password. Now we use the ldapadd command to add the ldif file to the LDAP directory as shown below:

root@ubuntuserver:]# ldapadd -x -D cn=admin,dc=int,dc=sages,dc=com -w sages123 -f base.ldif

You will see a similar output as shown below:

==========================================================

adding new entry “OU=Users,dc=int,dc=sages,dc=com”
adding new entry “OU=Groups,dc=int,dc=sages,dc=com”
adding new entry “uid=ldapuser1,OU=Users,dc=int,dc=sages,dc=com”
adding new entry “cn=ldapgroup1,OU=Groups,dc=int,dc=sages,dc=com”
=============================================================
Thats it!! Everything has been setup on the server side. Make sure to create the home directory /home/testuser.

You can test if the ldapuser is added by the following command:

root@ubuntuserver:]# id ldapuser1

This should return the uid and gid of ldapuser1.

Now is the time to learn how to add new users and groups to our LDAP directory. Adding users using ldif file is a difficult process if the number of users are more. To overcome this we can use the ldapscripts.

root@ubuntuserver:]# apt-get install ldapscripts

Next, edit the config file /etc/ldapscripts/ldapscripts.conf uncommenting and changing the following to match your environment:

================================
SERVER=localhost
BINDDN=’cn=admin,dc=int,dc=sages,dc=com’
BINDPWDFILE=”/etc/ldapscripts/ldapscripts.passwd”
SUFFIX=’dc=int,dc=sages,dc=com’
GSUFFIX=’ou=Groups’
USUFFIX=’ou=People’
MSUFFIX=’ou=Computers’
GIDSTART=10000
UIDSTART=10000
MIDSTART=10000
================================
Now, create the ldapscripts.passwd file to allow authenticated access to the directory:

root@ubuntuserver:]# sh -c “echo -n ‘sages123′ > /etc/ldapscripts/ldapscripts.passwd”
root@ubuntuserver:]# chmod 400  /etc/ldapscripts/ldapscripts.passwd

The ldapscripts are now ready to help manage your directory. The following are some examples of how to use the scripts:

root@ubuntuserver:]# ldapaddgroup testgroup 10005

This will create a new group named testgroup with gid 10005

root@ubuntuserver:]#  ldapadduser testuser testgroup

The above command will create a new ldap user named testuser with primary group as testgroup.

root@ubuntuserver:]# ldapsetpassword testuser

The above command helps to set a new login LDAP password for our new user testuser

There are many more scripts like ldapdeleteuser, ldapmodify user etc. You can learn more about ldapscripts at http://manpages.ubuntu.com/manpages/lucid/man5/ldapscripts.5.html .

LDAP CLIENT CONFIGURATION:

Lets now proceed and see how an LDAP client can be setup for user authentication. I’m using UBUNTU DESKTOP 12.04 as my LDAP client.

Once you have a working LDAP server, the auth-client-config and libnss-ldap packages take the pain out of configuring an Ubuntu client to authenticate using LDAP. To install the packages login to LDAP client as root user and enter the following command in a terminal:

ldapclient@ubuntudesktop:]# apt-get install libnss-ldap

During the install a menu dialog will ask you connection details about your LDAP server.

->LDAP server Uniform Resource Identifier (the uri to identify our LDAP server) : ldap://ldapserver.int.sages.com

You can use the IP address of the LDAP server (192.168.1.xxx) if DNS is not configured for hostname.

-> Distinguished Name(DN) for base search: dc=int,dc=sages,dc=com

-> LDAP version to use: 3

-> Make local root database admin: YES

-> Does the LDAP database require login: NO

->LDAP admin account: cn=admin,dc=int,dc=sages,dc=com

-> LDAP root account password:

If you make a mistake when entering your information you can recall the dialog screen again using:

ldapclient@ubuntudesktop:]# dpkg-reconfigure ldap-auth-config

All the configuration are mirrored to /etc/ldap.conf. Make sure all the details are configured correctly in /etc/ldap.conf

Now that libnss-ldap is configured. Enable the auth-client-config LDAP profile by entering:

ldapclient@ubuntudesktop:]# auth-client-config -t nss -p lac_ldap

(-t: only modifies /etc/nsswitch.conf, -p: name of the profile to enable, disable, etc and lac_ldap: the auth-client-config profile that is part of the ldap-auth-config package )

Now use the pam-auth-update utility, configure the system to use LDAP for authentication:

ldapclient@ubuntudesktop:]# pam-auth-update

Make sure LDAP Authentication is also selected along with the already selected authentication methods. After this your /etc/nsswitch.conf file will have the entries for ldap.

You should now be able to login using user credentials stored in the LDAP directory.

To test this :

ldapclient@ubuntudesktop:]# su – testuser

You will be switched to testuser with present working directory as /home/testuser

testuser@ubuntudesktop:]#

This is the basic setup where we use LDAP for user and group centralization. Now lets proceed to PART-2 for further configurations on setting up automatic mounting of ldap user home directory using nfs and autofs, setting up disk quotas for ldap users and allowing an ldap user to change his own password.

Continue Reading

Simple backup script for remotely backing up databases

This backup script is actually a small modification of another backup script available in Google. I am not able to get that URL to mark/link reference. Only modification I added is a four liner to remove a week old backup automatically. You may also see http://www.supportsages.com/blog/2011/05/applying-wildcards-in-grant-option-of-mysql/ as well. Indenting in python is lost, will fix tomorrow

#!/usr/bin/env python
import ConfigParser
import os
import time

# Variable Definition
username = 'cpdbbackups'
password = 'p@ssw0Rd'
hostname = 're.mo.te.ip
backupfolder = '/home/dbbackups/servername'

filestamp = time.strftime('%Y-%m-%d-%H')
deletetime = time.time() - 7 * 86400

# Delete old files

for backup_file in os.listdir (backupfolder):
 full_file_path = os.path.join(backupfolder, backup_file)
 if os.path.getmtime(full_file_path) < deletetime:
 os.unlink(full_file_path)

# Get a list of databases with :
database_list_command="mysql -u%s -p%s -h %s --silent -N -e 'show databases'" % (username, password, hostname)
for database in os.popen(database_list_command).readlines():
 database = database.strip()

 if database == 'information_schema':
 continue
 filename = "%s/%s-%s.sql" % (backupfolder, database, filestamp)
 os.popen("mysqldump -u%s -p%s -h %s -e --opt -c %s | gzip -c > %s.gz" % (username, password, hostname, database, filename))

Continue Reading

Applying wildcards in GRANT option of MySQL

We had this particular requirement of creating a database user to be used to backup only the cPanel databases. Only recently the database mapping has been introduced by cPanel which allows the clients to create databases without the _ . However on all our client servers, we insist to have the old style with every database has Prefixing On.

Creating the database user to backup only the cPanel databases, means matching the databases with an underscore (_) in its name and that resulted in this particular SQL command to be executed as root user.

GRANT ALL PRIVILEGES ON `%\_%`.* TO `cpdbbackups`@`re.mo.te.ip` IDENTIFIED BY 'p@ssw0Rd' WITH GRANT OPTION;

Read more about the cPanel’s DB mapping at http://www.cpanel.net/blog/integration/2010/05/more-details-about-db-mapping.html

Continue Reading

MySQL for Beginners

In this post, I’ll explain some basic MySQL tips that would be very useful for beginners

Reset MySQL root password

Log in as Root and Stop the MySQL daemon. Then Start the MySQL daemon and skip the grant tables which store the passwords.

# /etc/init.d/mysql stop

# mysqld_safe --skip-grant-tables

# mysql -u root

mysql> use mysql;

Now you should be able to connect to mysql without a password. If not, it might be some other issues. Execute the command to reset the password

mysql> update user set Password=PASSWORD('new-password') where user='root';
mysql> flush privileges;
mysql> exit

After this you need to kill the running mysqld and restart it normally.

Creation of MySQL database, user and access rights

It should be very easy to create a database through cpanel or some other panel. But if you are on your own with only Shell access, try this :

# mysql -u root -p
 mysql> use mysql;
 mysql> INSERT INTO db (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES  ('%','databasename','username','Y','Y','Y','Y','Y','N');
 mysql> flush privileges;

Or you can use

mysql> grant all privileges on databasename.* to username@localhost;
mysql> flush privileges;

MySQLdump and Restore

The mysqldump client is a database backup.  It can be used to dump a database or a collection of databases for backup or transfer to another SQL server (not necessarily a MySQL server). The dump typically contains SQL statements to create the table, populate it, or both.

Creating a Mysqldump is quite easy. If you know the database name, say database :

# mysqldump database > database.sql

When restoring a Database, make sure you login as the user. In other words, do not restore a database with root privileges. It may cause real damage. Assuming that you are logged in as the user

<strong> </strong>
mysql> use dbname; //Which is the database which the backup has to be restored to
mysql > source olddb.sql; //Backup

Repair a corrupted Database

Switch to the database directory which is having issues with, Like :

# cd /var/lib/mysql/database

Stop the MySQL server

# /etc/init.d/mysql stop

Check the tables

# myisamchk *.MYI

Repair the tables

# myisamchk -r *.MYI

Start the MySQL server

# /etc/init.d/mysql start

phpMyadmin

phpMyAdmin is an open source tool written in PHP intended to handle the administration of MySQL over the World Wide Web. It can perform various tasks such as creating, modifying or deleting databases, tables, fields or rows; executing SQL statements; or managing users and permissions.

If you are having a cPanel or WHM interface, its quite easy to manage a database. In cPanel interface, go to Databases section

This is the main phpMyadmin page.

In WHM, at SQL Services you can perform a variety of options. As mentioned earlier, the database repair option (for corrupted databases) can be performed here also. Access the option ‘Repair a Database’

Select the Database name and Click Repair Database.

These are quite simple tasks associated with MySQL operations.

Continue Reading

PostgreSQL for the sage – Must know basics for the system administrators

PostgreSQL or Postgres is an object-relational database management system (ORDBMS). Unlike MySQL, PostgreSQL is not controlled by any single company, it is a community developed project. It is a advanced version of the ‘Ingres’ Database project (which is how the project gets the name post-ingres or postgres ).

Postgres is one of the best open-source database alternative which is fully object oriented and transactions compliant. It has stored procedures, multiple views and a huge set of datatypes. Some of the other notable features are as follows.

Objects and Inheritance

Database consists of objects and the database administrators can design custom or user-defined objects for the tables. Inheritance is another feature. Tables can be set to inherit their characteristics from a “parent” table.

Functions

Functions can be used in Postgres. These can be written in the postgres’ own procedural language called ‘PL/pgSQL’ which resembles Oracle’s procedural language ‘PL/SQL’ or any other common scripting languages which support posgtres’ procedural language like PL/Perl, plPHP, PL/Python, PL/Ruby etc. Run the following in the psql client to determine if functions is enabled:

SELECT true FROM pg_catalog.pg_language WHERE lanname = 'plpgsql'; 

To create user-defined functions we use the CREATE OR REPLACE FUNCTION command. Example:

CREATE OR REPLACE FUNCTION fib (

fib_for integer

) RETURNS integer AS $$

BEGIN

IF fib_for < 2 THEN

RETURN fib_for;

END IF;

RETURN fib(fib_for - 2) + fib(fib_for - 1);

END;

$$ LANGUAGE plpgsql;

Indexes

An index is like a summary of a certain portion of the table. It is an optimization technique which increases speed of accessing records from a database. PostgreSQL supports indexes like Btree, hash etc. User-defined index methods can also be created. Indexes are created on tables with respect to a particular field (based on which there are a number of queries). As an example for a table:

CREATE TABLE name (

id integer,

fname varchar

lname varchar

);

To create an index on table name with respective to the field id (as there are many queries on this table requesting for firstname or lastname from the id provided), we use the index:

CREATE INDEX name_id_index ON name (id);

Triggers

Triggers are events or functions run upon the action of certain SQL statements which modify data in some records. Depending on the kind of modification we can have multiple triggers in a database. Postgres supports multiple triggers written in PL/PgSQL or it’s scripting counterparts like PL/Python. The trigger function must be defined before the trigger can be created. The trigger function must be declared as a function taking no arguments and returning type trigger. CREATE TRIGGER command is used to declare triggers.

Concurrency

PostgreSQL ensures concurrency with the help of MVCC (Multi-Version Concurrency Control), which gives the database user a “snapshot” of the database, allowing changes to be made without being visible to other users until a transaction is committed.

PostgreSQL’s MVCC keeps all of the versions of the data together in the same partition in the same table. By identifying which rows were added by which transactions, which rows were deleted by which transactions, and which transactions have actually committed, it becomes a straightforward check to see which rows are visible for which transactions.

Inorder to accomplish this, Rows of a table are stored in PostgreSQL as a tuple. Two fields of each tuple are xmin and xmax. Xmin is the transaction ID of the transaction that created the tuple. Xmax is the transaction ID of the transaction that deleted it (if any).

Along with the tuples in each table, a record of each transaction and its current state (in progress, committed, aborted) is kept in a universal transaction log.

When data in a table is selected, only those rows that are created and not destroyed are seen. That is, each row’s xmin is observed. If the xmin is a transaction that is in progress or aborted, then the row is invisible. If the xmin is a transaction that has committed, then the xmax is observed. If the xmax is a transaction that is in progress or aborted and not the current transaction, or if there is no xmax at all, then the row is seen. Otherwise, the row is considered as already deleted.

Insertions are straightforward. The transaction that inserts the tuple simply creates it with the xmax blank and the xmin set to its transaction ID. Deletions are also straightforward. The tuple’s xmax is set to the current transaction. Updates are no more than a concurrent insert and delete.

Views

A view is a table which does not exist in the database. It is a virtual table created from fields in various tables and is joined together based on some criteria. Views can be used in place of tables and will accomplish the task same as that of a table. The CREATE VIEW statement is used to accomplish this eg:

CREATE VIEW best_sellers AS

SELECT * FROM publishers WHERE demand LIKE 'high';

Foreign Keys

The primary key used in one table which is used to refer to the records in a second table is called the foreign key of the second table.

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);
CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    product_no integer REFERENCES products (product_no),
    quantity integer
);

Here product_no is the foreign key in the second table created. The foreign key field may have values which are repeated unlike primary keys.

Files Users and Configuration

The main configuration file of Postgres is postgresql.conf. This can be located in the ‘data’ directory. It may be present either in /var/lib (/var/lib/pgsql/data/postgresql.conf) or /usr/local (/usr/local/pgsql/data/postgresql.conf). Temporary changes to the configurations can be made using postmaster command.

The init script that starts the postgres service is /etc/init.d/postgresql . It runs a number of child processes concurrently. The postgres server process is postmaster. These processes and files associated with PosgreSQL are owned by the user/group postgres. The default port used for database connections is 5432

The user postgres is the PostgreSQL database superuser. We can create a number of super users for the database (this accomplished by the create role command ), however, the default super user is postgres. The postgres user has the privilege to access all the databases and files in the server (Unless the user root is created in postgres as a superuser).

Client Authentication is controlled by the file pg_hba.conf in the data directory, e.g., /var/lib/pgsql/data/pg_hba.conf. (HBA stands for host-based authentication.)

Each record specifies a connection type, a client IP address range (if relevant for the connection type), a database name or names, and the authentication method to be used for connections matching these parameters.A record is typically in one of two forms:

local database authentication-method [ authentication-option ]

host database IP-address IP-mask authentication-method [ authentication-option ]

local : This record pertains to connection attempts over Unix domain sockets.

host : This record pertains to connection attempts over TCP/IP networks.

database : Specifies the database that this record applies to. The value all specifies that it applies to all databases, while the value sameuser identifies the database with the same name as the connecting user.

authentication methods

trust: The connection is allowed unconditionally.

reject: The connection is rejected unconditionally.

password: The client is required to supply a password which is required to match the database password that was set up for the user.

md5: Like the password method, but the password is sent over the wire encrypted using a simple challenge-response protocol.

ident: This method uses the “Identification Protocol” as described in RFC 1413. It may be used to authenticate TCP/IP or Unix domain socket connections, but its reccomended use is for local connections only and not remote connections.

Front-ends

The minimalistic front-end for PostgreSQL is the psql command-line. It can be used to enter SQL queries directly, or execute them from a file. phpPgAdmin is a web-portal used for PostgreSQL administration written in PHP and based on the popular phpMyAdmin. Likewise pgAdmin is a graphical front-end administration tool for PostgreSQL, which has support on multiple platforms. The latest stable version of the same is pgAdmin III.

Some administration related commands

Command to login to psql database mydb as user myuser:

psql -d mydb -U myuser

Command to login to psql database mydb as user myuser on a different host myhost:

psql -h myhost -d mydb -U myuser

If the port the server runs is different we use -p [port number] . Upon entering the psql shell the prompt will show the database name currently being used. In the above example it will show

mydb=> (if logged in as an ordinary user )

mydb=# (if logged in as a super user like postgres)

Create a PostgreSQL user

There are two ways to create a postgres database user. The only user initially allowed to create users is postgres. So one has to switch to this user before creating other users with varying privileges.

1. Creating the user in the shell prompt, with createuser command.

switch to the postgres user with:

su - postgres

createuser tom

Shall the new role be a superuser? (y/n) n

Shall the new role be allowed to create databases? (y/n) y

Shall the new role be allowed to create more new roles? (y/n) n

2. Creating the user in the PSQL prompt, with CREATE USER command.

switch to the postgres user with:

su - postgres

create user mary with password 'marypass';

Creating and deleting a PostgreSQL Database

There are two way to create databases.

1. Creating database in the PSQL prompt, with createuser command.

CREATE DATABASE db1 WITH OWNER tom;

2. Creating database in the shell prompt, with createdb command.

createdb db2 -O mary

To delete an entire database from within the psql prompt do :

DROP DATABASE db1;

Determining execution time of a query

Turn on timing with

\timing

Now execute the qery:

SELECT * from db1.employees ;

Time: 0.065 ms

Calculate postgreSQL database size in disk

SELECT pg_database_size('db1');

to get the values in human readable format

SELECT pg_size_pretty(pg_database_size('db1'));

to calculate postgreSQL table size in disk

SELECT pg_size_pretty(pg_total_relation_size(‘big_table’));

Slash commands used in psql

To list all slash commands and thier purpose. Login to psql and issue to the command \? . Some of the most commonly used slash commands are the following:

List databases	\l
System tables	\dS
Types	\dT
Functions	\df
Operators	\do
Aggregates	\da
Users	\du
Quit from psql	\q
Connect to different database db2	\c db2
Describe Table/index/view/sequence	\d

The below can be used with a specific table/index/view name for description of the specific table/index/view

Tables	\dt
Indexes	\di
Sequences	\ds
Views	\dv

Useful Bash commands

Bash command to list all the postgresql databases:

psql -l #This can be run as a unix user who is also a super user in postgresql

Indirect bash command to list all the postgresl users:

psql -c '\du' #-c is used to run an internal or sql command in psql shell

Backing up and restoring databases

To dump the database to an sql file use the bash command:

pg_dump mydb > db.out

To restore a database from an sql backup file (via bash)

psql -d newdb -f backupdb.out

or

psql -f backupdb.out newdb

(here the database newdb must be already created and the file backupdb.out must be present in the current directory)

To take the backup of all the Postgres databases in the server:

pg_dumpall > /var/lib/pgsql/backups/dumpall.sql

(Only possible with the postgres or the database superuser )

Resetting database user’s password

To change the password for a database user (say ‘thomas’):

ALTER USER thomas WITH PASSWORD 'newpassword';

This same command can be used to reset the password for the postgresql super user postgres, but in this case, you will have to enable password less login for postgres user by adding the following line to the top of the file pg_hba.conf in the data directory of postgres. Once the password is reset this line can be removed:

local	all	postgres	trust

Next we issue the same command but for the user postgres

ALTER USER postgres WITH PASSWORD 'newpassword';

To create a super user via bash with multiple roles

createuser -sPE mysuperuser

Instead of this we can also use the below psql shell command:

CREATE ROLE mysuperuser2 WITH SUPERUSER CREATEDB CREATEROLE LOGIN ENCRYPTED PASSWORD 'mysuperpass2';

Physical database files in postgres

The files in data/base are named by the oid (Object Identifier) of the database record in

pg_database, like this:

cd /var/lib/pgsql/data/base

ls -l

total 33

drwx------ 22 postgres postgres 4096 Jul 23 20:06 ./

drwx------ 11 postgres postgres 4096 Aug  1 05:59 ../

drwx------  2 postgres postgres 4096 Jun 20 09:32 1/

drwx------  2 postgres postgres 4096 Mar  3 13:36 10792/

drwx------  2 postgres postgres 4096 Jun 20 15:09 10793/

drwx------  2 postgres postgres 4096 May 27 01:40 16497/

drwx------  2 postgres postgres 4096 May 27 01:40 16589/

drwx------  2 postgres postgres 4096 Jun 20 10:28 16702/

drwx------  2 postgres postgres 4096 May 27 01:40 16764/

drwx------  2 postgres postgres 4096 May 27 01:40 16785/

drwx------  2 postgres postgres 4096 Aug  1 04:37 16786/

drwx------  2 postgres postgres 4096 Aug  1 04:36 19992/

drwx------  2 postgres postgres 4096 May 27 01:40 19997/

To obtain the oid, execute the following command in psql prompt

postgres=# select oid,datname from pg_database order by oid;

   oid  |         datname

---------+--------------------------

1 | template1

10792 | template0

10793 | postgres

16497 | gadgetwi_Unable

16589 | vimusicc_filehost

16702 | personea_altissimo

16764 | shopping_businessfinance

16785 | ansonyi_wp2

16786 | ansonyi_wp

19992 | globook_PostgreSQL

Continue Reading