gathering ownership and grant permissions

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;*

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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

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 Fri, Feb 16, 2018 at 3:50 PM, chris <chrisk@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 <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;*

--
*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.