PostgreSQL
From KevinWiki
Line 46: | Line 46: | ||
Use <code>\quit</code> or <code>\q</code> to quit psql. | Use <code>\quit</code> or <code>\q</code> to quit psql. | ||
postgres=# \q | postgres=# \q | ||
+ | |||
+ | ====Setup Authentication Methods==== | ||
+ | * In order to allow new users to log in the PostgreSQL server, <code>/etc/postgresql/8.4/main/pg_hba.conf</code> should be modified. | ||
+ | |||
+ | # TYPE DATABASE USER CIDR-ADDRESS METHOD | ||
+ | |||
+ | # "local" is for Unix domain socket connections only | ||
+ | local all all ident | ||
+ | # IPv4 local connections: | ||
+ | host all all 127.0.0.1/32 md5 | ||
+ | # IPv6 local connections: | ||
+ | host all all ::1/128 md5 | ||
+ | To | ||
+ | # TYPE DATABASE USER CIDR-ADDRESS METHOD | ||
+ | |||
+ | # "local" is for Unix domain socket connections only | ||
+ | local all all md5 | ||
+ | # IPv4 local connections: | ||
+ | #host all all 127.0.0.1/32 md5 | ||
+ | # IPv6 local connections: | ||
+ | #host all all ::1/128 md5 | ||
+ | Allowing the local access only with username & password. | ||
+ | |||
====Create Role (User)==== | ====Create Role (User)==== | ||
Line 205: | Line 228: | ||
--owner owner | --owner owner | ||
Specifies the database user who will own the new database. | Specifies the database user who will own the new database. | ||
+ | |||
+ | ====Change Database==== | ||
+ | e.g.) | ||
+ | postgres=# ALTER DATABASE db_name OWNER TO new_owner; | ||
+ | |||
+ | ===Privileges=== | ||
+ | ====GRANT==== | ||
+ | e.g.) | ||
+ | postgres=# GRANT ALL PRIVILEGES ON DATABASE db_name TO username; | ||
Revision as of 18:09, 2 August 2010
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 |
Preview release |
9.0 beta3 / July 12, 2010 |
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
Setup Authentication Methods
- In order to allow new users to log in the PostgreSQL server,
/etc/postgresql/8.4/main/pg_hba.conf
should be modified.
# TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all all ident # IPv4 local connections: host all all 127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5
To
# TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all all md5 # IPv4 local connections: #host all all 127.0.0.1/32 md5 # IPv6 local connections: #host all all ::1/128 md5
Allowing the local access only with username & password.
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; CREATE ROLE role_name WITH LOGIN ENCRYPTED PASSWORD 'password'; CREATE USER role_name WITH ENCRYPTED PASSWORD 'password';
(CREATE USER == CREATE ROLE + LOGIN)
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).
- Options[8]
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;
Change Role
-
ALTER ROLE
[9] SQL
ALTER ROLE role_name WITH LOGIN ENCRYPTED PASSWORD 'password';
Remove Role
- Using
DROP ROLE
[10] SQL
DROP ROLE role_name;
- Using
dropuser
[11]
dropuser name
Databases
List Databases
postgres=# \l
Creating Database
- Using
CREATE DATABASE
[12] 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
[13] 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.
Change Database
e.g.)
postgres=# ALTER DATABASE db_name OWNER TO new_owner;
Privileges
GRANT
e.g.)
postgres=# GRANT ALL PRIVILEGES ON DATABASE db_name TO username;
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
- ↑ "PostgreSQL licence approved by OSI". 2010-02-18. http://www.crynwr.com/cgi-bin/ezmlm-cgi?17:mmp:969. Retrieved 2010-02-18.
- ↑ "OSI PostgreSQL licence". 2010-02-20. http://www.opensource.org/licenses/postgresql. Retrieved 2010-02-20.
- ↑ http://www.postgresql.org/about/licence
- ↑ "PostgreSQL 8.4.4 Documentation". 2009. http://www.postgresql.org/docs/8.4/static. Retrieved 2010-08-02.
- ↑ "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.
- ↑ "PostgreSQL 8.4.4 Documentation - createuser". 2009. http://www.postgresql.org/docs/8.4/static/app-createuser.html. Retrieved 2010-08-02.
- ↑ "PostgreSQL 8.4.4 Documentation - CREATE ROLE". 2009. http://www.postgresql.org/docs/8.4/static/sql-createrole.html. Retrieved 2010-08-02.
- ↑ "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.
- ↑ "PostgreSQL 8.4.4 Documentation - ALTER ROLE". 2009. http://www.postgresql.org/docs/8.4/static/sql-alterrole.html. Retrieved 2010-08-02.
- ↑ "PostgreSQL 8.4.4 Documentation - DROP ROLE". 2009. http://www.postgresql.org/docs/8.4/static/sql-droprole.html. Retrieved 2010-08-02.
- ↑ "PostgreSQL 8.4.4 Documentation - dropuser". 2009. http://www.postgresql.org/docs/8.4/static/app-dropuser.html. Retrieved 2010-08-02.
- ↑ "PostgreSQL 8.4.4 Documentation - CREATE DATABASE". 2009. http://www.postgresql.org/docs/8.4/static/sql-createdatabase.html. Retrieved 2010-08-02.
- ↑ "PostgreSQL 8.4.4 Documentation - createdb". 2009. http://www.postgresql.org/docs/8.4/static/app-createdb.html. Retrieved 2010-08-02.