Re: gathering ownership and grant permissions - Mailing list pgsql-general
On Fri, Feb 16, 2018 at 2:47 PM, chris <chrisk@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;
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.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
pgsql-general by date: