Step-by-Step PostgreSQL (III): network access configuration, user and role management, and tablespace

The previous articles briefly introduced the manual installation and startup of PG. For example: blog. csdn. netiiheroarticledetails8254115 (installation FAQ) Step by Step PostgreSQL: Implement PostgreSQL self-starting step by step PostgreSQL: Learn pqxxC ++ API access the dynamic link library written in C is the number of PostgreSQL

The previous articles briefly introduced the manual installation and startup of PG. For example: http://blog.csdn.net/iihero/article/details/8254115 (installation FAQ) step-by-step PostgreSQL: To achieve PostgreSQL self-starting step-by-step PostgreSQL: Learning pqxx C ++ API access using C written dynamic link library for PostgreSQL

The previous articles briefly introduced the manual installation and startup of PG.

For example:
Http://blog.csdn.net/iihero/article/details/8254115 (installation FAQ)

Step-by-Step PostgreSQL: Implement PostgreSQL self-starting
Step-by-Step PostgreSQL: Learn about pqxx C ++ API access
Use the dynamic link library written in C to add custom functions to the PostgreSQL database

To use PG properly, you must also understand its basic network configuration, user permissions, and how the database is created. Other usage is basically the same as that of other databases.

1. Network Configuration

By default, the configuration file of PG is:
There is a line in postgresql. conf: listen_addresses = 'localhost ';
There are several other rows in pg_cmd.conf:
# IPv4 local connections:
Host all 127.0.0.1/32 trust # IPv6 local connections:
Host all: 1/128 trust
This means to only listen to the local machine. All users on the local machine are trusted and can be accessed without a password. The IP address is limited to 127.0.0.1./32 means that 32 bits are valid addresses.
I want to restrict access to hosts only on the network segment 10.128.98. *. First, modify postgresql. conf,
Listen_addresses = '*';
Add another line in pg_hba.conf:
Host all 10.128.98.0/24 md5

(Note: If you only want to access a machine, use 10.128.98.4/32, indicating that all 32-bit access addresses are valid addresses)

Open to all network segments:
# IPV4
Host all 0.0.0.0/0 md5
# IPV6
Host all:/0 md5
When Will IPV6 be used? When you use psql-h It is not an IP address.
The specific description definition, can refer to: http://www.postgresql.org/docs/8.2/static/datatype-net-types.html

After that, you can access the local database from another host.

2. Create and manage users, groups, roles, and Schemas
The roles in PG are separated from users, with some minor differences. Different from other databases. Remember.
Its user is equivalent to adding a login to the role, and its group is equivalent to adding a nologin to the role, that is, it is essentially a role. this explains why you cannot log on after creating a role.

Iihero = # \ du
List of roles
Role name | Attributes | Member
----------- + -------------------------------------------------- + -----------
Spring | Superuser, Create role, Create DB | {}

Create a new user spring2 with the createdb createuser permission
Iihero = # create user spring2 password 'spring1' createdb createuser;
CREATE ROLE
Iihero = # \ du
List of roles
Role name | Attributes | Member
----------- + -------------------------------------------------- + -----------
Spring | Superuser, Create role, Create DB | {}
Spring2 | Superuser, Create DB | {}

Problem:
Iihero = # create table spring2.t1 (id int );
ERROR: schema "spring2" does not exist
Why cannot I create a table in spring 2? Because PG has the concept of schema. User spring2 is not equal to schema spring2.
To work properly, create a schema with the same name for spring2.
Iihero = # create schema spring2 authorization spring2;
CREATE SCHEMA
Iihero = # create table spring2.t1 (id int );
CREATE TABLE
We cut it down to the session of spring2, check and view:
Iihero = # set session authorization spring2;
SET
Iihero = # \ d
List of relations
Schema | Name | Type | Owner
--------- + ------ + ------- + --------
Spring2 | t1 | table | spring
(1 row)
The created table is displayed.

You can also create different schema names:
Iihero = # create schema spring_new authorization spring2;
CREATE SCHEMA
Iihero = # create table spring_new.t1 (id int );
CREATE TABLE
To create a schema with the same name, save the schema name in the middle. In this way, it is exactly the same as the user name.
Iihero = # create schema authorization spring2;
CREATE SCHEMA

Therefore, after creating a user with a password, you need to create a schema with the same name in one step to complete the equivalent functions of other databases.
The following is a complete sequence experiment for creating users, schemas, corresponding tables, and deletions:

iihero=# create user spring3 password 'spring1';CREATE ROLEiihero=# create schema authorization spring3;CREATE SCHEMAiihero=# create table spring3.t1(id int);CREATE TABLEiihero=# insert into spring3.t1 values(1), (2);INSERT 0 2iihero=# select * from spring3.t1; id----  1  2(2 rows)iihero=# drop table spring3.t1;DROP TABLEiihero=# drop user spring3;ERROR:  role "spring3" cannot be dropped because some objects depend on itDETAIL:  owner of schema spring3iihero=# drop schema spring3;DROP SCHEMAiihero=# drop user spring3;DROP ROLE

3. Create tablespaces and databases
PostgreSQL supports the concept of tablespace, and a tablespace can be placed in multiple databases (although this is rarely done). A database can also be placed in multiple tablespaces, for example, you can specify some tables to different tablespaces.

Generally, you can select different databases, create different tablespaces, or create a tablespace in different locations to improve the performance.

The following is a simple example. You can use the psql console or command line only:
Create tablespace demospace location 'd:/pgsql92latest/demo'; iihero = # create database demo encoding 'latin1' template template0 OWNER spring tablespace demospace;
CREATE DATABASEiihero = # drop database demo;
Drop database or: D: \ pgsql92latest> createdb -- tablespace = demospace-e -- encoding = latin1 -- owner = spring -- template = template0 demo
Create database "demo" OWNER spring TABLESPACE demospace ENCODING 'latin1' TEMPLATE template0; or D: \ pgsql92latest> createdb-Ddemospace-e-Elatin1-Ospring-Ttemplate0 demo
Create database demo OWNER spring TABLESPACE demospace ENCODING 'latin1' TEMPLATE template0;

Summary:
When using PG, you can perform basic manual installation, configure network access restrictions, create database users and corresponding schemas, and clear them in sequence.
In this way, you can proceed to the next step of basic management and development. For developers, With the concept corresponding to schema, the things behind it should be similar.