Postgresql 学习笔记（1）

一、环境

#配置远程连接

supostgres
vim/var/lib/pgsql/9.4/data/postgresql.conf编辑配置文件
listen_address=’localhost’前面的注释#去掉，并把’localhost’该为’*’;
vim/etc/postgresql/8.2/main/pg_hba.conf
hostallall192.168.1.0/24passwordpassword可以设置为trust
/etc/init.d/postgresql-8.2restart重启服务：还有stopstart命令一样的。如果配置错误可能导致无法重启

二、语法：

psql-Upostgres#进入数据库
l#查看有哪些数据库
cpostgresql#选择postgresql这个数据库，会提示进入连接
dt#查看所有表
dtablename#查看某张表结构
h#查看帮助

三、创建只读用户

#1.创建表
createtablet1(idserial,namevarchar(64));
CREATETABLE
postgres=#dtListofrelations
Schema|Name|Type|Owner
--------+------+-------+----------
public|t1|table|postgres
(1row)

2.创建用户u1
createroleu1withloginpassword'123456';#login是赋予登录权限，否则是不能登录的
CREATEROLE

3.赋予u1对表的只读权限（因为创建的普通用户默认是没有任何权限的）
postgres=#c-u1
FATAL:Peerauthenticationfailedforuser"u2"
Previousconnectionkept

如果出现以上信息，则需改配置文件：
vim/etc/postgresql/9.6/main/pg_hba.conf
找到下面的一行：
localallpostgrespeer
改成：
localallpostgrestrust

如果出现下面的错误：
FATAL:Peerauthenticationfailedforuser"mypguser"

请仍然修改pg_hba.conf文件，该下面行的peer为md5：
localallallmd5#replacepeerwithmd5

完成上面的修改后请重新加载postgresql：
/etc/init.d/postgresqlreload

postgres=>select*fromt1;
ERROR:permissiondeniedforrelationt1
postgres=>c-postgres
Youarenowconnectedtodatabase"postgres"asuser"postgres".
postgres=#grantselectonalltablesinschemapublictou1;
GRANT
postgres=#c-u1Youarenowconnectedtodatabase"postgres"asuser"u1".
postgres=>select*fromt1;
id|name
----+------
(0rows)

4.创建表t2
postgres=>c-postgres
Youarenowconnectedtodatabase"postgres"asuser"postgres".
postgres=#createtablet2(idserial,namevarchar(64));
CREATETABLE
postgres=#dtListofrelations
Schema|Name|Type|Owner
--------+------+-------+----------
public|t1|table|postgres
public|t2|table|postgres
(2rows)

5.验证u1的权限
postgres=#c-u1Youarenowconnectedtodatabase"postgres"asuser"u1".
postgres=>select*fromt1;
id|name
----+------
(0rows)

postgres=>select*fromt2;
ERROR:permissiondeniedforrelationt2

可见u1是有t1表的读权限，但没有t2表的读权限，这样是不是意味着每次新建表就要赋一次权限？

6.解决办法
postgres=>c-postgres
Youarenowconnectedtodatabase"postgres"asuser"postgres".
postgres=#alterdefaultprivilegesinschemapublicgrantselectontablestou1;
ALTERDEFAULTPRIVILEGES
#grant是赋予用户schema下当前表的权限
#alterdefaultprivileges是赋予用户schema下表的默认权限

postgres=#createtablet3(idserial,namevarchar(64));
CREATETABLE
postgres=#dtListofrelations
Schema|Name|Type|Owner
--------+------+-------+----------
public|t1|table|postgres
public|t2|table|postgres
public|t3|table|postgres
(3rows)

四、创建可更新用户

1.创建u2用户
postgres=#createroleu2withloginpassword'123456';
CREATEROLE

2.赋予更新权限
postgres=#alterdefaultprivilegesinschemapublicgrantselect,insert,update,deleteontablestou2;
ALTERDEFAULTPRIVILEGES

3.创建表t4
postgres=#createtablet4(idserial,namevarchar(64));CREATETABLE
postgres=#dtListofrelations
Schema|Name|Type|Owner
--------+------+-------+----------
public|t1|table|postgres
public|t2|table|postgres
public|t3|table|postgres
public|t4|table|postgres
(4rows)

4.查看权限
postgres=#c-u2Youarenowconnectedtodatabase"postgres"asuser"u2".
postgres=>insertintot4values(1,'aa');
INSERT01
postgres=>select*fromt4;
id|name
----+------
1|aa
(1row)

postgres=>updatet4setname='bb'whereid=1;
UPDATE1
postgres=>select*fromt4;
id|name
----+------
1|bb
(1row)

postgres=>deletefromt4whereid=1;
DELETE1
postgres=>select*fromt4;
id|name
----+------
(0rows)

5.序列的权限与解决办法
#在insert的时候，指定列插入，主键id是serial类型会默认走sequence的下一个值，但前面
#只赋予了表的权限，所以会出现下面的问题：

postgres=>insertintot4(name)values('aa');
ERROR:permissiondeniedforsequencet4_id_seq

#解决方法就是再赋一次sequence的值就行了
postgres=>c-postgres
Youarenowconnectedtodatabase"postgres"asuser"postgres".
postgres=#alterdefaultprivilegesinschemapublicgrantusageonsequencestou2;
ALTERDEFAULTPRIVILEGES
postgres=#createtablet5(idserial,namevarchar(64));
CREATETABLE
postgres=#c-u2
Youarenowconnectedtodatabase"postgres"asuser"u2".
postgres=>insertintot5(name)values('cc');
INSERT01postgres=>select*fromt5;
id|name
----+------
1|cc
(1row)

五、删除用户

postgres=>c-postgres
Youarenowconnectedtodatabase"postgres"asuser"postgres".
postgres=#droproleu2;
ERROR:role"u2"cannotbedroppedbecausesomeobjectsdependonit
DETAIL:privilegesfortablet5
privilegesforsequencet5_id_seq
privilegesfordefaultprivilegesonnewsequencesbelongingtorolepostgresinschemapublicprivilegesfortablet4
privilegesfordefaultprivilegesonnewrelationsbelongingtorolepostgresinschemapublic

#当我们删除用户的时候，会提示有权限依赖，所以我们要删除这些权限
postgres=#alterdefaultprivilegesinschemapublicrevokeusageonsequencesfromu2;
ALTERDEFAULTPRIVILEGES
postgres=#alterdefaultprivilegesinschemapublicrevokeselect,delete,updateontablesfromu2;
ALTERDEFAULTPRIVILEGES
postgres=#revokeselect,updateonalltablesinschemapublicfromu2;
REVOKE
postgres=#revokeusageonallsequencesinschemapublicfromu2;
REVOKE
postgres=#droproleu2;
DROPROLE

六、修改用户密码

sudo-upostgrespsql
ALTERUSERpostgresWITHPASSWORD'passwd';

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!