Postgresql 學習筆記（1）

標籤：sql   postgre   

一、環境

#配置遠端連線su postgres  vim /var/lib/pgsql/9.4/data/postgresql.conf   編輯設定檔  listen_address=’localhost’ 前面的注釋#去掉，並把’localhost’該為’*’;  vim /etc/postgresql/8.2/main/pg_hba.conf  host all all 192.168.1.0/24 password    password 可以設定為trust  /etc/init.d/postgresql-8.2 restart 重啟服務：還有stop start命令一樣的。如果配置錯誤可能導致無法重啟

二、文法：

psql -U postgres #進入資料庫\l #查看有哪些資料庫 \c postgresql  #選擇postgresql 這個資料庫，會提示進入串連 \dt #查看所有表\d tablename #查看某張表結構\h #查看協助

三、建立唯讀使用者

#1.建立表create table t1 ( id serial, name varchar(64) );CREATE TABLEpostgres=# \dt        List of relations Schema | Name | Type  |  Owner   --------+------+-------+---------- public | t1   | table | postgres(1 row)

2.建立使用者u1create role u1 with login password ‘123456‘; #login是賦予登入許可權，否則是不能登入的CREATE ROLE

3.賦予u1對錶的唯讀許可權（因為建立的普通使用者預設是沒有任何許可權的）postgres=# \c - u1FATAL:  Peer authentication failed for user "u2"  Previous connection kept如果出現以上資訊，則需改設定檔：vim /etc/postgresql/9.6/main/pg_hba.conf找到下面的一行：local   all             postgres                                peer改成：local   all             postgres                                trust如果出現下面的錯誤：FATAL: Peer authentication failed foruser "mypguser"請仍然修改pg_hba.conf檔案，該下面行的peer為md5：local   all    all    md5       # replace peer with md5    完成上面的修改後請重新載入postgresql：/etc/init.d/postgresql reloadpostgres=> select * from t1;ERROR:  permission denied for relation t1postgres=> \c - postgresYou are now connected to database "postgres" as user "postgres".postgres=# grant select on all tables in schema public to u1;GRANTpostgres=# \c - u1You are now connected to database "postgres" as user "u1".postgres=> select * from t1; id | name ----+------(0 rows)

4.建立表t2postgres=> \c - postgresYou are now connected to database "postgres" as user "postgres".postgres=# create table t2 ( id serial, name varchar(64) );CREATE TABLEpostgres=# \dt        List of relations Schema | Name | Type  |  Owner   --------+------+-------+---------- public | t1   | table | postgres public | t2   | table | postgres(2 rows)

5.驗證u1的許可權postgres=# \c - u1You are now connected to database "postgres" as user "u1".postgres=> select * from t1; id | name ----+------(0 rows)postgres=> select * from t2;ERROR:  permission denied for relation t2 可見u1是有t1表的讀許可權，但沒有t2表的讀許可權，這樣是不是意味著每次建立表就要賦一次許可權？

6.解決辦法postgres=> \c - postgresYou are now connected to database "postgres" as user "postgres".postgres=# alter default privileges in schema public grant select on tables to u1;ALTER DEFAULT PRIVILEGES# grant是賦予使用者schema下當前表的許可權# alter default privileges是賦予使用者schema下表的預設許可權postgres=# create table t3 ( id serial, name varchar(64) );CREATE TABLEpostgres=# \dt        List of relations Schema | Name | Type  |  Owner   --------+------+-------+---------- public | t1   | table | postgres public | t2   | table | postgres public | t3   | table | postgres(3 rows)

四、建立可更新使用者

1.建立u2使用者postgres=# create role u2 with login password ‘123456‘;CREATE ROLE

2.賦予更新許可權postgres=# alter default privileges in schema public grant select,insert,update,delete on tables to u2;ALTER DEFAULT PRIVILEGES

3.建立表t4postgres=# create table t4 ( id serial, name varchar(64) );CREATE TABLEpostgres=# \dt        List of relations Schema | Name | Type  |  Owner   --------+------+-------+---------- public | t1   | table | postgres public | t2   | table | postgres public | t3   | table | postgres public | t4   | table | postgres(4 rows)

4.查看許可權postgres=# \c - u2You are now connected to database "postgres" as user "u2".postgres=> insert into t4 values ( 1, ‘aa‘ );INSERT 0 1postgres=> select * from t4; id | name ----+------  1 | aa(1 row)postgres=> update t4 set name = ‘bb‘ where id = 1;UPDATE 1postgres=> select * from t4; id | name ----+------  1 | bb(1 row)postgres=> delete from t4 where id = 1;DELETE 1postgres=> select * from t4; id | name ----+------(0 rows)

5.序列的許可權與解決辦法# 在insert的時候，指定列插入，主鍵id是serial類型會預設走sequence的下一個值，但前面# 只賦予了表的許可權，所以會出現下面的問題：postgres=> insert into t4 ( name ) values ( ‘aa‘ );ERROR:  permission denied for sequence t4_id_seq# 解決方案就是再賦一次sequence的值就行了postgres=> \c - postgresYou are now connected to database "postgres" as user "postgres".postgres=# alter default privileges in schema public grant usage on sequences to u2;ALTER DEFAULT PRIVILEGESpostgres=# create table t5 ( id serial, name varchar(64) );CREATE TABLEpostgres=# \c - u2You are now connected to database "postgres" as user "u2".postgres=> insert into t5 ( name ) values ( ‘cc‘ );INSERT 0 1postgres=> select * from t5; id | name ----+------  1 | cc(1 row)

五、刪除使用者

postgres=> \c - postgresYou are now connected to database "postgres" as user "postgres".postgres=# drop role u2;ERROR:  role "u2" cannot be dropped because some objects depend on itDETAIL:  privileges for table t5privileges for sequence t5_id_seqprivileges for default privileges on new sequences belonging to role postgres in schema publicprivileges for table t4privileges for default privileges on new relations belonging to role postgres in schema public# 當我們刪除使用者的時候，會提示有許可權依賴，所以我們要刪除這些許可權postgres=# alter default privileges in schema public revoke usage on sequences from u2;ALTER DEFAULT PRIVILEGESpostgres=# alter default privileges in schema public revoke select,insert,delete,update on tables from u2;ALTER DEFAULT PRIVILEGESpostgres=# revoke select,insert,delete,update on all tables in schema public from u2;REVOKEpostgres=# revoke usage on all sequences in schema public from u2;REVOKEpostgres=# drop role u2;DROP ROLE

六、修改使用者密碼

sudo -u postgres psqlALTER USER postgres WITH PASSWORD ‘passwd‘;

Postgresql 學習筆記（1）