How to set table attribute in rails using postgresql?

Question

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

I am trying to make rails use a postgresql database with two different user accounts. Nevertheless, I want to be able to switch between these accounts, so they have the same access rights to the database (without them, ideally this is the superuser of postgresql).

I created a role NOLOGINin postgres that owns the database. Then assigned two accounts for this role. However, when 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 will not have permission to access the tables created by the first user.

The reason for having two accounts is the ability to switch quickly, for example. when I need to change the password without downtime.

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

UPDATE: so far I have found this solution to run manually as an external script and change the ownership of all tables to the NOLOGIN role, but I hope for something more elegant or fits nicely on the rails.

Answer 1

You can do a bit on the PostgreSQL side:

, role2 , role1, :

GRANT role1 TO role2;

/, PostgreSQL 9.0 , PostgreSQL :

ALTER DEFAULT PRIVILEGES , , . ( , .) ( ), , .

. :

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

---

, , - ( GROUP) - role0 . DEFAULT PRIVILEGES , (, , ).
GRANT role0 .

:

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;

GRANT , . , .

DML . ALTER TABLE:

, ALTER TABLE.

, , . , , role0, role1 role2 ALTER TABLE. :

ALTER TABLE tbl OWNER TO user0;

.

CREATE:

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

, :

SELECT current_user, session_user;