On Fri, Feb 16, 2018 at 3:50 PM, chris <chr...@pgsqlrocket.com> wrote:
> 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. > > Thanks > > On 02/16/2018 01:05 PM, Melvin Davidson wrote: > > > > 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. > > > > *>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.
