16

Is it possible with Postgresql to create a database which has 2 users which act like owners to the database?

I can create a group role and add both users to that group, and then make the group the owner of the database, but this requires both users to be have to manually set their role on every connection to make any tables they have created accessible to the other user. Is there any way to make the group be the default role for a user each time they log in or any other way to achieve the same thing?

ekad
  • 14,436
  • 26
  • 44
  • 46
David Reynolds
  • 3,141
  • 3
  • 18
  • 19

2 Answers2

18

No, each database can only have one owner. As stated previously you can have more than one superuser, or you can grant permissions specifically to group roles that are then inherited.

You might want to look at http://blog.hagander.net/archives/70-Faking-the-dbo-role.html, for a way to fake something similar to what you're asking for. It's not perfect, but it might be good enough for you. It should be able to solve the object-ownership problem at least.

Magnus Hagander
  • 23,890
  • 5
  • 56
  • 43
  • Yes, that appears to do what I want. Any security implications of working this way that you can think of? – David Reynolds Jan 08 '10 at 11:21
  • No, that should be fine. I assume that if you make them admins, they are trusted not to intentionally break things. Like, the user can still revert his role to the original one if he has malicious intent. – Magnus Hagander Jan 08 '10 at 13:12
8

Ah, found it: PostgreSQL Docs: Chapter 20. Database Roles and Privileges

"member roles that have the INHERIT attribute automatically have use of privileges of roles they are members of."

CREATE ROLE joe LOGIN INHERIT;
CREATE ROLE admin NOINHERIT;
GRANT admin TO joe;

"Immediately after connecting as role joe, a database session will have use of privileges granted directly to joe plus any privileges granted to admin, because joe "inherits" admin's privileges."

Timothy
  • 2,457
  • 19
  • 15
  • True, but tables created by joe are owned by joe and not admin, which means any other users in the group don't have access to them. – David Reynolds Jan 08 '10 at 10:21
  • But you can have 'admin' with rights to do everything on all tables, which would include tables created by other users in the group. – Timothy Jan 08 '10 at 10:31
  • But you still have to manually set the admin role for each connection you make with the user don't you? – David Reynolds Jan 08 '10 at 10:34
  • No, that's what the INHERIT means. Go read that documentation page on Roles and Privileges - it's clear. "The members of a role can use the privileges of the group role in two ways. First, every member of a group can explicitly do SET ROLE to temporarily "become" the group role." ... "Second, member roles that have the INHERIT attribute automatically have use of privileges of roles they are members of. " – Timothy Jan 08 '10 at 10:42
  • Yes, they have the privileges of the group, but they are still the sole owner of any tables they create. I have investigated this avenue, in fact I describe it in the original question. Ideally what I need to happen is that set role admin is automatically executed on each client connection. – David Reynolds Jan 08 '10 at 11:06
  • >>> member roles that have the INHERIT attribute automatically have use of privileges of roles they are members of <<< which means that they do not need to explicitly do SET ROLE. – Timothy Jan 08 '10 at 13:08