PostgreSQL

From KevinWiki

(Difference between revisions)
Jump to: navigation, search
Line 27: Line 27:
==Administration==
==Administration==
-
===Setup Root User Password===
+
===Roles (Users)===
 +
====Setup Root User Password====
*Set up the password of the PostgreSQL root user
*Set up the password of the PostgreSQL root user
  $ sudo -u postgres psql postgres  
  $ sudo -u postgres psql postgres  
Line 46: Line 47:
  postgres=# \q  
  postgres=# \q  
-
===List Databases===
+
====Create Role (User)====
-
postgres=# \l
+
Creating a user in PostgreSQL is in fact creating a role. So there are two ways to do it. The first one is using <code>CREATE ROLE</code><ref>{{cite web |url=http://www.postgresql.org/docs/8.4/static/database-roles.html | title = PostgreSQL 8.4.4 Documentation - 20.1. Database Roles | date = 2009 | accessdate = 2010-08-02}}</ref> SQL command and the other one is using the programme, <code>createuser</code><ref>{{cite web|url= http://www.postgresql.org/docs/8.4/static/app-createuser.html | title= PostgreSQL 8.4.4 Documentation - createuser | date = 2009 | accessdate = 2010-08-02}}</ref>.
-
===Create Role (User)===
+
=====CREATE ROLE=====
-
Creating a user in PostgreSQL is in fact creating a role. So there are two ways to do it. The first one is using <code>CREATE ROLE</code><ref>{{cite web |url=http://www.postgresql.org/docs/8.4/static/database-roles.html | title = PostgreSQL 8.4.4 Documentation - 20.1. Database Roles | date = 2009 | accessdate = 2010-08-02}}</ref> SQL command and the other one is using the programme, <code>createuser</code><ref>{{cite web|url= http://www.postgresql.org/docs/8.4/static/app-createuser.html | title= PostgreSQL 8.4.4 Documentation - createuser | date = 2009 | accessdate = 2010-08-02}}</ref>.
+
-
====CREATE ROLE====
+
<code>CREATE ROLE</code><ref>{{cite web |url = http://www.postgresql.org/docs/8.4/static/sql-createrole.html | title = PostgreSQL 8.4.4 Documentation - CREATE ROLE | date = 2009 | accessdate = 2010-08-02}}</ref>
<code>CREATE ROLE</code><ref>{{cite web |url = http://www.postgresql.org/docs/8.4/static/sql-createrole.html | title = PostgreSQL 8.4.4 Documentation - CREATE ROLE | date = 2009 | accessdate = 2010-08-02}}</ref>
Line 57: Line 56:
-
====createuser====
+
=====createuser=====
  createuser [option...] [username]  
  createuser [option...] [username]  
Line 164: Line 163:
-
===View Roles===
+
====View Roles====
* To see all the exising roles.
* To see all the exising roles.
Line 173: Line 172:
-
===Remove Role===
+
====Remove Role====
* Using <code>DROP ROLE</code><ref>{{cite web | url = http://www.postgresql.org/docs/8.4/static/sql-droprole.html | title = PostgreSQL 8.4.4 Documentation - DROP ROLE | date = 2009 | accessdate = 2010-08-02}}</ref> SQL
* Using <code>DROP ROLE</code><ref>{{cite web | url = http://www.postgresql.org/docs/8.4/static/sql-droprole.html | title = PostgreSQL 8.4.4 Documentation - DROP ROLE | date = 2009 | accessdate = 2010-08-02}}</ref> SQL
  DROP ROLE role_name;  
  DROP ROLE role_name;  
Line 179: Line 178:
*Using <code>dropuser</code><ref>{{cite web | url = http://www.postgresql.org/docs/8.4/static/app-dropuser.html | title = PostgreSQL 8.4.4 Documentation - DROP ROLE | date = 2009 | accessdate = 2010-08-02}}</ref>
*Using <code>dropuser</code><ref>{{cite web | url = http://www.postgresql.org/docs/8.4/static/app-dropuser.html | title = PostgreSQL 8.4.4 Documentation - DROP ROLE | date = 2009 | accessdate = 2010-08-02}}</ref>
  dropuser name  
  dropuser name  
 +
 +
===Databases===
 +
====List Databases====
 +
postgres=# \l
 +
 +
====Creating Database====
 +
* Using <code>CREATE DATABASE</code><ref>{{cite web | url = http://www.postgresql.org/docs/8.4/static/sql-createdatabase.html | title = PostgreSQL 8.4.4 Documentation - CREATE DATABASE | date = 2009 | accessdate = 2010-08-02}}</ref> SQL to create a new database.
 +
CREATE DATABASE db_name
 +
 +
CREATE DATABASE db_name OWNER role_name
 +
 +
* Similar to creating a role, it is also possible to create a new database from the shell by using <code>createdb</code><ref>{{cite web | url = http://www.postgresql.org/docs/8.4/static/app-createdb.html | title = PostgreSQL 8.4.4 Documentation - createdb | date = 2009 | accessdate = 2010-08-02}}</ref> command.
 +
createdb [option...] [db_name] [description]
 +
 +
sudo -u postgres createuser -P username
 +
sudo -u postgres createdb -O username db_name
 +
 +
-O owner
 +
--owner owner
 +
      Specifies the database user who will own the new database.

Revision as of 15:57, 2 August 2010

PostgreSQL
Developer(s) PostgreSQL Global Development Group
Stable release 8.4.4 /
8.3.11 /
8.2.17 /
8.1.21 /
8.0.25 /
7.4.29 /
May 17, 2010; 14 years ago (2010-05-17)
Preview release 9.0 beta3 /
July 12, 2010; 14 years ago (2010-07-12)
Written in C
Operating system Cross-platform
Type ORDBMS
License PostgreSQL licence[1][2][3]
Website http://www.postgresql.org/

Contents

PostgreSQL

PostgreSQL is an object-relational database management system (ORDBMS). The documentation of PostgreSQL 8.4.4 can be found here[4]

Installation

  • Install PostgreSQL 
$ sudo apt-get install postgresql

Administration

Roles (Users)

Setup Root User Password

  • Set up the password of the PostgreSQL root user 
$ sudo -u postgres psql postgres 

Welcome to psql 8.3.5, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

postgres=# ALTER USER postgres WITH ENCRYPTED PASSWORD 'password';

or 

postgres=# \password postgres

Use \quit or \q to quit psql. 

postgres=# \q

Create Role (User)

Creating a user in PostgreSQL is in fact creating a role. So there are two ways to do it. The first one is using CREATE ROLE[5] SQL command and the other one is using the programme, createuser[6].

CREATE ROLE

CREATE ROLE[7] 

CREATE ROLE role_name;


createuser
createuser [option...] [username]

So to create a new user and a db belonging to him/her. 

sudo -u postgres createuser -P username 
sudo -u postgres createdb -O username db_name

-P: it will ask you to enter the password for the new user (role).


createuser accepts the following command-line arguments: 

username

    Specifies the name of the PostgreSQL user to be created. This name must be different from all existing roles in this PostgreSQL installation. 
-s
--superuser

    The new user will be a superuser. 
-S
--no-superuser

    The new user will not be a superuser. This is the default. 
-d
--createdb

    The new user will be allowed to create databases. 
-D
--no-createdb

    The new user will not be allowed to create databases. This is the default. 
-r
--createrole

    The new user will be allowed to create new roles (that is, this user will have CREATEROLE privilege). 
-R
--no-createrole

    The new user will not be allowed to create new roles. This is the default. 
-l
--login

    The new user will be allowed to log in (that is, the user name can be used as the initial session user identifier). This is the default. 
-L
--no-login

    The new user will not be allowed to log in. (A role without login privilege is still useful as a means of managing database permissions.) 
-i
--inherit

    The new role will automatically inherit privileges of roles it is a member of. This is the default. 
-I
--no-inherit

    The new role will not automatically inherit privileges of roles it is a member of. 
-c number
--connection-limit number

    Set a maximum number of connections for the new user. The default is to set no limit. 
-P
--pwprompt

    If given, createuser will issue a prompt for the password of the new user. This is not necessary if you do not plan on using password authentication. 
-E
--encrypted

    Encrypts the user's password stored in the database. If not specified, the default password behavior is used. 
-N
--unencrypted

    Does not encrypt the user's password stored in the database. If not specified, the default password behavior is used. 
-e
--echo

    Echo the commands that createuser generates and sends to the server. 

You will be prompted for a name and other missing information if it is not specified on the command line.

createuser also accepts the following command-line arguments for connection parameters:

-h host
--host host

    Specifies the host name of the machine on which the server is running. If the value begins with a slash, it is used as the directory for the Unix domain socket. 
-p port
--port port

    Specifies the TCP port or local Unix domain socket file extension on which the server is listening for connections. 
-U username
--username username

    User name to connect as (not the user name to create). 
-w
--no-password

    Never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail. This option can be useful in batch jobs and scripts where no user is present to enter a password. 
-W
--password

    Force createuser to prompt for a password (for connecting to the server, not for the password of the new user).

    This option is never essential, since createuser will automatically prompt for a password if the server demands password authentication. However, createuser will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt.


View Roles

  • To see all the exising roles. 
SELECT * FROM pg_roles;
  • To see the details of an existing role 
SELECT role_name FROM pg_roles;


Remove Role

  • Using DROP ROLE[9] SQL 
DROP ROLE role_name; 

dropuser name

Databases

List Databases

postgres=# \l

Creating Database

  • Using CREATE DATABASE[11] SQL to create a new database. 
CREATE DATABASE db_name 

CREATE DATABASE db_name OWNER role_name
  • Similar to creating a role, it is also possible to create a new database from the shell by using createdb[12] command. 
createdb [option...] [db_name] [description]

sudo -u postgres createuser -P username 
sudo -u postgres createdb -O username db_name 

-O owner
--owner owner
     Specifies the database user who will own the new database.


Restart Server

To restart the server, /etc/init.d/postgresql-8.x (depending on the version). 

sudo /etc/init.d/postgresql-8.4 restart


Install pgAdmin III

$ sudo apt-get install pgadmin3


References

  1. ↑ "PostgreSQL licence approved by OSI". 2010-02-18. http://www.crynwr.com/cgi-bin/ezmlm-cgi?17:mmp:969. Retrieved 2010-02-18. 
  2. ↑ "OSI PostgreSQL licence". 2010-02-20. http://www.opensource.org/licenses/postgresql. Retrieved 2010-02-20. 
  3. ↑ http://www.postgresql.org/about/licence
  4. ↑ "PostgreSQL 8.4.4 Documentation". 2009. http://www.postgresql.org/docs/8.4/static. Retrieved 2010-08-02. 
  5. ↑ "PostgreSQL 8.4.4 Documentation - 20.1. Database Roles". 2009. http://www.postgresql.org/docs/8.4/static/database-roles.html. Retrieved 2010-08-02. 
  6. ↑ "PostgreSQL 8.4.4 Documentation - createuser". 2009. http://www.postgresql.org/docs/8.4/static/app-createuser.html. Retrieved 2010-08-02. 
  7. ↑ "PostgreSQL 8.4.4 Documentation - CREATE ROLE". 2009. http://www.postgresql.org/docs/8.4/static/sql-createrole.html. Retrieved 2010-08-02. 
  8. ↑ "PostgreSQL 8.4.4 Documentation - 20.1. Database Roles#Options". 2009. http://www.postgresql.org/docs/8.4/static/app-createuser.html#AEN67555. Retrieved 2010-08-02. 
  9. ↑ "PostgreSQL 8.4.4 Documentation - DROP ROLE". 2009. http://www.postgresql.org/docs/8.4/static/sql-droprole.html. Retrieved 2010-08-02. 
  10. ↑ "PostgreSQL 8.4.4 Documentation - DROP ROLE". 2009. http://www.postgresql.org/docs/8.4/static/app-dropuser.html. Retrieved 2010-08-02. 
  11. ↑ "PostgreSQL 8.4.4 Documentation - CREATE DATABASE". 2009. http://www.postgresql.org/docs/8.4/static/sql-createdatabase.html. Retrieved 2010-08-02. 
  12. ↑ "PostgreSQL 8.4.4 Documentation - createdb". 2009. http://www.postgresql.org/docs/8.4/static/app-createdb.html. Retrieved 2010-08-02. 
Retrieved from "http://kevinlee.io/wiki/PostgreSQL"
Personal tools