{"id":508,"date":"2014-10-06T13:40:12","date_gmt":"2014-10-06T13:40:12","guid":{"rendered":"http:\/\/shirishranjit.com\/blog1\/?page_id=508"},"modified":"2021-05-28T15:41:27","modified_gmt":"2021-05-28T19:41:27","slug":"postgres-database-stuff","status":"publish","type":"page","link":"https:\/\/shirishranjit.com\/blog1\/technical-posts\/postgres-database-cheatsheet\/postgres-database-stuff","title":{"rendered":"Postgres SQL Cheatsheet"},"content":{"rendered":"\n<ol class=\"wp-block-list\"><li>List all the schema in the database<br \/><code>\\dt *.*<\/code><br \/><code>\\dt schema.*<\/code><\/li><li>List of Sequences<br \/><code>\\ds *.*<\/code><\/li><li>Run a sql file<br \/><code>psql -f filename.sql -U user<\/code><\/li><li>Grants and revokes<\/li><\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>--ACCESS DB\nREVOKE CONNECT ON DATABASE nova FROM PUBLIC;\nGRANT  CONNECT ON DATABASE nova  TO user;\n\n--ACCESS SCHEMA\nREVOKE ALL     ON SCHEMA public FROM PUBLIC;\nGRANT  USAGE   ON SCHEMA public  TO user;\n\n--ACCESS TABLES\nREVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC ;\nGRANT SELECT                         ON ALL TABLES IN SCHEMA public TO read_only ;\nGRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write ;\nGRANT ALL                            ON ALL TABLES IN SCHEMA public TO admin ;<\/code><\/pre>\n\n\n\n<ol class=\"wp-block-list\"><li>&nbsp;<\/li><\/ol>\n\n\n\n<h1 class=\"wp-block-heading\">Reference<\/h1>\n\n\n\n<ul class=\"wp-block-list\"><li><a title=\"http:\/\/www.linuxinsight.com\/optimize_postgresql_database_size.html\" href=\"http:\/\/www.linuxinsight.com\/optimize_postgresql_database_size.html\">http:\/\/www.linuxinsight.com\/optimize_postgresql_database_size.html<\/a> Tidbits on optimizing postgres database.<\/li><li><a href=\"https:\/\/help.ubuntu.com\/community\/PostgreSQL\">https:\/\/help.ubuntu.com\/community\/PostgreSQL <\/a>Postgres Introduction on install and setup<\/li><li>https:\/\/medium.com\/engineering-on-the-incline\/securing-postgresql-databases-in-a-multi-user-environment-4b992dc785fb &#8211; User Role &#8211; setup.<\/li><li>\u00a0https:\/\/www.azureblue.io\/how-to-manage-psql-db-permissions-with-aad-groups\/<\/li><li><\/li><\/ul>\n<div class=\"twttr_buttons\"><div class=\"twttr_twitter\">\n\t\t\t\t\t<a href=\"http:\/\/twitter.com\/share?text=Postgres+SQL+Cheatsheet\" class=\"twitter-share-button\" data-via=\"\" data-hashtags=\"\"  data-size=\"default\" data-url=\"https:\/\/shirishranjit.com\/blog1\/technical-posts\/postgres-database-cheatsheet\/postgres-database-stuff\"  data-related=\"\" target=\"_blank\">Tweet<\/a>\n\t\t\t\t<\/div><div class=\"twttr_followme\">\n\t\t\t\t\t\t<a href=\"https:\/\/twitter.com\/shiranjit\" class=\"twitter-follow-button\" data-size=\"default\"  data-show-screen-name=\"false\"  target=\"_blank\">Follow me<\/a>\n\t\t\t\t\t<\/div><\/div>","protected":false},"excerpt":{"rendered":"<p>List all the schema in the database\\dt *.*\\dt schema.* List of Sequences\\ds *.* Run a sql filepsql -f filename.sql -U user Grants and revokes &nbsp; Reference http:\/\/www.linuxinsight.com\/optimize_postgresql_database_size.html Tidbits on optimizing postgres database. https:\/\/help.ubuntu.com\/community\/PostgreSQL Postgres Introduction on install and setup https:\/\/medium.com\/engineering-on-the-incline\/securing-postgresql-databases-in-a-multi-user-environment-4b992dc785fb &hellip; <a href=\"https:\/\/shirishranjit.com\/blog1\/technical-posts\/postgres-database-cheatsheet\/postgres-database-stuff\">Continue reading <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"parent":489,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-508","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/shirishranjit.com\/blog1\/wp-json\/wp\/v2\/pages\/508"}],"collection":[{"href":"https:\/\/shirishranjit.com\/blog1\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/shirishranjit.com\/blog1\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/shirishranjit.com\/blog1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/shirishranjit.com\/blog1\/wp-json\/wp\/v2\/comments?post=508"}],"version-history":[{"count":11,"href":"https:\/\/shirishranjit.com\/blog1\/wp-json\/wp\/v2\/pages\/508\/revisions"}],"predecessor-version":[{"id":2047,"href":"https:\/\/shirishranjit.com\/blog1\/wp-json\/wp\/v2\/pages\/508\/revisions\/2047"}],"up":[{"embeddable":true,"href":"https:\/\/shirishranjit.com\/blog1\/wp-json\/wp\/v2\/pages\/489"}],"wp:attachment":[{"href":"https:\/\/shirishranjit.com\/blog1\/wp-json\/wp\/v2\/media?parent=508"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}