艾瑞克的 Hexo 空間

筆記-在postgresql 建立 read only 權限的使用者 create a readonly user in postgresql

PostgreSQL

Create a user with read only permissions:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
BEGIN;
CREATE ROLE user_name LOGIN PASSWORD 'secretpassword';
GRANT CONNECT ON DATABASE database_name TO user_name;
GRANT USAGE ON SCHEMA public TO user_name;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO user_name;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO user_name;
COMMIT;

~這指令雖然可以執行 SQL COMMANDS ,但是看不到tables和欄位~~

修正:這樣指定權限後,無法執行 SQL COMMAND

https://i.imgur.com/U0mZL90.png

https://i.imgur.com/xt0Y95O.png

應該要跟下圖一樣,可以看到table/columns

https://i.imgur.com/dItrV5b.png

已經在官方論壇發問,看看會不會有回應

用底下的指令,可以建立出可以查詢的帳號,但是還是沒有object viewer

~~

psql

\c neonexus

CREATE USER dbr WITH PASSWORD ‘dbrpassword’;

GRANT CONNECT ON DATABASE neonexus TO dbr;

GRANT USAGE ON SCHEMA public TO dbr;

GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO dbr;

GRANT SELECT ON ALL TABLES IN SCHEMA public to dbr;

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO dbr;

~~

刪除 postgresql 內的使用者

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
### Drop PostgreSQL User #####
DROP OWNED by user_name
# drop default privileges in postgres
postgres=# drop owned by user_name;
# drop default privileges in xyz_dev
postgres=# \c xyz_dev
# drop default privileges in xyz_dev
xyz_dev=# drop owned by user_name;
# drop the user
xyz_dev=# drop user user_name;
avatar
筆記-用find找出日期區間內的所有檔案並計算容量 find files in period and calcualte size

  1. 1. PostgreSQL
    1. 1.1. 用底下的指令,可以建立出可以查詢的帳號,但是還是沒有object viewer
    2. 1.2. 刪除 postgresql 內的使用者