2019年8月13日 星期二

[ 常見問題 ] How do you create a read-only user in PostgreSQL?

Source From Here 
Question 
I'd like to create a user in PostgreSQL that can only do SELECTs from a particular database. In MySQL the command would be: 
  1. GRANT SELECT ON mydb.* TO 'xxx'@'%' IDENTIFIED BY 'yyy';  
What is the equivalent command or series of commands in PostgreSQL? 

How-To 

Grant usage/select to a single table 
If you only grant CONNECT to a database, the user can connect but has no other privileges. You have to grant USAGE on namespaces (schemas) and SELECT on tables and views individually like so: 
  1. GRANT CONNECT ON DATABASE mydb TO xxx;  
  2. -- This assumes you're actually connected to mydb..  
  3. GRANT USAGE ON SCHEMA public TO xxx;  
  4. GRANT SELECT ON mytable TO xxx;  
Multiple tables/views (PostgreSQL 9.0+) 
In the latest versions of PostgreSQL, you can grant permissions on all tables/views/etc in the schema using a single command rather than having to type them one by one: 
  1. GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx;  
This only affects tables that have already been created. More powerfully, you can automatically have default roles assigned to new objects in future: 
  1. ALTER DEFAULT PRIVILEGES IN SCHEMA public  
  2.    GRANT SELECT ON TABLES TO xxx;  
Note that by default this will only affect objects (tables) created by the user that issued this command: although it can also be set on any role that the issuing user is a member of. However, you don't pick up default privileges for all roles you're a member of when creating new objects... so there's still some faffing around. If you adopt the approach that a database has an owning role, and schema changes are performed as that owning role, then you should assign default privileges to that owning role. IMHO this is all a bit confusing and you may need to experiment to come up with a functional workflow. 

Multiple tables/views (PostgreSQL versions before 9.0) 
To avoid errors in lengthy, multi-table changes, it is recommended to use the following 'automatic' process to generate the required GRANT SELECT to each table/view: 
  1. SELECT 'GRANT SELECT ON ' || relname || ' TO xxx;'  
  2. FROM pg_class JOIN pg_namespace ON pg_namespace.oid = pg_class.relnamespace  
  3. WHERE nspname = 'public' AND relkind IN ('r', 'v', 'S');  
This should output the relevant GRANT commands to GRANT SELECT on all tables, views, and sequences in public, for copy-n-paste love. Naturally, this will only be applied to tables that have already been created. 


Supplement 
* oinopion/read-access.sql 
  1. -- Create a group  
  2. CREATE ROLE readaccess;  
  3.   
  4. -- Grant access to existing tables  
  5. GRANT USAGE ON SCHEMA public TO readaccess;  
  6. GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;  
  7.   
  8. -- Grant access to future tables  
  9. ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess;  
  10.   
  11. -- Create a final user with password  
  12. CREATE USER tomek WITH PASSWORD 'secret';  
  13. GRANT readaccess TO tomek;  
* PostgreSQL Doc - Chapter 18. Database Roles and Privileges 
To determine the set of existing roles, examine the pg_roles system catalog, for example 
  1. SELECT rolname FROM pg_roles;  

* PostgreSQL - ALTER USER 
Change a user's password: 
  1. ALTER USER davide WITH PASSWORD 'hu8jmn3';  


沒有留言:

張貼留言

[Git 文章收集] Differences between git merge and git rebase

Source From  Here Preface Merging and rebasing are the two most popular way to applying changes from one branch into another one. They bot...