PostgreSQL Database


Configuring PostgreSQL on ORHEL6.1

posted Apr 22, 2013, 5:47 AM by Sachchida Ojha

-bash-4.1$ initdb -D /var/lib/pgsql/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale en_US.UTF-8.
The default database encoding has accordingly been set to UTF8.
The default text search configuration will be set to "english".

initdb: directory "/var/lib/pgsql/data" exists but is not empty
If you want to create a new database system, either remove or empty
the directory "/var/lib/pgsql/data" or run initdb
with an argument other than "/var/lib/pgsql/data".
-bash-4.1$ rmdir /var/lib/pgsql/data
rmdir: failed to remove `/var/lib/pgsql/data': Directory not empty
-bash-4.1$ rm -rf /var/lib/pgsql/data
-bash-4.1$ initdb -D /var/lib/pgsql/data
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale en_US.UTF-8.
The default database encoding has accordingly been set to UTF8.
The default text search configuration will be set to "english".

creating directory /var/lib/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 32MB
creating configuration files ... ok
creating template1 database in /var/lib/pgsql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... ok
creating dictionaries ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok
copying template1 to postgres ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

    postgres -D /var/lib/pgsql/data
or
    pg_ctl -D /var/lib/pgsql/data -l logfile start

-bash-4.1$
-bash-4.1$ pg_ctl status
pg_ctl: server is running (PID: 10912)
/usr/bin/postgres "-D" "/var/lib/pgsql/data"
-bash-4.1$ psql
psql (8.4.7)
Type "help" for help.

postgres=# \df
                       List of functions
 Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)

postgres=#

pg_ctl -D /var/lib/pgsql/data -l logfile start




SECURITY DEFINER or AUTHID CURRENT USER

posted Apr 3, 2013, 5:54 PM by Sachchida Ojha

I've just come to minor notice: PostgreSQL defaults to executing procedures with current user rights, Oracle - with definer's rights. It was rather interesting.

Let's say we have departments table in Oracle:

alp@XE>descr departments
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- -----------------------
 DEPARTMENT_ID                                                  NUMBER(4)
 DEPARTMENT_NAME                                       NOT NULL VARCHAR2(30)
 MANAGER_ID                                                     NUMBER(6)
 LOCATION_ID                                                    NUMBER(4)

And we create a function to access this table's data:

create type department_row AS OBJECT  (department_id int, department_name varchar2(30), manager_id number, location_id number);

create type department_table AS TABLE OF department_row;

create or replace function showData return department_table PIPELINED IS
r department_row:= department_row(null,null,null,null);
CURSOR c1 IS SELECT * FROM  DEPARTMENTS;
BEGIN
 FOR  i in c1 LOOP
    r.department_id:=i.department_id;
    r.department_name:=i.department_name;
    r.manager_id:=i.manager_id;
    r.location_id:=i.location_id;
    PIPE ROW(r);
 END LOOP;
END;
/

Now we can just grant execute on this function to some user:

alp@XE>grant execute on showData to tu;

And this user can view data in departments table:

tu@XE>select * from TABLE(alp.showData());

DEPARTMENT_ID DEPARTMENT_NAME                MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
           10 Administration                        200        1700
           20 Marketing                             201        1800
           30 Purchasing                            114        1700
           40 Human Resources                       203        2400
           50 Shipping                              121        1500
           60 IT                                    103        1400
           70 Public Relations                      204        2700
           80 Sales                                 145        2500
           90 Executive                             100        1700
          100 Finance                               108        1700
          110 Accounting                            205        1700
          120 Treasury                                         1700
          130 Corporate Tax                                    1700
          140 Control And Credit                               1700
          150 Shareholder Services                             1700
          160 Benefits                                         1700
          170 Manufacturing                                    1700
          180 Construction                                     1700
          190 Contracting                                      1700
          200 Operations                                       1700
          210 IT Support                                       1700
          220 NOC                                              1700
          230 IT Helpdesk                                      1700
          240 Government Sales                                 1700
          250 Retail Sales                                     1700
          260 Recruiting                                       1700
          270 Payroll                                          1700

27 rows selected.

In PostgreSQL you should explicitly set definer's rights when creating function:

CREATE TYPE data_rowtype as (ip inet, traffic integer);

create function showData () returns SETOF DATA_ROWTYPE AS '
select * from data
'
LANGUAGE SQL 
SECURITY DEFINER; 

And after

grant execute on function showData() to tu;

tu can view data in table "data":

alp=> select * from showData();
       ip       | traffic  
----------------+----------
 10.1.1.6/24    | 24117248
 192.168.1.2/24 |  3145728
 10.1.1.7/24    |  4194304
 10.1.1.8/24    |  8388608
 10.1.1.2/24    | 90177536
 10.1.1.4/24    | 24117248
(6 rows)

In general, PostgreSQL's ability to use SQL in function definition is something great (1 line of  SQL instead of 10 PL/SQL - Oracle is bitten :) ). However, there are differences from Oracle's behaviour to remember... 

Information Schema in PostGreSQL

posted Sep 18, 2010, 6:36 AM by Sachchida Ojha

The information schema itself is a schema named information_schema. This schema automatically exists in all databases. The owner of this schema is the initial database user in the cluster, and that user naturally has all the privileges on this schema, including the ability to drop it (but the space savings achieved by this are minuscule). By default, the information schema is not in the schema search path, so you need to access all objects in it through qualified names. Since the names of some of the objects in the information schema are generic names that might occur in user applications, you should be careful if you want to put the information schema in the path.

The information schema is a facility to provide a standardized description of PostgreSQL metadata: definitions of tables, views etc. The information schema is defined in the SQL standard and should remain stable between PostgreSQL versions (unlike the system catalogues, PostgreSQL's "data dictionary") and should also be compatible with other database systems offering the same feature.

The information schema is kept in a separate schema - information_schema - which exists in all databases, but which is not included in the search path by default. It is implemented as a set of views returning specially defined datatypes as required by the standard. These types are implemented as simple domains over ordinary built-in types. information_schema can be dropped by the owner, but this does not provide any significant release of space or other advantages.

Example:

SELECT table_name FROM information_schema.tables WHERE table_schema='public'

will display the names of all tables in the default public schema.

Note that although the information schema also describes the system catalogue objects, it does not contain information on PostgreSQL-specific features. As always, invoking psql with the -E option and issuing appropriate slash commands will display the SQL used to query the system catalogues directly.

What are template1 and template0?

posted Sep 18, 2010, 6:34 AM by Sachchida Ojha

When you do an initdb two default databases are created: template0 and template1. Template0 is the pristine default database from which template1 is created. All other databases are created by duplicating template1 unless another database is specified in the createdb statement. You can access template1 but you cannot access template0.

When you have a common set of objects, such as languages, functions, operators, etc., you can create those objects in template1. All databases created after doing so will be created with those things in place. For example, doing createlang 'plperl' template1 will install plperl in template1. Then all databases created afterwards will have plperl available.

Beware of pilot errors. If you inadvertently load database specific functions or other schema information into template1 they will also be created in each new database.

There was a case when someone dropped and recreated a database and tried to reload the schema. There were many errors regarding duplicate objects. It turned out that, by mistake, the schema had first been loaded into template1. The newly created database already had the objects created and this caused the error messages.

Annotated postgresql.conf and Global User Configuration (GUC) Guide

posted Sep 18, 2010, 6:31 AM by Sachchida Ojha   [ updated Jul 28, 2012, 6:05 AM ]


Steps to install PostgreSQL

posted Sep 18, 2010, 6:17 AM by Sachchida Ojha   [ updated Sep 18, 2010, 6:23 AM ]

gunzip postgresql-version.tar.gz
tar xvf postgresql-version.tar
./configure --prefix =       // default path is /usr/local/pgsql
gmake                                           
su
gmake install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
 
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/postmaster -D /usr/local/pgsql/data >logfile 2>&1 &
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test

 Creating the PostgreSQL User

 It is recommended to create a separate user to own the PostgreSQL files and processes that will be installed.The user name is typically postgres. By default, POSTGRESQL allows database access only to userslogged into the computer running the database server.

To enable network access, the postmaster must be started with the -i flag. 
Configuration 
Many distributions use a configure command that allows users to choose 
various options before compiling and installing the software. 
Compilation 
PostgreSQL is usually distributed in source code format. 
As a consequence, C source code must be compiled into a format that is 
understood by the CPU. This process is  handled by cc or gcc compiler. 
Installation 
When PostgreSQL is installed, it creates files in its home directory, 
typically /usr/local/pgsql. This directory holds all the files needed 
by PostgreSQL in various subdirectories: 

/bin

 PostgreSQL command-line programs, such as psql. 
 /data 
 Configuration files and tables shared by all databases.  
 /data/base
 A subdirectory for each database. 
Using the du and ls commands, administrators can display the 
amount of disk space used by each database, table, or index. 
 /doc 
 PostgreSQL documentation. 
 /include 
 Include files  used by various programming languages. 
 /lib 
 Libraries used by various programming languages. 
This subdirectory also contains files used during initialization 
and sample configuration files that can be copied to /data and modified. 
 /man 
 PostgreSQL manual pages.
 Initialization 
Initialization creates a database called template1 in the PostgreSQL 
home directory. This database is used to create all other databases. 
Initdb  performs this initialization step: 
 
                $PGSQL_DIR/bin/initdb -D $PGDATA
 
     The "-D" option specifies the location where the data will be stored.
     Just make sure that the server account can write to the directory 
     (or create it, if it doesn't already exist) before starting "initdb".
Starting the Server 
 Once template1  is created, the database server can be started. 
This step typically involves running the program called postmaster. 
 
To start up the database server: 
 
        a). $PGSQL_DIR/bin/pg_ctl start -l logfile -D $PGDATA
 
        b). in the foreground: 
            $PGSQL_DIR/bin/postmaster -D $PGDATA
 
        c). in the background use:
            $PGSQL_DIR/bin/postmaster -D $PGDATA >logfile 2>&1 &
To stop a server:
 
     $PGSQL_DIR/bin/pg_ctl stop -l logfile -D $PGDATA
 Creating a Database 
 Once the database server is running, you can create databases by running 
createdb from the operating system prompt:  
 
        $PGSQL_DIR/bin/createdb testdb
 
        Initially, only the POSTGRESQL superuser can create new databases. 
        Other users can be given permission to create new databases. 
 
        The createdb program creates a new database by making a copy of the template1 db. 
        This database is created when POSTGRESQL is first initialized. Any modifications to 
        template1 will appear in subsequently created databases. 
 
 
To see a list avalable databases use following command:
 
                $PGSQL_DIR/bin/pg_ctl start -l 
 
To connect to database testdb enter:
                $PGSQL_DIR/bin/psql testdb
 
At the prompt you can enter SQL commands and start working with PostgreSql.
 
To switch to amother database: 
                testdb# \connect newdb;
 PostgreSQL download
 
 Windows
 
 http://www.postgresql.org/ftp/binary/v8.1.4/win32/
 
 Unix
 
 http://www.postgresql.org/ftp/source/v8.1.4/
 
 PGadmin3
 
 http://www.postgresql.org/ftp/pgadmin3/
 
 JDBC DRIVER
 
 http://jdbc.postgresql.org/download.html
 http://www.postgresql.org/download/

Creating, updating, deleting user and role in PostgreSQL :

posted Sep 17, 2010, 3:23 PM by Sachchida Ojha



As in most database systems, users and groups handle an important role within PostgreSQL. Used correctly, users and groups can allow for fine-grained, versatile access control to your database objects

PostgreSQL stores both user and group data within its own system catalogs. These are different from the users and groups defined within the operating system on which the software is installed. Any connection to PostgreSQL must be made with a specific user, and any user may belong to one or more defined groups.

Users control the allocation of rights and track who is allowed to perform actions on the system (and which actions they may perform). Groups exist as a means to simplify the allocation of these rights. Both users and groups exist as global database objects, which means they are not tied to any particular database

Managing User

In order to establish a connection to PostgreSQL, you must supply a basic form of identification. This is called a username, as it identifies the user who the system will recognize as connected to a database. Users within PostgreSQL are not necessarily related to users of the operating system (which are sometimes called system accounts), though you may choose to name your PostgreSQL users after the system accounts that will be accessing them.

Each user has an internal system ID to PostgreSQL (called a sysid), as well as a password, though the password is not necessarily required to connect (depending on the configuration of the pg_hba.conf ; The user's system ID is used to associate objects in a database with their owner (the user who is allowed to grant and revoke rights on an object).

As well as being used to associate database objects with their owner, users may also have global rights assigned to them when they are created. These rights determine whether or not a user is allowed to create and destroy databases, and whether or not the user is a superuser (a user who is allowed all rights, in all databases, including the right to create users). The assignment of these rights may be
modified at any time by an existing superuser.

PostgreSQL installs a single superuser by default named postgres. All other users must be added by this user, or by another subsequently added superuser.

Viewing Users

All user information is stored in a PostgreSQL system table called pg_shadow, shown in Table 10-1.
This table is only selectable by superusers, though a limited view of this table called pg_user is accessible to normal users.
Table 10-1. The pg_shadow table

Column
Type usename
name usesysid
integer
usecreatedb
boolean
usetrace
boolean
usesuper
boolean
usecatupd
boolean
passwd
text
valuntil
abstime

The primary difference between the selectable data in pg_user and pg_shadow is that the actual value of the passwd column is not shown (it is replaced with a string of asterisks). This is a security measure to ensure that normal users are not able to determine one another's passwords.

The usename column stores the name of the system user, which is a unique character string (no two users may have the same name, as users are global database objects). Similarly, the usesysid column stores a unique integer value associated with the user. The usecreatedb and usesuper each correspond to the pair of privileges which can be set upon creation of a user, as documented in the Section called Creating Users."

Creating Users

PostgreSQL provides two methods by which database users may be created. Each requires authentication as a superuser, for only superusers can create new users.

The first method is through the use of the SQL command CREATE USER, which may be executed by any properly authenticated PostgreSQL client (e.g., psql ). The second is a command-line wrapper called createuser, which may be more convenient for a system administrator, as it can be executed in a single command without the need to interact with a PostgreSQL client.

The following sections document each of these methods.Creating a user with the CREATE USER SQL command


The CREATE USER command requires only one parameter: the name of the new user. There are also a
variety of options that may be set, including a password, explicit system ID, group, and a set of rights that may be explicitly allocated.
Here is the complete syntax for CREATE USER: CREATE USER username [ WITH [ SYSID uid ]
[ PASSWORD 'password' ] ] [ CREATEDB | NOCREATEDB ]
[ CREATEUSER | NOCREATEUSER ] [ IN GROUP groupname [, ...] ]
[ VALID UNTIL 'abstime' ]

In this syntax, username is the name of the new user to be created. You cannot have two users with the same name. By specifying the WITH keyword, either or both of the SYSID and PASSWORD keywords may be applied.

Every other optional keyword may follow in the order displayed (not requiring the use of the WITH keyword). The following is a detailed explanation of each optional keyword and its meaning:

SYSID uid Specifies that the system ID is to be set to the value of uid. If omitted, a reasonable, unique numeric default is chosen. PASSWORD 'password' Sets the new user's password to password. If unspecified, the password defaults to NULL. CREATEDB | NOCREATEDB

Specifying the CREATEDB keyword grants the new user the right to create new databases, as well as the right to destroy databases which they own. Specifying NOCREATEDB explicitly enforces
the default, which is the lack of this right. CREATEUSER | NOCREATEUSER

Grants the right to create new users, which implicitly creates a superuser. Notice that a user with the rights to create other users will therefore have all rights, in all databases (including the rights to create a database, even if NOCREATEDB was specified). NOCREATEUSER explicitly enforces the default, which is the lack of this right. IN GROUP groupname [, ...]

Adds the new user to the group named groupname. Multiple group names may be specified by separating them with commas. The group(s) must exist in order for the statement to succeed. VALID UNTIL 'abstime'

Sets the user's password to expire at abstime, which must be of a recognizable timestamp format. After that date, the password must be reset, and the expiration moved forward. VALID UNTIL 'infinity'

Sets the user's password to be valid indefinitely.

By not specifying either CREATEDB or CREATEUSER, users are implicitly "normal" with no special rights. They may not create databases or other users, nor may they destroy databases or users.
Such users may connect to databases in PostgreSQL, but they can only perform the statements which they have been granted access to (see the Section called Granting Privileges" for more on granting rights).


Example 10-1 creates a normal user named salesuser. It also sets a password of N0rm4! by the use of the WITH PASSWORD clause. By omitting the VALID UNTIL clause, this password will never expires.

Example 10-1. Creating a normal usertemplate1=# CREATE USER salesuser template1-# WITH PASSWORD 'N0rm4!'; CREATE USER

The CREATE USER server message returned in Example 10-1 indicates that the user was added successfully.

Other messages you may receive from this command are as follows:
ERROR: CREATE USER: permission denied

This message is returned if the user issuing the CREATE USER command is not a superuser. Only superusers may create new users. ERROR: CREATE USER: user name "salesuser" already exists

This message indicates that a user with the name salesuser already exists. If you wish to create a user who has the ability to create databases within PostgreSQL but not create or destroy PostgreSQL users, you may specify the CREATEDB keyword rather than CREATEUSER. This allows the named user to arbitrarily create databases, as well as drop any databases which they own. See Chapter 9, for more on this the topic of creating and destroying databases.


Example 10-2 illustrates the creation of a user named dbuser who has the right to create new databases. This is achieved by specifying the CREATEDB keyword after the username. Notice also the use of the WITH PASSWORD and VALID UNTIL keywords. These set the password for dbuser to DbuS3r, which will be valid until

November 11th, 2002.

Example 10-2. Creating a user with CREATEDB rightstemplate1=# CREATE USER dbuser CREATEDB template1-# WITH PASSWORD 'DbuS3r' template1-# VALID UNTIL '2002-11-11'; CREATE USER

Resetting an expired user's password does not modify the VALID UNTIL value. In order to re-active a user's access whose password has expired, both the WITH PASSWORD and VALID UNTIL keywords must be provided to the ALTER USER command. See the Section called Altering Users" for more on this command.You may wish to create an alternate superuser from the postgres user, though caution should be exercised in creating superusers. These users are granted every right within PostgreSQL, including creating users, removing users, and destroying databases. Example 10-3 demonstrates the creation of a PostgreSQL superuser named manager from the psql prompt.

Example 10-3. Creating a superusertemplate1=# CREATE USER manager CREATEUSER; CREATE USER
Creating a user with the createuser script


The createuser script is executed directly from the command line, and can operate in one of two ways. If issued without any arguments, it will interactively prompt you for the username and each of the rights, and attempt to make a local connection to PostgreSQL. Alternatively, you may choose to specify the options and the username to be created on the command line.As with other command-line applications for PostgreSQL, arguments may be supplied either in their short form (with a single dash, and character), or in their long form (with two dashes, and the full name of the argument).

Here is the syntax for createuser : createuser [ options ] [ username ]

The username in the syntax represents the name of the user you wish to create. Replace options with one or more of the following flags:
-d, - -createdb
Equivalent to the CREATEDB keyword of the CREATE USER SQL command. Allows the new user to create databases. -D, - -no-createdb
Equivalent to the NOCREATEDB keyword of the CREATE USER SQL command.
Explicitly indicates that the new user may not create databases. This is the default. -a, - -adduser

Equivalent to the CREATEUSER keyword of the CREATE USER SQL command. Allows the new user to create users, and raises the status of the user to a superuser (enabling all rights within PostgreSQL). -A, - -no-adduser
Equivalent to the NOCREATEUSER keyword of the CREATE USER SQL command.
Explicitly indicates that the new user is not a superuser. This is the default. -i SYSID, - -sysid=SYSID

Sets the new users system ID to SYSID. -P, - -pwprompt

Results in a password prompt allowing you to set the password of the new user username. -h HOSTNAME, - -host=HOSTNAME
Specifies that HOSTNAME will be connected to, rather than the localhost,
or the host defined by the PGHOST environment variable. -p PORT, - -port=PORT
Specifies that the database connection will be made on port PORT, rather than the default port (usually 5432). -U USERNAME, - -username=USERNAME

Specifies that USERNAME will be the user who connects to PostgreSQL
(The default is to connect using the name of the system user executing the createuser script). -W, - -password
Results in a password prompt for the connecting user, which happens
automatically if the pg_hba.conf file is configured not to trust the requesting host. -e, - -echo
Causes the CREATE USER command sent to PostgreSQL to be displayed to
the screen as it is executed by createuser. -q, - -quiet
Prevents output from being sent to stdout (though errors will still be sent to stderr).

If any of the -d, -D, -a, -A, or username arguments are omitted, createuser will prompt
you for each missing argument. This is because PostgreSQL will not make any assumptions
about the rights intended for the new user, nor about the new user's name.

Example 10-4 creates a user named newuser, who has neither the right to create a database, nor create users.

Example 10-4. Creating a user with createuser$ createuser -U manager -D -A newuser CREATE USER

Notice also the -U manager flag passed to the createuser script. This indicates that
the user with which to connect to PostgreSQL is manager, not jworsley as the script
would otherwise assume, based on the name of the system account invoking the script.

If you prefer to be interactively prompted for each setting, (instead of having to remember
the meaning of each flag or check the reference each time) you may simply omit the flags
you are uncertain of. The createuser script will then prompt you for the basic createuser options.
These options include the PostgreSQL username, whether the user may create databases,
and whether or not the user may add new users to PostgreSQL.



Example 10-5 demonstrates using the createuser script in interactive mode.

The net effect of this example is the same as the single line executed in Example 10-4.

Example 10-5. Interactively creating a user with createuser$ createuser Enter name of user to add: newuser Shall the new user
be allowed to create databases? (y/n) n Shall the new user be
allowed to create more new users? (y/n) n CREATE USER

Altering Users
Existing users may only be modified by PostgreSQL superusers. Possible modifications include each of the options available at the creation of the user (e.g., password, password
expiration date, global rights), except for the system ID of an existing user, which may not be modified. Modification of existing users is achieved through the use of the ALTER USER SQL statement.

Here is the syntax for ALTER USER: ALTER USER username [ WITH PASSWORD 'password' ]
[ CREATEDB | NOCREATEDB ]
[ CREATEUSER | NOCREATEUSER ] [ VALID UNTIL 'abstime' ]

The required username argument specifies which user is to be modified. Any of the following parameters may additionally be specified:
WITH PASSWORD 'password'
Sets username's password to password. CREATEDB | NOCREATEDB
Grants or revokes from username the right to create databases. CREATEUSER | NOCREATEUSER
Grants or revokes from username the status of superuser, which enables all possible right within PostgreSQL (most notably the ability to create and destroy users and superusers). VALID UNTIL 'abstime'

Sets username's password to expire at abstime, which must be of some valid timestamp format.
This value is only relevant for systems requiring password authentication, and is otherwise ignored
(e.g., for trusted sites).
A common function of ALTER USER is to reset the password (and potentially the expiration date) of a user.
If a PostgreSQL user had an expiration date set when their user was originally added, and that date has
passed, and the user requires password-based authentication, a superuser will have to reset both the password
and the expiration date to re-activate a user's ability to connect. If you want to cause a user's password to
never expire, set it to the special timestamp infinity.

Example 10-6 modifies a user named salesuser. The user's password is set to n3Wp4s4 by the WITH PASSWORD
clause, and set to expire on January 1st, 2003 by the VALID UNTIL clause.

Example 10-6. Resetting a passwordtemplate1=# ALTER USER salesuser template1-#
WITH PASSWORD 'n3WP4s4' template1-#
VALID UNTIL '2003-01-01'; ALTER USER

At times you may wish to grant a user additional rights beyond those originally granted to them.
The use of the CREATEUSER keyword in Example 10-7 modifies the user salesuser to have all rights
in PostgreSQL, making the user into a superuser. Note that this makes the CREATEDB right moot,
as superusers can create databases implicitly.

Example 10-7. Adding superuser rightstemplate1=# ALTER USER salesuser template1-# CREATEUSER; ALTER USER

Conversely, there may be times when a user no longer deserves rights that have been granted in the past.
These rights may be just as easily removed by a superuser with the NOCREATEDB and NOCREATEUSER keywords.

Example 10-8. Removing superuser rightstemplate1=# ALTER USER salesuser template1-# NOCREATEDB NOCREATEUSER; ALTER USER

Removing Users

PostgreSQL users may at any time be removed from the system by authenticated superusers.
The only restriction is that a user may not be removed if any databases exist which are owned by that user.
If a user owns a database, that database must be dropped before the user can be removed from the system.
As with the creation of PostgreSQL users, there are two methods by which users may be removed.
These are the DROP USER SQL command, and the dropuser command-line executable.
Removing users with the DROP USER SQL command
A superuser may remove a user by issuing the DROP USER command from a valid PostgreSQL client.
The psql program is most commonly used to achieve this task.



Here is the syntax for DROP USER: DROP USER username

In this syntax, username is the name of the user that you intend to permanently remove from the system.
Example 10-9 shows the use of the psql client to connect to PostgreSQL as the manager user in order
to remove the salesuser database user.

Example 10-9. Removing a user with DROP USER$ psql -U manager template1 Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit template1=# DROP USER salesuser; DROP USER

The DROP USER server message indicates that the user was successfully removed from the system. Other messages that you might receive from this command include:

ERROR: DROP USER: permission denied Indicates that the user initiating the command does not have the right to drop a user. Only superusers may drop existing database users. ERROR: DROP USER: user "salesuser" does not exist Indicates that there is no such user with the name salesuser.

Removing users with the dropuser operating system command
The dropuser command operates much like the createuser script. It offers the same connection options, ensuring that it can be used remotely as well as locally, and requires only the username of the user to be removed from the system.
Here is the syntax for dropuser: dropuser [ options ] [ username ]

Each of the connectivity options is identical to those for createuser, described in the Section called Creating a user with the createuser script," earlier in this chapter. Example 10-10 demonstrates the same net effect as the SQL statement in Example 10-9 by connecting to the PostgreSQL backend as the manager user, and removing the user named salesuser.

Example 10-10. Removing a user with dropuser$ dropuser -U manager salesuser DROP USER

The output from dropuser is the same as the output for the SQL DROP USER command. If you omit the username that you wish to remove when you execute the script dropuser, you will be prompted interactively for the name of that user to be removed from the system.

Misc PostgreSQL Commands and scripts

posted Sep 17, 2010, 3:21 PM by Sachchida Ojha   [ updated Sep 17, 2010, 3:23 PM ]

GRANT ALL PRIV TO A ROLE
 
 ###### start of grantall.sh #######
 
 #!/bin/ash
  SQL="SELECT relname FROM pg_class WHERE (relkind = 'r' OR relkind = 'S')
  AND relname !~ '^pg_' ORDER BY relname"
  OBJ=`psql -t -c "${SQL}" $1`
  # OBJ=`echo ${OBJ} | sed 's/EOF//g'`
  OBJ=`echo ${OBJ} | sed 's/ /, /g'`
 # SQL="REVOKE ALL ON ${OBJ} FROM role1"
  SQL="GRANT ALL ON ${OBJ} TO role1"
  echo ${SQL}
  psql -c "${SQL}" $1
 
 
 
 ###### end of grantall.sh #######
 -- test.sql
 --
 -- Outputs connected ip address to a CSV file that
 -- is easily imported into spreadsheet software.
 \o report.csv
 \a
 \f ,
 select connected_ip_address from asset
 where os_type like :ostype;
 \o
 
 -- test.sh
 #!/bin/sh
 # test.sh
 #
 # Wrapper script for test.sql
 echo "Please enter OS Type:"
 read ostype
 psql --set ostype=\'$ostype\' -f test.sql em_db emsowner
 
 -- test.sh
 #!/bin/sh
 # test.sh
 #
 # Wrapper script for test.sql
 echo "Please enter OS Type:"
 read ostype
 psql --set ostype=\'$1\' -f test.sql em_db emsowner
 
 List tables:
 
 SELECT relname FROM pg_class WHERE relname !~ '^(pg_|sql_)' AND relkind = 'r';
 
 -- using INFORMATION_SCHEMA:
 
 SELECT table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema');
 
 List Views:
 SELECT viewname FROM pg_views WHERE schemaname NOT IN ('pg_catalog', 'information_schema') AND viewname !~ '^pg_';
 
 -- using INFORMATION_SCHEMA:
 
 SELECT table_name FROM information_schema.tables WHERE table_type = 'VIEW' AND table_schema NOT IN ('pg_catalog', 'information_schema') AND table_name !~ '^pg_';
 
 List users:
 SELECT usename FROM pg_user;
 List Indexes:
 SELECT relname FROM pg_class WHERE oid IN ( SELECT indexrelid FROM pg_index, pg_class WHERE pg_class.relname='test2' AND pg_class.oid=pg_index.indrelid AND indisunique != 't' AND indisprimary != 't' );
 
 List CONSTRAINTs:
 SELECT relname FROM pg_class WHERE oid IN ( SELECT indexrelid FROM pg_index, pg_class WHERE pg_class.relname='test' AND pg_class.oid=pg_index.indrelid AND ( indisunique = 't' OR indisprimary = 't' ) );
 
 -- with INFORMATION_SCHEMA:
 
 SELECT constraint_name FROM information_schema.table_constraints WHERE table_name = 'test';
 List table fields:
 SELECT a.attname FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = 'test' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid
 
 -- with INFORMATION_SCHEMA:
 
 SELECT column_name FROM information_schema.columns WHERE table_name = 'test';
 
 SELECT a.attnum AS ordinal_position, a.attname AS column_name, t.typname AS data_type, a.attlen AS character_maximum_length, a.atttypmod AS modifier, a.attnotnull AS notnull, a.atthasdef AS hasdefault FROM pg_class c, pg_attribute a, pg_type t WHERE c.relname = 'test' AND a.attnum > 0 AND a.attrelid = c.oid AND a.atttypid = t.oid ORDER BY a.attnum;
 
 -- with INFORMATION_SCHEMA:
 
 SELECT ordinal_position, column_name, data_type, column_default, is_nullable, character_maximum_length, numeric_precision FROM information_schema.columns WHERE table_name = 'test' ORDER BY ordinal_position;
 
 List sequences:
 SELECT relname FROM pg_class WHERE relkind = 'S' AND relnamespace IN ( SELECT oid FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema' );
 
 List TRIGGERs:
 SELECT trg.tgname AS trigger_name FROM pg_trigger trg, pg_class tbl WHERE trg.tgrelid = tbl.oid AND tbl.relname = 'newtable';
 
 -- with INFORMATION_SCHEMA:
 
 SELECT DISTINCT trigger_name FROM information_schema.triggers WHERE event_object_table = 'newtable' AND trigger_schema NOT IN ('pg_catalog', 'information_schema');
 
 SELECT trg.tgname AS trigger_name FROM pg_trigger trg, pg_class tbl WHERE trg.tgrelid = tbl.oid AND tbl.relname = 'newtable';
 
 -- with INFORMATION_SCHEMA:
 
 SELECT DISTINCT trigger_name FROM information_schema.triggers WHERE event_object_table = 'newtable' AND trigger_schema NOT IN ('pg_catalog', 'information_schema');
 
 SELECT trg.tgname AS trigger_name, tbl.relname AS table_name, p.proname AS function_name, CASE trg.tgtype & cast(2 as int2) WHEN 0 THEN 'AFTER' ELSE 'BEFORE' END AS trigger_type, CASE trg.tgtype & cast(28 as int2) WHEN 16 THEN 'UPDATE' WHEN 8 THEN 'DELETE' WHEN 4 THEN 'INSERT' WHEN 20 THEN 'INSERT, UPDATE' WHEN 28 THEN 'INSERT, UPDATE, DELETE' WHEN 24 THEN 'UPDATE, DELETE' WHEN 12 THEN 'INSERT, DELETE' END AS trigger_event FROM pg_trigger trg, pg_class tbl, pg_proc p WHERE trg.tgrelid = tbl.oid AND trg.tgfoid = p.oid AND tbl.relname !~ '^pg_';
 
 -- with INFORMATION_SCHEMA:
 
 SELECT * FROM information_schema.triggers WHERE trigger_schema NOT IN ('pg_catalog', 'information_schema');
 
 List FUNCTIONs:
 SELECT proname FROM pg_proc pr, pg_type tp WHERE tp.oid = pr.prorettype AND pr.proisagg = FALSE AND tp.typname <> 'trigger' AND pr.pronamespace IN ( SELECT oid FROM pg_namespace WHERE nspname NOT LIKE 'pg_%' AND nspname != 'information_schema' );
 
 -- with INFORMATION_SCHEMA:
 
SELECT routine_name FROM information_schema.routines WHERE specific_schema NOT IN ('pg_catalog', 'information_schema') AND type_udt_name != 'trigger';

INSTR Function in PostgreSQL

posted Sep 17, 2010, 3:18 PM by Sachchida Ojha

CREATE FUNCTION instr(varchar, varchar) RETURNS integer AS $$
DECLARE
    pos integer;
BEGIN
    pos:= instr($1, $2, 1);
    RETURN pos;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

CREATE FUNCTION instr(string varchar, string_to_search varchar, beg_index integer)
RETURNS integer AS $$
DECLARE
    pos integer NOT NULL DEFAULT 0;
    temp_str varchar;
    beg integer;
    length integer;
    ss_length integer;
BEGIN
    IF beg_index > 0 THEN
        temp_str := substring(string FROM beg_index);
        pos := position(string_to_search IN temp_str);
        IF pos = 0 THEN
            RETURN 0;
        ELSE
            RETURN pos beg_index - 1;
        END IF;
    ELSE
        ss_length := char_length(string_to_search);
        length := char_length(string);
        beg := length beg_index - ss_length 2;
        WHILE beg > 0 LOOP
            temp_str := substring(string FROM beg FOR ss_length);
            pos := position(string_to_search IN temp_str);
            IF pos > 0 THEN
                RETURN beg;
            END IF;
            beg := beg - 1;
        END LOOP;
        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

CREATE FUNCTION instr(string varchar, string_to_search varchar,
                      beg_index integer, occur_index integer)
RETURNS integer AS $$
DECLARE
    pos integer NOT NULL DEFAULT 0;
    occur_number integer NOT NULL DEFAULT 0;
    temp_str varchar;
    beg integer;
    i integer;
    length integer;
    ss_length integer;
BEGIN
    IF beg_index > 0 THEN
        beg := beg_index;
        temp_str := substring(string FROM beg_index);
        FOR i IN 1..occur_index LOOP
            pos := position(string_to_search IN temp_str);
            IF i = 1 THEN
                beg := beg pos - 1;
            ELSE
                beg := beg pos;
            END IF;
            temp_str := substring(string FROM beg 1);
        END LOOP;
        IF pos = 0 THEN
            RETURN 0;
        ELSE
            RETURN beg;
        END IF;
    ELSE
        ss_length := char_length(string_to_search);
        length := char_length(string);
        beg := length beg_index - ss_length 2;
        WHILE beg > 0 LOOP
            temp_str := substring(string FROM beg FOR ss_length);
            pos := position(string_to_search IN temp_str);
            IF pos > 0 THEN
                occur_number := occur_number 1;
                IF occur_number = occur_index THEN
                    RETURN beg;
                END IF;
            END IF;
            beg := beg - 1;
        END LOOP;
        RETURN 0;
    END IF;
END;
$$ LANGUAGE plpgsql STRICT IMMUTABLE;

FTP BACKUP - backup your postgresql database and FTP to another server

posted Sep 17, 2010, 3:17 PM by Sachchida Ojha

#!/bin/bash
FTP_SRVR=10.0.101.103 #or ip
FTP_USR=guest
FTP_PWD=guest
currdir="`pwd`";
database="testdb"
BackupFileName="emdb"
echo This script will create a database backup file and transfers file to user defined FTP location . Continue [Y/N] ?
read -t 30 YN
YN=${YN:-"Y"};
YN=${YN%%[^YyNn]*};
if ( test -z "$YN" )
then
 echo -e "Please enter either \"Y\" or \"N\" ";
eval "$0" "$@";
exit;
fi
if ( test "$YN" = "N" -o "$YN" = "n" )
then
 exit;
fi

today=`date %y%m%d%H%M%S`
rm -Rf *.dump
echo backing up em_db database. Please Wait......
pg_dump -U postgres testdb>${BackupFileName}-$today.dump
echo Done!

 

echo File FTP in progress...........
ftp -vin FTP_SRVR<<!
user $FTP_USR $FTP_PWD
binary
put *.dump
bye
EOF

1-10 of 20