On Fri, Feb 16, 2018 at 2:47 PM, chris <chr...@pgsqlrocket.com> wrote:
> HI, > > I would like to know if there is a better way to grab the grant > permissions as well as the "owner to" of a table. > > I can currently do this through a pg_dumb with greps for "^grant" and > "^alter" but than I need to do a word search of those lines looking for the > specific answers which gets much more involved. > > I essentially need to know what grant command was ran and use that grant > permission to set to a variable for a script. > > Ex: GRANT ALL ON TABLE testing TO bob; then set only the "all" to a > variable. > > And then same for the ALTER .... OWNER TO bob. > > This is on postgresl 9.6. > > Thank you, > > Chris > > > *>... is a better way to grab the grant permissions as well as the "owner to" of a table. * *Chris, see if the query below will help. Note, you need to execute as a superuser.SELECT n.nspname, c.relname, o.rolname AS owner, array_to_string(ARRAY[c.relacl], '|') as permits FROM pg_class c JOIN pg_namespace n ON (n.oid = c.relnamespace) JOIN pg_authid o ON (o.oid = c.relowner)WHERE n.nspname not like 'pg_%' AND n.nspname not like 'inform_%' AND relkind = 'r'ORDER BY 1;* -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
