如何在Apache Zeppelin中配置PostgreSQL用户权限映射

Alright, let's break this down step by step—since you already have your PostgreSQL permissions locked in and Zeppelin users created, the key here is to map each Zeppelin user to their corresponding PostgreSQL database user via dedicated interpreter instances. Here's how to do it properly:

1. Create Per-User PostgreSQL Interpreter Instances #

Zeppelin uses shared interpreters by default, which would let all users inherit the admin PostgreSQL user's permissions if you stick with a single connection. To fix this, we'll make separate interpreter instances for each Zeppelin user:

• Log into Zeppelin as an admin, then go to the Interpreter page (click your username in the top right → Interpreter).
• Find the postgresql interpreter in the list, then click Create to make a new instance:
  • Give it a clear name like postgresql-user1
  • Fill in the connection parameters to match your PostgreSQL user1 account:
    • default.driver: org.postgresql.Driver
    • default.url: jdbc:postgresql://<your-pg-host>:5432/somedatabase (replace <your-pg-host> with your actual PostgreSQL server address)
    • default.user: user1
    • default.password: <user1's-postgresql-password>
  • Scroll down to the Permissions section, remove any default users/groups, then add only the Zeppelin user1 to this interpreter's allowed list.
  • Save the interpreter.
• Repeat the exact same process to create a postgresql-user2 instance, using PostgreSQL's user2 credentials and restricting permissions to Zeppelin's user2.

2. Verify Permission Isolation #

Now test to make sure the restrictions work as expected:

• Log into Zeppelin as user1, create a new notebook, and select the postgresql-user1 interpreter. Run these queries:

  -- This should work fine (user1 has access to this table)
  SELECT * FROM table1_for_user1;
  
  -- This should throw a permission error (user1 can't access table2)
  SELECT * FROM table2_for_user2;
  

• Log in as user2, use the postgresql-user2 interpreter, and run the reverse queries—you should only be able to access table2_for_user2.

3. Optional: Keep an Admin Interpreter for Full Access #

If you want your Zeppelin admin user to still access both tables, keep the original postgresql interpreter (configured with the PostgreSQL admin credentials) and restrict its permissions to only the Zeppelin admin user.

Key Things to Remember #

• Never share a single PostgreSQL connection across multiple Zeppelin users—this breaks all permission controls you set up in PostgreSQL.
• Double-check that your PostgreSQL permissions are correctly applied (run these as PostgreSQL admin):

  -- Confirm user1's permissions
  GRANT SELECT ON table1_for_user1 TO user1;
  REVOKE ALL ON table2_for_user2 FROM user1;
  
  -- Confirm user2's permissions
  GRANT SELECT ON table2_for_user2 TO user2;
  REVOKE ALL ON table1_for_user1 FROM user2;
  

• Make sure Zeppelin's interpreter instances are properly restricted to their target users—this prevents cross-user access to sensitive data.

内容的提问来源于stack exchange，提问作者Alexander SKyzZz