I'm sorry I realized that I only need to know which users have permissions to the table which I can do through
$ psql -t
SELECT grantee FROM information_schema.role_table_grants WHERE table_name='table_name' GROUP BY grantee;
thanks!
On 02/16/2018 01:13 PM, chris wrote:
Thanks for the quick response.
That does not work for what I need because I only need the owner and permissions of one table, I need the grant to look like the output that pg_dump displays.
ex:
GRANT ALL ON TABLE testing_cdc TO bob;
-- -- PostgreSQL database dump complete --
I need a way which my script can isolate the owner's name and set it to a variable on its own. Same with grant.
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.
>I'm sorry I realized that I only need to know which users have permissions to the table
No need to apologize. No hurt, no foul. Thank you for your query.
--
Melvin Davidson I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.