Search Postgresql Archives

Re: gathering ownership and grant permissions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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 <chrisk@xxxxxxxxxxxxxxx> 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.




[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux