Building on how-do-you-create-a-read-only-user-in-postgresql, my situation is more complex. Each hour, new INHERITED tables are created. Any (easy?) programmatic way to all our read-only user privileges on these as well? Trigger? Cronjob that looks for new ones?
+2
A:
When you create a new table, make sure the access privileges are set as well.
Frank Heikens
2010-06-18 21:25:50
that suggests the "trigger" route, then? Ick.
Gregg Lind
2010-06-18 21:36:20
No, not a trigger, is not going to work either (pgSQL doesn't have DDL triggers yet). When do you create the new table? That's also the moment to set the privileges. Table don't fall from the sky, you have to create them. Right after the CREATE statement, you do a GRANT statement for the privileges.
Frank Heikens
2010-06-18 21:40:42
Yes, I'm doing it from SqlAlchemy, and can do it from there, but it make those processes very tightly coupled, when really, they should n't have to be :) Here's to 9.0 and "GRANT select on ALL TABLES IN SCHEMA"
Gregg Lind
2010-06-18 21:55:56
AFAICT "GRANT ... on ALL TABLES" in 9.0 will not work for you. "ALTER DEFAULT PRIVILEGES" probably will do tho.
Milen A. Radev
2010-06-19 00:36:27