how do I set table ownership in rails with postgresql?

Question

I'm very new to rails, so hope this is not a stupid question.

I am trying to make rails use a postgresql database with two different user accounts. I want however to be able to switch between those accounts, so they have the same permissions on the database (without both of them being a postgresql superuser ideally).

I created a NOLOGIN role in postgres, which is the owner of the database. Then assigned two user accounts to this role. However, whenever I do rake db:migrate, it will make the user account (and not the role) the owner of the table. If I then switch users, the second user won't have the right permissions on the tables created by the first user.

The reason for having two account is to be able to switch quickly, e.g. when I need to change the password with no downtime.

Is there a way to tell rails to use a specific role as the owner when creating tables? (or perhaps there's a better way to accomplish this without two accounts in the same postgresql role?)

UPDATE: So far I found this solution to run manually as an external script and change ownership of all tables to the NOLOGIN role, but am hoping for something more elegant or that fits well with rails.

Answer 1

You can do quite a bit on the PostgreSQL side:

If you want role2 to have all privileges that role1 holds, you can just:

GRANT role1 TO role2;

And / or, with PostgreSQL 9.0 or later, you can instruct PostgreSQL to set default privileges on new objects:

ALTER DEFAULT PRIVILEGES allows you to set the privileges that will be applied to objects created in the future. (It does not affect privileges assigned to already-existing objects.) Currently, only the privileges for tables (including views and foreign tables), sequences, and functions can be altered.

You can do that for roles or schemas. For your case you could:

ALTER DEFAULT PRIVILEGES FOR ROLE role1 GRANT SELECT ON TABLES TO role2;

---

If you want multiple distinct roles to share a set of privileges, I would grant all permissions on objects to one non-login role (used to be called GROUP) - role0 in my example below. Use DEFAULT PRIVILEGES for that and don't forget about pre-existing objects, including sequences (I tend to forget those).
Then GRANT role0 to those other roles.

Along these lines:

ALTER DEFAULT PRIVILEGES FOR ROLE role1 GRANT SELECT ON TABLES TO role0;
ALTER DEFAULT PRIVILEGES FOR ROLE role2 GRANT SELECT ON TABLES TO role0;
...

GRANT role0 TO role1;
GRANT role0 TO role2;

This way you can easily GRANT additional privileges to just one role if need should be. You can add as many roles as you like to this setup.

Many DML statements require ownership of the object. I quote the manual on ALTER TABLE:

You must own the table to use ALTER TABLE.

Being member of the role that owns the table work as well. So, in the above example, if you make role0 own tables, role1 and role2 can use ALTER TABLE. Execute:

ALTER TABLE tbl OWNER TO user0;

for every table you create.

Or you slip into the role before you run the CREATE commands:

SET ROLE user0;
CREATE ...;
CREATE ...;
RESET ROLE;

If you lost track who you are:

SELECT current_user, session_user;