5

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.

Community
  • 1
  • 1
gingerlime
  • 5,206
  • 4
  • 37
  • 66

1 Answers1

6

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;
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Thanks for the quick reply. So looking at the first option: instead of having one 'master' role with two users, I will have two users each granted on the other? e.g. `GRANT user1 TO user2; GRANT user2 TO user1`? And this will have to be manually executed every time before a 'switch'? If I use the `DEFAULT PRIVILEGES` however I can do it just once. Right? – gingerlime Apr 18 '12 at 16:40
  • [`GRANT`](http://www.postgresql.org/docs/current/interactive/sql-grant.html) means to permanently grant privileges and is needed once only. – Erwin Brandstetter Apr 18 '12 at 17:13
  • sorry for being daft, but I don't understand how this would work. It seems that grants are hierarchical, e.g. `GRANT user1 TO user2`, so user2 will have the same permissions as user1, but not the other way around. I need both users to have exactly the same privileges regardless of which user created a table. Perhaps I'm missing something. – gingerlime Apr 18 '12 at 20:30
  • @YoavAner: I added a proposal for that to my answer. – Erwin Brandstetter Apr 19 '12 at 08:24
  • Thanks for not giving up on me. I tried your suggestion, but it still doesn't work with rails. Seems like rails insists on the table being owned by the user making the change when you run a migration. It throws this kind of error `PG::Error: ERROR: must be owner of relation {table_name}` – gingerlime Apr 19 '12 at 08:45
  • It's actually not rails, but probably Postgres that complains. Checking this manually from within postgres, I get the same error when trying to run an `ALTER TABLE {table_name}...` command, even after granting `ALTER DEFAULT PRIVILEGES... GRANT ALL ON TABLES...` similarly to the way presented above. – gingerlime Apr 19 '12 at 08:51
  • Thanks for the 2nd update to the answer, but this was my starting point when asking the question - how do I make rails create tables with a different owner than the postgresql user... – gingerlime Apr 19 '12 at 13:07