ansaurus

Question

Two owners of the same PostgreSQL database

Answer 1

A:

Do the users really need to be "owners", or can they just both be super-users on that database?

Tim Drisdelle 2010-01-08 09:46:32

If them both being superusers on the database would achieve the same thing, then yes that would work :)How would I achieve that?

David Reynolds 2010-01-08 09:54:54

Well, the superuser is across the whole server, not just on the single database. Not sure if this is still a good solution for you...

Tim Drisdelle 2010-01-08 10:06:01

No, that wouldn't work then.

David Reynolds 2010-01-08 10:21:05

Answer 2

+1 A:

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."

Tim Drisdelle 2010-01-08 10:14:02

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 2010-01-08 10:21:54

But you can have 'admin' with rights to do everything on all tables, which would include tables created by other users in the group.

Tim Drisdelle 2010-01-08 10:31:47

But you still have to manually set the admin role for each connection you make with the user don't you?

David Reynolds 2010-01-08 10:34:59

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. "

Tim Drisdelle 2010-01-08 10:42:06

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 2010-01-08 11:06:09

>>> 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.

Tim Drisdelle 2010-01-08 13:08:40

Answer 3

+2 A:

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 2010-01-08 10:27:27

Yes, that appears to do what I want. Any security implications of working this way that you can think of?

David Reynolds 2010-01-08 11:21:59

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 2010-01-08 13:12:10

ansaurus

tags:

views:

answers:

Two owners of the same PostgreSQL database

related questions