First steps

From PostgreSQL wiki

Revision as of 21:41, 1 November 2013 by Niels (Talk | contribs)

Jump to: navigation, search


please add info or links:

First steps after installation

First connect/login as root

 # su - postgres
 $ psql
 psql (9.2.4)
 Type "help" for help.
 postgres=#  help
 You are using psql, the command-line interface to PostgreSQL.
 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

Create a schema called test in the default database called postgres

 postgres=# CREATE SCHEMA test;

Create a role (user) with password

 postgres=# CREATE USER xxx PASSWORD 'yyy';

Grant privileges (like the ability to create tables) on new schema to new role

 postgres=# GRANT ALL ON SCHEMA test TO xxx;

Grant privileges (like the ability to insert) to tables in the new schema to the new role

 postgres=# GRANT ALL ON ALL TABLES IN SCHEMA test TO xxx;


 postgres=# \q

Became a standard user.
The default authentication mode is set to 'ident' which means a given Linux user xxx can only connect as the postgres user xxx.

 # su - xxx

Login from xxx user in shell to default postgres db

 xxx$ psql -d postgres
 psql (9.2.4)
 Type "help" for help.

Create a table test in schema test

 postgres=>CREATE TABLE test.test (coltest varchar(20));

Insert a single record into new table

 postgres=>insert into test.test (coltest) values ('It works!');

First SELECT from a table

 postgres=>SELECT * from test.test;
  It works!
 (1 row)

Drop test table

 postgresql=>DROP TABLE test.test;
Personal tools