rkaneko/psql-grant.md

Last active December 26, 2019 13:18

Star (0) You must be signed in to star a gist
Fork (0) You must be signed in to fork a gist

Learn more about clone URLs
Clone this repository at <script src="https://gist.github.com/rkaneko/32ec0a93cc00e29a4cb181fc3a689369.js"></script>
Save rkaneko/32ec0a93cc00e29a4cb181fc3a689369 to your computer and use it in GitHub Desktop.

psql grant, logs configurations

Raw

# display privileges
SELECT grantee, privilege_type FROM information_schema.role_table_grants WHERE table_name='some_table_name';

  grantee  | privilege_type
-----------+----------------
 root      | INSERT
 root      | SELECT
 root      | UPDATE
 root      | DELETE
 root      | TRUNCATE
 root      | REFERENCES
 root      | TRIGGER
 
 
# add privileges for (select, insert, update, delete) of all tables to a user
GRANT select, insert, update, delete on all tables in schema public to some_user_name;

GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO some_user_name;

Raw

psql-set-echo-query-logs.md

# Show log_statement current config
SELECT current_setting('log_statement');
 current_setting
-----------------
 none
(1 row)

# Turn on echo query log
ALTER DATABASE databse_name set log_statement = 'all';

# Turn off echo query log
ALTER DATABASE database_name set log_statement = 'none';

# Reload conf
select pg_reload_conf();

rkaneko/psql-grant.md

References