Re: gathering ownership and grant permissions

On Fri, Feb 16, 2018 at 2:47 PM, chris <chrisk(at)pgsqlrocket(dot)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.