Thread: Anticipatory privileges
If I am reading the (7.4) docs correctly, privileges can be granted only with respect to tables that exist at the time the GRANT command is given - there is no GRANT ALL ON * TO PUBLIC or some such that would result in subsequently created tables having public privileges. Is this so? Thanks. - John D. Burger MITRE
John D. Burger wrote: > If I am reading the (7.4) docs correctly, privileges can be granted > only with respect to tables that exist at the time the GRANT command > is given - there is no > > GRANT ALL ON * TO PUBLIC > > or some such that would result in subsequently created tables having > public privileges. > > Is this so? Yes. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera wrote: >> If I am reading the (7.4) docs correctly, privileges can be granted >> only with respect to tables that exist at the time the GRANT command >> is given > Yes. In fact, I have to individually grant access to each table, and any associated sequences, yes? How dangerous is it to UPDATE pg_class directly, perhaps copying the relacl column for a table that I've done by hand with GRANT. I'm thinking something like this: => grant all on annotations to public; => update pg_class set relacl = (select relacl from pg_class where relname = 'annotations') where relnamespace = (select oid from pg_namespace where nspname = 'public'); This will "grant" access to indexes and other stuff that may be unnecessary, but is this a sound approach? (By the way, are there in fact any other kinds of objects that I may need to allow access to, other than tables and sequences?) Another solution to my access control issues is to change the owner of the tables and sequences. Can I safely do this with an UPDATE on pg_class? Thanks, and sorry if these are dumb questions, but I haven't been able to glean the answers directly from the docs. - John Burger MITRE
"John D. Burger" <john@mitre.org> writes: > How dangerous is it to UPDATE pg_class > directly, perhaps copying the relacl column for a table that I've > done by hand with GRANT. You can do it, and it will seem to work. However, unless you also make entries in pg_shdepend, bad things will happen if you later drop any of the users mentioned in the ACL. Your code will also be vulnerable to breakage in future releases if we change any of these details. A better approach is to write a plpgsql function that assembles and EXECUTEs the required GRANT commands. regards, tom lane
Tom Lane wrote: >> How dangerous is it to UPDATE pg_class >> directly, perhaps copying the relacl column for a table that I've >> done by hand with GRANT. > > You can do it, and it will seem to work. However, unless you also > make > entries in pg_shdepend, bad things will happen if you later drop > any of > the users mentioned in the ACL. Your code will also be vulnerable to > breakage in future releases if we change any of these details. > > A better approach is to write a plpgsql function that assembles and > EXECUTEs the required GRANT commands. Okay, thanks - guess it's time to learn some real plpgsql control structures. - John Burger MITRE
On Feb 17, 2007, at 12:12 PM, John D. Burger wrote: >> >> A better approach is to write a plpgsql function that assembles and >> EXECUTEs the required GRANT commands. > > Okay, thanks - guess it's time to learn some real plpgsql control > structures. You can find some help here: http://pgedit.com/tip/postgresql/access_control_functions John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL