PostgreSQL

Create a user with read only permissions:

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

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

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

用底下的指令，可以建立出可以查詢的帳號，但是還是沒有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 內的使用者

### 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;