角色与用户管理介绍,从安装到使用Go连接数据库
分类:面向对象

一、角色与用户的区别角色就相当于岗位:角色可以是经理,助理。用户就是具体的人:比如陈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)。

安装:

安装PostgresSQL

  1. 添加source
    sudo vi /etc/apt/sources.list.d/pgdg.list
    添加以下内容(zesty应根据不同版本代号更换):
    deb http://apt.postgresql.org/pub/repos/apt/ zesty-pgdg main
  2. 添加source key
    wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
  3. 更新并安装9.6
    sudo apt update
    apt-get install postgresql-9.6
    sudo apt-get install postgresql-9.6

1.1 创建角色与用户

sudo apt-get install postgresql

配置PostgreSQL并创建一个数据库

PostgreSQL安装完成后,系统中会创建一个叫做postgres的用户:
ethan@usb:~$ cat /etc/passwd | grep postgres
postgres:x:123:132:PostgreSQL administrator,,,:/var/lib/postgresql:/bin/bash

CREATE ROLE 语法

修改数据库的管理员用户密码:

1. ROLE配置

我们开始通过createdb尝试创建一个数据库,将会得到错误信息:
ethan@usb:~$ createdb mydb
createdb: could not connect to database template1: FATAL: role "ethan" does not exist

PostgreSQL 9.5及以上版本通过角色(ROLE)来管理权限。默认情况下,只有postgres这一个ROLE可以创建数据库,我们可以通过su切换到postgres来进行各种操作,也可以将用户添加到ROLE中,但我并不喜欢这种方式,如有需要可以自行找文档。

所以我们可以先给postgres用户设置一个密码:
sudo passwd postgres
设置完密码后,通过su切换到该用户:
su postgres

CREATE 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' | IN ROLE role_name [, ...] | IN GROUP role_name [, ...] | ROLE role_name [, ...] | ADMIN role_name [, ...] | USER role_name [, ...] | SYSID uid

PostgreSQL数据默认会创建一个叫postgres的数据库用户作为数据库的管理员

2. 创建数据库

createdb testdb

创建david 角色和sandy 用户postgres=# CREATE ROLE david; //默认不带LOGIN属性CREATE ROLEpostgres=# CREATE USER sandy; //默认具有LOGIN属性CREATE ROLEpostgres=# /du List of rolesRole name | Attributes | Member of -----------+------------------------------------------------+-----------david | Cannot login | {}postgres | Superuser, Create role, Create DB, Replication | {}sandy | | {}

sudo -u postgres psql                                          打开客户端工具(psql)

ALTER USER postgres WITH PASSWORD 'password';

3. 创建表

首先连接到数据库: 
psql testdb
查看当前有哪些表:
select * from information_schema.tables;
创建一个SCHEMA:
CREATE SCHEMA IF NOT EXISTS snw;
在SCHEMA snw下创建一个表:

CREATE TABLE snw.contacts(
id CHAR(20),
name TEXT,
email TEXT
);

postgres=# postgres=# SELECT rolname from pg_roles ;rolname ----------postgresdavidsandy(3 rows)

退出PostgreSQL psql客户端:

关于SCHEMA

SCHEMA相当于table的命名空间,不同SCHEMA中可以包含相同名字的table。此外,SCHEMA可以有一套自己的授权管理策略,从而控制所有子表的权限管理。
这并不是创建表必须的,但很有用!

postgres=# SELECT usename from pg_user; //角色david 创建时没有分配login权限,所以没有创建用户usename ----------postgressandy(2 rows)

q

插入值

INSERT INTO snw.contacts(name, email) VALUES
('Dr Nic Williams', 'drnic'),
('Brian Mattal', 'brain'),
('Wayne E. Seguin', 'wayneeseguin'),
('Long Nguyen', 'long');

postgres=# 1.2 验证LOGIN属性postgres@CS-DEV:~ psql -U davidpsql: FATAL: role "david" is not permitted to log inpostgres@CS-DEV:~ psql -U sandypsql: FATAL: database "sandy" does not existpostgres@CS-DEV:~ psql -U sandy -d postgrespsql (9.1.0)Type "help" for help.

修改ubuntu操作系统的postgres用户的密码(密码要与数据库用户postgres的密码相同)

使用Go连接数据库

我们新创建的数据库中,也包含了ROLE权限管理。默认已经有一个叫做postgres的ROLE,但是它还没有设置密码,不能通过远程连接它。
通过psql testdb进入数据库,为该数据库的用户postgres设置一个密码:
ALTER ROLE postgres WITH ENCRYPTED PASSWORD 'test_password';
现在postgres的密码就是test_password,但仅限于这个叫做testdb的数据库,不会影响系统中的postgres用户。

使用go get 添加github.com/lib/pq 作为Go的postgres 驱动:
go get github.com/lib/pq

package main

import (
"database/sql"

_ "github.com/lib/pq"
"log"
"fmt"
)


func main() {
db, err := sql.Open("postgres", "postgres://postgres:test_password@localhost/testdb")
//db, err := sql.Open("postgres", "user=tester dbname=testdb")
if err != nil {
    log.Fatal(err)
}

rows, err := db.Query("SELECT * FROM snw.contacts")
if err != nil {
    log.Fatal(err)
}
defer rows.Close()
for rows.Next() {
    var id string
    var name string
    var email string
    if err := rows.Scan(&id, &name, &email); err != nil {
        log.Fatal(err)
    }
    fmt.Printf("%s: %s Email: %sn", id, name, email)
}
}

postgres= /dtNo relations found.postgres= 用户sandy 可以登录,角色david 不可以登录。1.3 修改david 的权限,增加LOGIN权限postgres=# ALTER ROLE david LOGIN ;ALTER ROLEpostgres=# /du List of rolesRole name | Attributes | Member of -----------+------------------------------------------------+-----------david | | {}postgres | Superuser, Create role, Create DB, Replication | {}sandy | | {}

sudo passwd -d postgres                                    passwd -d 清空指定用户密码

sudo -u postgres passwd                                   设置PostgreSQL系统用户的密码

创建一个新的DB ROLE

CREATE ROLE gotester LOGIN ENCRYPTED PASSWORD 'gotester_password';

如果不指定LOGIN,那么使用该用户名密码连接数据库始终fail。
如果不指定PASSWORD,fail

这时如果通过这个用户名密码连接数据库,还会得到:
2017/10/01 09:06:16 pq: permission denied for schema snw

这就是SCHEMA的权限管理发挥作用了。可以将SCHEMA的owner转交给gotester:
ALTER SCHEMA snw OWNER TO gotester;
这时连接会出现:
2017/10/01 09:13:35 pq: permission denied for relation contacts
这时将snw.contacts也转交给gotester:
ALTER TABLE snw.contacts OWNER TO gotester;

一切OK。

在PostgreSQL中,postgres是SUPERUSER,根据权限最小的原则,最好使用具有较小权限的用户来进行各种操作。

postgres=# SELECT rolname from pg_roles ;rolname ----------postgressandydavid(3 rows)

登录postgre SQL数据库:

postgres=# SELECT usename from pg_user; //给david 角色分配login权限,系统将自动创建同名用户davidusename ----------postgressandydavid(3 rows)

psql -U postgres -h 127.0.0.1

postgres=# 1.4 再次验证LOGIN属性postgres@CS-DEV:~ psql -U david -d postgrespsql (9.1.0)Type "help" for help.

查看数据库用户:

postgres= /du List of rolesRole name | Attributes | Member of -----------+------------------------------------------------+-----------david | | {}postgres | Superuser, Create role, Create DB, Replication | {}sandy | | {}

du

postgres= david 现在也可以登录了。

查看所以数据库

二、查看角色信息

l

psql 终端可以用/du 或/du+ 查看,也可以查看系统表 select * from pg_roles;postgres= /du List of rolesRole name | Attributes | Member of -----------+------------------------------------------------+-----------david | Cannot login | {}postgres | Superuser, Create role, Create DB, Replication | {}sandy | | {}

SHELL创建[超级](删除)用户

postgres= /du+ List of rolesRole name | Attributes | Member of | Description -----------+------------------------------------------------+-----------+-------------david | Cannot login | {} | postgres | Superuser, Create role, Create DB, Replication | {} | sandy | | {} |

createuser [--superuser](dropuser)username

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 | ******** | | | 10david | f | t | f | f | f | f | f | -1 | ******** | | | 49438sandy | f | t | f | f | f | t | f | -1 | ******** | | | 49439(3 rows)

SHELL创建(删除)数据库

postgres=

createdb(dropdb) dbname

三、角色属性

登陆权限:

一个数据库角色可以有一系列属性,这些属性定义了他的权限。

CREATE ROLE name LOGIN;                               CREATE USER name 默认具有登陆权限

属性说明login只有具有LOGIN属性的角色可以用做数据库连接的初始角色名。superuser数据库超级用户createdb创建数据库权限createrole 允许其创建或删除其他普通的用户角色(超级用户除外)replication做流复制的时候用到的一个用户属性,一般单独设定。password在登录时要求指定密码时才会起作用,比如md5或者password模式,跟客户端的连接认证方式有关inherit用户组对组员的一个继承标志,成员可以继承用户组的权限特性......

超级用户,拥有所有权限:

四、创建用户时赋予角色属性从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模式,跟客户端的连接认证方式有关。

CREATE ROLE name SUPERUSER

查看pg_hba.conf 文件,发现local 的METHOD 为trust,所以不需要输入密码。

创建数据库权限:

将local 的METHOD 更改为password,然后保存重启postgresql。

CREATE ROLE name CREATEDB

c.再次验证

创建、更改、删除角色权限:

提示输入密码,输入正确密码后进入到数据库。

CREATE ROLE name CREATEROLE

d. 测试创建数据库

启动复制的权限:

创建成功。

CREATE ROLE name REPLICATION LOGIN

五、给已存在用户赋予各种权限

口令:

使用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'

password和md5认证方法使用口令。

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 CURRENTALTER ROLE name [ IN DATABASE database_name ] RESET configuration_parameterALTER ROLE name [ IN DATABASE database_name ] RESET ALL5.1 赋予bella 登录权限a. 查看现在的角色属性postgres=# /du List of rolesRole name | Attributes | Member of -----------+------------------------------------------------+-----------bella | Create DB, Cannot login | {}david | | {}postgres | Superuser, Create role, Create DB, Replication | {}renee | Create DB | {}sandy | | {}

CREATE ROLE name PASSWORD “password

postgres=# b. 赋予登录权限postgres=# ALTER ROLE bella WITH LOGIN;ALTER ROLEpostgres=# /du List of rolesRole 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 ROLEpostgres=# /du List of rolesRole name | Attributes | Member of -----------+------------------------------------------------+-----------bella | Create DB | {}david | | {}postgres | Superuser, Create role, Create DB, Replication | {}renee | Create role, Create DB | {}sandy | | {}

一个角色的属性可以在创建后用ALTER ROLE修改

postgres=# 5.3 赋予david 带密码登录权限postgres=# ALTER ROLE david WITH PASSWORD 'ufo456';ALTER ROLEpostgres=#5.4 设置sandy 角色的有效期postgres=# ALTER ROLE sandy VALID UNTIL '2014-04-24';ALTER ROLEpostgres=# /du List of rolesRole 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 | ******** | | | 10bella | f | t | f | t | f | t | f | -1 | ******** | | | 49440renee | f | t | t | t | f | t | f | -1 | ******** | | | 49442david | f | t | f | f | f | t | f | -1 | ******** | | | 49438sandy | f | t | f | f | f | t | f | -1 | ******** | 2014-04-24 00:00:00+08 | | 49439(5 rows)

/etc/init.d/postgresql restart

postgres=#

重新加载配置文件

六、角色赋权/角色成员

/etc/init.d/postgresql reload

在系统的角色管理中,通常会把多个角色赋予一个组,这样在设置权限时只需给该组设置即可,撤销权限时也是从该组撤销。在PostgreSQL中,首先需要创建一个代表组的角色,之后再将该角色的membership 权限赋给独立的角色即可。6.1 创建组角色postgres=# CREATE ROLE father login nosuperuser nocreatedb nocreaterole noinherit encrypted password 'abc123';CREATE ROLEpostgres=# /du List of rolesRole name | Attributes | Member of -----------+------------------------------------------------+-----------bella | Create DB | {}david | | {}father | No inheritance | {}postgres | Superuser, Create role, Create DB, Replication | {}renee | Create role, Create DB | {}sandy | | {}

在shell命令行下,创建数据库exampledb,并指定所有者为dbuser

postgres=#6.2 给father 角色赋予数据库test 连接权限和相关表的查询权限。postgres=# GRANT CONNECT ON DATABASE test to father;GRANTpostgres=# /c test reneeYou are now connected to database "test" as user "renee".test= /dtNo 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 TABLEtest= INSERT INTO emp (name) VALUES ('david'); INSERT 0 1test= INSERT INTO emp (name) VALUES ('sandy');INSERT 0 1test= SELECT * from emp;id | name ----+------- 1 | david 2 | sandy(2 rows)

sudo -u postgres createdb -O dbuser exampled

test= /dt List of relationsSchema | Name | Type | Owner --------+------+-------+-------public | emp | table | renee(1 row)

登入数据库

test= GRANT USAGE ON SCHEMA public to father;WARNING: no privileges were granted for "public"GRANTtest= GRANT SELECT on public.emp to father;GRANTtest= 6.3 创建成员角色test= /c postgres postgresYou are now connected to database "postgres" as user "postgres".postgres=# CREATE ROLE son1 login nosuperuser nocreatedb nocreaterole inherit encrypted password 'abc123';CREATE ROLEpostgres=# 这里创建了son1 角色,并开启inherit 属性。PostgreSQL 里的角色赋权是通过角色继承的方式实现的。6.4 将father 角色赋给son1postgres=# GRANT father to son1;GRANT ROLEpostgres=# 还有另一种方法,就是在创建用户的时候赋予角色权限。postgres=# CREATE ROLE son2 login nosuperuser nocreatedb nocreaterole inherit encrypted password 'abc123' in role father;CREATE ROLEpostgres=# 6.5 测试son1 角色postgres=# /c test son1You are now connected to database "test" as user "son1".test= /dt List of relationsSchema | Name | Type | Owner --------+------+-------+-------public | emp | table | renee(1 row)

psql -U dbuser -d exampledb-h 127.0.0.1 -p 5432

test= SELECT * from emp;id | name ----+------- 1 | david 2 | sandy(2 rows)

http://blog.sina.com.cn/s/blog_581df0be0101ju3o.html

test= 用renee 角色新创建一张表,再次测试test= /c test reneeYou are now connected to database "test" as user "renee".test= CREATE TABLE dept (test( deptid integer,test( deptname text);CREATE TABLEtest= INSERT INTO dept (deptid, deptname) values(1, 'ts');INSERT 0 1test= /c test son1You are now connected to database "test" as user "son1".test= SELECT * from dept ;ERROR: permission denied for relation depttest= son1 角色只能查询emp 表的数据,而不能查询dept 表的数据,测试成功。6.6 查询角色组信息test= /c postgres postgresYou are now connected to database "postgres" as user "postgres".postgres=# postgres=# /du List of rolesRole 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 角色组。

本文由10bet手机官网发布于面向对象,转载请注明出处:角色与用户管理介绍,从安装到使用Go连接数据库

上一篇:没有了 下一篇:没有了
猜你喜欢
热门排行
精彩图文