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:50:53 |
Message-ID: | 0c307f7a-56ae-cee8-61cb-a1ebcf6c2fde@pgsqlrocket.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(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 | Vitaliy Garnashevich | 2018-02-16 22:36:57 | shared_buffers 8GB maximum |
Previous Message | Basques, Bob (CI-StPaul) | 2018-02-16 20:26:04 | Re: Database health check/auditing |