PostgreSQL

From KevinWiki

(Difference between revisions)
Jump to: navigation, search
Line 31: Line 31:
===Create User===
===Create 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 <code>CREATE ROLE</code> SQL command and the other one is using the programme, <code>createuser</code>.
+
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====
====CREATE ROLE====
-
  CREATE ROLE name;
+
  CREATE ROLE name;  
====createuser====
====createuser====
-
  createuser [option...] [username]
+
  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''').
 +
 
 +
 
 +
* Options<ref>{{cite web | url = http://www.postgresql.org/docs/8.4/static/app-createuser.html#AEN67555 | title = PostgreSQL 8.4.4 Documentation - 20.1. Database Roles#Options}}</ref>
 +
 
 +
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.
 +
 
 +
 
====References====
====References====
-
* [http://www.postgresql.org/docs/8.4/static/database-roles.html PostgreSQL 8.4.4 Documentation - 20.1. Database Roles]
+
{{Reflist|1}}
-
* [http://www.postgresql.org/docs/8.4/static/app-createuser.html PostgreSQL 8.4.4 Documentation - createuser]
+

Revision as of 13:57, 2 August 2010


Contents

PostgreSQL

PostgreSQL is an object-relational database management system (ORDBMS).

Installation

  • Install PostgreSQL
$ sudo apt-get install postgresql 

Administration

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 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[1] SQL command and the other one is using the programme, createuser[2].

CREATE ROLE

CREATE 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. 


References


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

http://www.postgresql.org/docs/8.4/static

Personal tools