Postgres 9.6中GRANT ROLE及DB所有者权限异常问题咨询

Let’s tackle your Postgres 9.6 permission headaches one by one—this stuff can be counterintuitive at first, but I’ve sorted through similar issues plenty of times!

When you grant the db_owner role to another user, it doesn’t automatically cover all existing objects or future objects unless you set up a few key things. Here’s what you might have missed:

• You didn’t sync permissions for existing objects
  The db_owner role only applies to objects created after you grant the role. Any tables, sequences, views, or functions that existed before won’t inherit these permissions automatically. You’ll need to manually grant access to them, for example:

  -- Grant access to all tables in the public schema
  GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO your_granted_user;
  -- Don't forget sequences (critical for auto-increment columns)
  GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO your_granted_user;
  -- And functions if you use them
  GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO your_granted_user;
  

• You didn’t set default privileges for future objects
  To make sure any new objects created after the role grant automatically respect the db_owner permissions, you need to configure default privileges. This ensures you don’t have to repeat manual grants every time someone creates a new object:

  -- Apply to objects created by the current user
  ALTER DEFAULT PRIVILEGES GRANT ALL PRIVILEGES ON TABLES TO db_owner;
  ALTER DEFAULT PRIVILEGES GRANT ALL PRIVILEGES ON SEQUENCES TO db_owner;
  ALTER DEFAULT PRIVILEGES GRANT ALL PRIVILEGES ON FUNCTIONS TO db_owner;
  
  -- If you want to cover objects created by another specific user (like user2)
  ALTER DEFAULT PRIVILEGES FOR ROLE user2 GRANT ALL PRIVILEGES ON TABLES TO db_owner;
  

• Schema-level USAGE permissions are locked down
  Even with db_owner access, if the schema containing your objects doesn’t have USAGE permissions granted to the role (or the user), you’ll hit access blocks. Check and fix this with:

  GRANT USAGE ON SCHEMA your_target_schema TO db_owner;
  

• You didn’t refresh your database session
  Permission changes sometimes don’t take effect in active sessions. Log out and log back in with the user you granted the role to—this forces Postgres to reload the latest permissions.

This boils down to Postgres’s default security behavior—object ownership is strict by design. Here’s the breakdown:

• By default, only object creators have full access
  When user2 creates a table (or any object), Postgres assigns exclusive ownership to user2. Even the database owner (db_owner role included) doesn’t get automatic access to objects created by other users. This is intentional to prevent unintended cross-user access.

• No default privileges were set for user2’s objects
  If you didn’t run ALTER DEFAULT PRIVILEGES FOR ROLE user2 (like mentioned in the first question), any objects user2 creates will stay locked to user2 alone—no inherited access for db_owner or other users unless you explicitly grant it.

• Schema USAGE permissions are missing
  If user2 created objects in a schema that doesn’t grant USAGE to db_owner, user1 and user3 won’t even be able to see the objects, let alone access them. Double-check the schema permissions as outlined earlier.

• Role inheritance might be disabled (rare, but possible)
  If user3 was granted db_owner but has INHERIT disabled, they won’t inherit the role’s permissions. Verify and fix this with:

  ALTER ROLE user3 INHERIT;
  

Quick Fixes for This Issue #

To give user1 and user3 access to user2’s existing objects:

GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user1, user3;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO user1, user3;

To prevent this from happening for future objects user2 creates:

ALTER DEFAULT PRIVILEGES FOR ROLE user2 GRANT ALL PRIVILEGES ON TABLES TO db_owner;
ALTER DEFAULT PRIVILEGES FOR ROLE user2 GRANT ALL PRIVILEGES ON SEQUENCES TO db_owner;

内容的提问来源于stack exchange，提问作者Richard PB