From: | chris <chrisk(at)pgsqlrocket(dot)com> |
---|---|
To: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
Cc: | Postgres General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: gathering ownership and grant permissions |
Date: | 2018-02-16 20:13:32 |
Message-ID: | 15f84768-27ce-593f-9120-bc85ec7a4fdf@pgsqlrocket.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)pgsqlrocket(dot)com
> <mailto:chrisk(at)pgsqlrocket(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tim Cross | 2018-02-16 20:17:47 | Re: Database health check/auditing |
Previous Message | Melvin Davidson | 2018-02-16 20:05:35 | Re: gathering ownership and grant permissions |