创建pgsql的数据库

创建用户时赋予角色属性
从pg_roles 表里查看到的信息，在上面创建的david 用户时，默认没有创建数据库等权限。 
postgres@CS-DEV:~> psql -U david -d postgres 
psql (9.1.0) 
Type "help" for help. 
postgres=> \du 
List of roles 
Role name | Attributes | Member of 
-----------+------------------------------------------------+----------- 
david | | {} 
postgres | Superuser, Create role, Create DB, Replication | {} 
sandy | | {} 
postgres=> CREATE DATABASE test; 
ERROR: permission denied to create database 
postgres=> 
如果要在创建角色时就赋予角色一些属性，可以使用下面的方法。 
首先切换到postgres 用户。 
4.1 创建角色bella 并赋予其CREATEDB 的权限。 
postgres=# CREATE ROLE bella CREATEDB ; 
CREATE ROLE 
postgres=# \du 
List of roles 
Role name | Attributes | Member of 
-----------+------------------------------------------------+----------- 
bella | Create DB, Cannot login | {} 
david | | {} 
postgres | Superuser, Create role, Create DB, Replication | {} 
sandy | | {} 
postgres=# 
4.2 创建角色renee 并赋予其创建数据库及带有密码登录的属性。 
postgres=# CREATE ROLE renee CREATEDB PASSWORD ‘abc123‘ LOGIN; 
CREATE ROLE 
postgres=# \du 
List of roles 
Role name | Attributes | Member of 
-----------+------------------------------------------------+----------- 
bella | Create DB, Cannot login | {} 
david | | {} 
postgres | Superuser, Create role, Create DB, Replication | {} 
renee | Create DB | {} 
sandy | | {} 
postgres=# 
4.3 测试renee 角色 
a. 登录 
postgres@CS-DEV:~> psql -U renee -d postgres 
psql (9.1.0) 
Type "help" for help. 
postgres=> 
用renee 用户登录数据库，发现不需要输入密码既可登录，不符合实际情况。 
b. 查找原因 
在角色属性中关于password的说明，在登录时要求指定密码时才会起作用，比如md5或者password模式，跟客户端的连接认证方式有关。
查看pg_hba.conf 文件，发现local 的METHOD 为trust，所以不需要输入密码。

将local 的METHOD 更改为password，然后保存重启postgresql。
c. 再次验证

提示输入密码，输入正确密码后进入到数据库。
d. 测试创建数据库

创建成功。
给已存在用户赋予各种权限
使用ALTER ROLE 命令。
ALTER ROLE 语法：
ALTER ROLE name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| CREATEUSER | NOCREATEUSER
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| CONNECTION LIMIT connlimit
| [ ENCRYPTED | UNENCRYPTED ] PASSWORD ‘password‘
| VALID UNTIL ‘timestamp‘
ALTER ROLE name RENAME TO new_name
ALTER ROLE name [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER ROLE name [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER ROLE name [ IN DATABASE database_name ] RESET configuration_parameter
ALTER ROLE name [ IN DATABASE database_name ] RESET ALL
5.1 赋予bella 登录权限
a. 查看现在的角色属性
postgres=# \du
List of roles
Role name | Attributes | Member of 
-----------+------------------------------------------------+-----------
bella | Create DB, Cannot login | {}
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
renee | Create DB | {}
sandy | | {}
postgres=# 
b. 赋予登录权限
postgres=# ALTER ROLE bella WITH LOGIN;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of 
-----------+------------------------------------------------+-----------
bella | Create DB | {}
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
renee | Create DB | {}
sandy | | {}
postgres=# 
5.2 赋予renee 创建角色的权限
postgres=# ALTER ROLE renee WITH CREATEROLE;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of 
-----------+------------------------------------------------+-----------
bella | Create DB | {}
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
renee | Create role, Create DB | {}
sandy | | {}
postgres=# 
5.3 赋予david 带密码登录权限
postgres=# ALTER ROLE david WITH PASSWORD ‘ufo456‘;
ALTER ROLE
postgres=#
5.4 设置sandy 角色的有效期
postgres=# ALTER ROLE sandy VALID UNTIL ‘2014-04-24‘;
ALTER ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of 
-----------+------------------------------------------------+-----------
bella | Create DB | {}
david | | {}
postgres | Superuser, Create role, Create DB, Replication | {}
renee | Create role, Create DB | {}
sandy | | {}
postgres=# SELECT * from pg_roles ;
rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolreplication | rolconnlimit | rolpassword | rolvaliduntil | rolconfig | oid 
----------+----------+------------+---------------+-------------+--------------+-------------+----------------+--------------+-------------+------------------------+-----------+-------
postgres | t | t | t | t | t | t | t | -1 | ******** | | | 10
bella | f | t | f | t | f | t | f | -1 | ******** | | | 49440
renee | f | t | t | t | f | t | f | -1 | ******** | | | 49442
david | f | t | f | f | f | t | f | -1 | ******** | | | 49438
sandy | f | t | f | f | f | t | f | -1 | ******** | 2014-04-24 00:00:00+08 | | 49439
(5 rows)
postgres=#
角色赋权/角色成员
在系统的角色管理中，通常会把多个角色赋予一个组，这样在设置权限时只需给该组设置即可，撤销权限时也是从该组撤销。在PostgreSQL中，首先需要创建一个代表组的角色，之后再将该角色的membership 权限赋给独立的角色即可。
6.1 创建组角色
postgres=# CREATE ROLE father login nosuperuser nocreatedb nocreaterole noinherit encrypted password ‘abc123‘;
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of 
-----------+------------------------------------------------+-----------
bella | Create DB | {}
david | | {}
father | No inheritance | {}
postgres | Superuser, Create role, Create DB, Replication | {}
renee | Create role, Create DB | {}
sandy | | {}
postgres=#
6.2 给father 角色赋予数据库test 连接权限和相关表的查询权限。
postgres=# GRANT CONNECT ON DATABASE test to father;
GRANT
postgres=# \c test renee
You are now connected to database "test" as user "renee".
test=> \dt
No relations found.
test=> CREATE TABLE emp (
test(> id serial,
test(> name text);
NOTICE: CREATE TABLE will create implicit sequence "emp_id_seq" for serial column "emp.id"
CREATE TABLE
test=> INSERT INTO emp (name) VALUES (‘david‘); 
INSERT 0 1
test=> INSERT INTO emp (name) VALUES (‘sandy‘);
INSERT 0 1
test=> SELECT * from emp;
id | name 
----+-------
1 | david
2 | sandy
(2 rows)
test=> \dt
List of relations
Schema | Name | Type | Owner 
--------+------+-------+-------
public | emp | table | renee
(1 row)
test=> GRANT USAGE ON SCHEMA public to father;
WARNING: no privileges were granted for "public"
GRANT
test=> GRANT SELECT on public.emp to father;
GRANT
test=> 
6.3 创建成员角色
test=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# CREATE ROLE son1 login nosuperuser nocreatedb nocreaterole inherit encrypted password ‘abc123‘;
CREATE ROLE
postgres=# 
这里创建了son1 角色，并开启inherit 属性。PostgreSQL 里的角色赋权是通过角色继承（INHERIT）的方式实现的。
6.4 将father 角色赋给son1
postgres=# GRANT father to son1;
GRANT ROLE
postgres=# 
还有另一种方法，就是在创建用户的时候赋予角色权限。
postgres=# CREATE ROLE son2 login nosuperuser nocreatedb nocreaterole inherit encrypted password ‘abc123‘ in role father;
CREATE ROLE
postgres=# 
6.5 测试son1 角色
postgres=# \c test son1
You are now connected to database "test" as user "son1".
test=> \dt
List of relations
Schema | Name | Type | Owner 
--------+------+-------+-------
public | emp | table | renee
(1 row)
test=> SELECT * from emp;
id | name 
----+-------
1 | david
2 | sandy
(2 rows)
test=> 
用renee 角色新创建一张表，再次测试
test=> \c test renee
You are now connected to database "test" as user "renee".
test=> CREATE TABLE dept (
test(> deptid integer,
test(> deptname text);
CREATE TABLE
test=> INSERT INTO dept (deptid, deptname) values(1, ‘ts‘);
INSERT 0 1
test=> \c test son1
You are now connected to database "test" as user "son1".
test=> SELECT * from dept ;
ERROR: permission denied for relation dept
test=> 
son1 角色只能查询emp 表的数据，而不能查询dept 表的数据，测试成功。
6.6 查询角色组信息
test=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# 
postgres=# \du
List of roles
Role name | Attributes | Member of 
-----------+------------------------------------------------+-----------
bella | Create DB | {}
david | | {}
father | No inheritance | {}
postgres | Superuser, Create role, Create DB, Replication | {}
renee | Create role, Create DB | {}
sandy | | {}
son1 | | {father}
son2 | | {father}
postgres=# 
“ Member of ” 项表示son1 和son2 角色属于father 角色组。
提示错误就用下面这条命令
initdb -E UTF-8 -D /var/lib/pgsql/data --locale=en_US.UTF-8 -U postgres -W
The files belonging to this database system will be owned by user "postgres".  
修改/var/lib/pgsql/data/postgresql.conf
修改/var/lib/pgsql/data/pg_hba.conf
postgres -D /var/lib/pgsql/data    or    pg_ctl -D /var/lib/pgsql/data -l logfile start
新开一个窗口
cd /var/lib/pgsql
su - postgres
psql
角色与用户的区别
角色就相当于岗位：角色可以是经理，助理。
用户就是具体的人：比如陈XX经理，朱XX助理，王XX助理。
在PostgreSQL 里没有区分用户和角色的概念，"CREATE USER" 为 "CREATE ROLE" 的别名，这两个命令几乎是完全相同的，唯一的区别是"CREATE USER" 命令创建的用户默认带有LOGIN属性，而"CREATE ROLE" 命令创建的用户默认不带LOGIN属性(CREATE USER is equivalent to CREATE ROLE except that CREATE USER assumes LOGIN by default, while CREATE ROLE does not)。
postgres=# Alter USER postgres WITH PASSWORD ‘***密码**‘;  //添加密码

创建pgsql的数据库

标签：doc   撤销   b16   登录   lte   属性   tar   提示   multi