Interview Q and A for PostgreSQL Part - 2

23. How to create a PostgreSQL Database?

There are two metods in which you can create two databases.

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

# CREATE DATABASE mydb WITH OWNER ramesh;

CREATE DATABASE

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

$ /usr/local/pgsql/bin/createdb mydb -O ramesh

CREATE DATABASE

* -O owner name is the option in the command line.

PostgreSQL starts with 3 databases, namely, template0, template1, and postgres. The main user database is postgres.

When you create another database it actually takes a copy of an existing database. Once created, there is no further link between the two databases.

Template0 and template1 are known as template databases. Template1 can be changed to allow you to create a localized template for any new databases that you create. Template0 exists so that when you alter Template1 you still have a pristine copy on which to fall back.

You can drop the database named postgres. But don't, OK? Similarly, don't touch template0. Template1 exists to be modified, so feel free to change that.

24. How do I get a list of databases in a Postgresql database ?

# \l  [Note: This is backslash followed by lower-case L]

List of databases

Name | Owner | Encoding

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

backup | postgres | UTF8

mydb | ramesh | UTF8

postgres | postgres | UTF8

template0 | postgres | UTF8

template1 | postgres | UTF8

#psql –l

postgres=# \x

postgres=# select datname from pg_database;

-[ RECORD 1 ]-+------------------------------

datname | template1               

\x command. That makes the output in psql appear as one column per line, rather than one row per line.

25. How to Delete/Drop an existing PostgreSQL database ?

# \l

List of databases

Name | Owner | Encoding

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

backup | postgres | UTF8

mydb | ramesh | UTF8

postgres | postgres | UTF8

template0 | postgres | UTF8

template1 | postgres | UTF8

# DROP DATABASE mydb;

DROP DATABASE

 

26. Getting help on postgreSQL commands

\? will show PSQL command prompt help. \h CREATE will shows help about all the commands that starts with CREATE, when you want something specific such as help for creating index, then you need to give CREATE INDEX.

# \?

# \h CREATE

# \h CREATE INDEX

 

27. How do I get a list of all the tables in a Postgresql database?

# \d

On an empty database, you’ll get “No relations found.” message for the above command.

 

28. How to turn on timing, and checking how much time a query takes to execute ?

# \timing — After this if you execute a query it will show how much time it took for doing it.

# \timing

Timing is on.

# SELECT * from pg_catalog.pg_attribute ;

Time: 9.583 ms

 

29. How To Backup and Restore PostgreSQL Database and Table?

We discussed earlier how to backup and restore postgres database and tables using pg_dump and psql utility.

 

30. How to see the list of available functions in PostgreSQL ?

To get to know more about the functions, say \df+

# \df

# \df+

 

31. How to edit PostgreSQL queries in your favorite editor ?

# \e

\e will open the editor, where you can edit the queries and save it. By doing so the query will get executed.

 

31. Where can i find the postgreSQL history file ?

Similar to the Linux ~/.bash_history file, postgreSQL stores all the sql command that was executed in a history filed called ~/.psql_history as shown below.

$ cat ~/.psql_history

alter user postgres with password 'tmppassword';

\h alter user

select version();

create user ramesh with password 'tmppassword';

\timing

select * from pg_catalog.pg_attribute;

 

31.  Save Psql Command History

Use \s <filename> to save the psql command history to a file.  If no file is listed, the psql command history will be sent to standard output:

mary=> \s psql-history.sql

 

32.  Select Current TimeStamp from Postgresql:

uptimemadeeasy=> select current_time; timetz ——————– 02:34:20.582741+00 (1 row)

33.  Select the current database that you are using:

uptimemadeeasy=> select current_database(); current_database —————— uptimemadeeasy (1 row)

34.  Show the User You Are Logged in as:

uptimemadeeasy.com=# select current_user; current_user ————– postgres (1 row)

 

35.  Show the IP Address you used to connect to the Postgresql Database

uptimemadeeasy=> select inet_server_addr(); inet_server_addr —————— 10.10.10.16 (1 row)

 

36.  Show the IP Port That the Postgresql Database is Listening On

uptimemadeeasy=> select inet_server_port(); inet_server_port —————— 5432 (1 row)

37.  Select When Postgresql was Last Started

uptimemadeeasy=> select pg_postmaster_start_time(); pg_postmaster_start_time ——————————- 2013-11-27 17:42:43.390377+00 (1 row)

 

38.  Select How Long Postgresql Has Been Up

uptimemadeeasy=> select date_trunc(‘minute’, current_timestamp – pg_postmaster_start_time()) as “postgresql uptime”; postgresql uptime ——————- 7 days 08:59:00 (1 row) SELECT date_trunc('second', current_timestamp - pg_postmaster_start_time()) as uptime;

 

39.  Change database

Use \c <database_name> to change database names:

mary=> \c marydb You are now connected to database “marydb” as user “mary”.

 

40.  List permissions

Use \z to list permissions for the logged in user:

mary=> \z Access privileges Schema | Name | Type | Access privileges | Column access privileges ——–+——+——+——————-+————————– (0 rows)mary=>

 

41.  exit psql

Use \q to exit psql:

mary=> \q [mary@www.uptimemadeeasy.com ~]$

42. What does "INSERT 0 1" mean?

> "1" stands for the number of the records added to the table, as far as I

> understood, but what about the "0"?

It stands for the OID of the row that was inserted, if the table was created to use them (CREATE TABLE .... WITH (OIDS=TRUE)); newer versions of PostgreSQL by default have tables created without OIDs on the rows, so you just get a 0 returned instead.

43. Who is particularly vulnerable because of this issue?

Any system that allows unrestricted access to the PostgreSQL network port, such as users running PostgreSQL on a public cloud, is especially vulnerable. Users whose servers are only accessible on protected internal networks, or who have effective firewalling or other network access restrictions, are less vulnerable.

This is a good general rule for database security: do not allow port access to the database server from untrusted networks unless it is absolutely necessary. This is as true, or more true, of other database systems as it is of PostgreSQL.

44. What is the nature of the vulnerability?

The vulnerability allows users to use a command-line switch for a PostgreSQL connection intended for single-user recovery mode while PostgreSQL is running in normal, multiuser mode. This can be used to harm the server.

45. What potential exploits are enabled by this vulnerability?

1. Persistent Denial of Service: an unauthenticated attacker may use this vulnerability to cause PostgreSQL error messages to be appended to targeted files in the PostgreSQL data directory on the server. Files corrupted in this way may cause the database server to crash, and to refuse to restart. The database server can be fixed either by editing the files and removing the garbage text, or restoring from backup.
2. Configuration Setting Privilege Escalation: in the event that an attacker has a legitimate login on the database server, and the server is configured such that this user name and the database name are identical (e.g. user web, database web), then this vulnerability may be used to temporarily set one configuration variable with the privileges of the superuser.
3. Arbitrary Code Execution: if the attacker meets all of the qualifications under 2 above, and has the ability to save files to the filesystem as well (even to the tmp directory), then they can use the vulnerability to load and execute arbitrary C code. SELinux will prevent this specific type of exploit.

 

46. How can users protect themselves?

• Download the update release and update all of your servers as soon as possible.
• Ensure that PostgreSQL is not open to connections from untrusted networks.
• Audit your database users to be certain that all logins require proper credentials, and that the only logins which exist are legitimate and in current use.

Use of advanced security frameworks, such as SELinux with PostgreSQL's SEPostgres extension, also lessen or eliminate the exposure and potential damage from PostgreSQL security vulnerabilities.

47. What is PostgreSQL? How is it pronounced? What is Postgres?

PostgreSQL is a powerful, open source relational database system. It has more than 20 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness. It is fully ACID compliant, has full support for foreign keys, joins, views, triggers, and stored procedures (in multiple languages).

An enterprise class database, PostgreSQL boasts sophisticated features such as Multi-Version Concurrency Control (MVCC), point in time recovery (PITR), tablespaces, asynchronous replication, nested transactions (savepoints), online/hot backups, a sophisticated query planner/optimizer, and write ahead logging for fault tolerance. It supports international character sets, multi-byte character encodings, Unicode, and it is locale-aware for sorting, case-sensitivity, and formatting. It is highly scalable both in the sheer quantity of data it can manage and in the number of concurrent users it can accommodate. There are active PostgreSQL systems in production environments that manage in excess of 4 terabytes of data.

PostgreSQL is pronounced Post-Gres-Q-L. PostgreSQL is an object-relational database system that has the features of traditional proprietary database systems with enhancements to be found in next-generation DBMS systems. PostgreSQL is free and the complete source code is available.

PostgreSQL development is performed by a team of mostly volunteer developers spread throughout the world and communicating via the Internet. It is a community project and is not controlled by any company. Postgres is a widely-used nickname for PostgreSQL. It was the original name of the project at Berkeley and is strongly preferred over other nicknames. If you find 'PostgreSQL' hard to pronounce, call it 'Postgres' instead.

48. What is the license of PostgreSQL?

PostgreSQL is distributed under a license similar to BSD and MIT. Basically, it allows users to do anything they want with the code, including reselling binaries without the source code. The only restriction is that you not hold us legally liable for problems with the software. There is also the requirement that this copyright appear in all copies of the software. Here is the license we use:

PostgreSQL Database Management System

(formerly known as Postgres, then as Postgres95)

Portions Copyright (c) 1996-2011, PostgreSQL Global Development Group

Portions Copyright (c) 1994, The Regents of the University of California

Permission to use, copy, modify, and distribute this software and its

documentation for any purpose, without fee, and without a written agreement

is hereby granted, provided that the above copyright notice and this

paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE UNIVERSITY OF CALIFORNIA BE LIABLE TO ANY PARTY FOR

DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING

LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS

DOCUMENTATION, EVEN IF THE UNIVERSITY OF CALIFORNIA HAS BEEN ADVISED OF THE

POSSIBILITY OF SUCH DAMAGE.

THE UNIVERSITY OF CALIFORNIA SPECIFICALLY DISCLAIMS ANY WARRANTIES,

INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY

AND FITNESS FOR A PARTICULAR PURPOSE.  THE SOFTWARE PROVIDED HEREUNDER IS

ON AN "AS IS" BASIS, AND THE UNIVERSITY OF CALIFORNIA HAS NO OBLIGATIONS TO

PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

49. What platforms does PostgreSQL support?

In general, any modern Unix-compatible platform should be able to run PostgreSQL. The platforms that have received recent explicit testing can be seen in the Build farm. The documentation contains more details about supported platforms at http://www.postgresql.org/docs/current/static/supported-platforms.html.

PostgreSQL also runs natively on Microsoft Windows NT-based operating systems like Windows XP, Vista, 7, 8, 2003, 2008, etc. A prepackaged installer is available at http://www.postgresql.org/download/windows.

Cygwin builds for Windows exist but are generally not recommended; use the native Windows builds instead. You can use the Cygwin build of the PostgreSQL client library (libpq) to connect to a native Windows PostgreSQL if you really need Cygwin for client applications.

50. How does PostgreSQL compare to other DBMSs?

There are several ways of measuring software: features, performance, reliability, support, and price.

Features

PostgreSQL has most features present in large proprietary DBMSs, like transactions, subselects, triggers, views, foreign key referential integrity, and sophisticated locking. We have some features they do not have, like user-defined types, inheritance, rules, and multi-version concurrency control to reduce lock contention.

Performance

PostgreSQL's performance is comparable to other proprietary and open source databases. It is faster for some things, slower for others. Our performance is usually +/-10% compared to other databases.

Reliability

We realize that a DBMS must be reliable, or it is worthless. We strive to release well-tested, stable code that has a minimum of bugs. Each release has at least one month of beta testing, and our release history shows that we can provide stable, solid releases that are ready for production use. We believe we compare favorably to other database software in this area.

Support

Our mailing lists provide contact with a large group of developers and users to help resolve any problems encountered. While we cannot guarantee a fix, proprietary DBMSs do not always supply a fix either. Direct access to developers, the user community, manuals, and the source code often make PostgreSQL support superior to other DBMSs. There is commercial per-incident support available for those who need it.

Price

We are free for all use, both proprietary and open source. You can add our code to your product with no limitations, except those outlined in our BSD-style license stated above.