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


沒有留言:

張貼留言

[ Py DS ] Ch5 - Machine Learning (Part1)

Source From  Here   What Is Machine Learning?   Before we take a look at the details of various machine learning methods, let’s start by ...