snipt

Ctrl+h for KB shortcuts

SQL

grant rights to all tables to a user

-- This outputs GRANT statements which you can copy-paste and execute:
SELECT 'GRANT ALL ON ' || schemaname || '.' || tablename || ' TO myuser;' FROM pg_tables WHERE schemaname='public';
https://snipt.net/embed/001d6c82a0d8bf3b6017275083f74aef/
/raw/001d6c82a0d8bf3b6017275083f74aef/
001d6c82a0d8bf3b6017275083f74aef
sql
SQL
2
2019-07-13T04:46:37
True
False
False
/api/public/snipt/4731/
grant-rights-to-all-tables-to-a-user
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a><span class="c1">-- This outputs GRANT statements which you can copy-paste and execute:</span> </span><span id="L-2"><a name="L-2"></a><span class="k">SELECT</span> <span class="s1">&#39;GRANT ALL ON &#39;</span> <span class="o">||</span> <span class="n">schemaname</span> <span class="o">||</span> <span class="s1">&#39;.&#39;</span> <span class="o">||</span> <span class="n">tablename</span> <span class="o">||</span> <span class="s1">&#39; TO myuser;&#39;</span> <span class="k">FROM</span> <span class="n">pg_tables</span> <span class="k">WHERE</span> <span class="n">schemaname</span><span class="o">=</span><span class="s1">&#39;public&#39;</span><span class="p">;</span> </span></pre></div> </td></tr></table>
grant, postgresql

Text only

passwordless PostgreSQL dumps

1
2
3
4
5
6
# /etc/sudoers
username machine = (postgres) NOPASSWD: /usr/bin/pg_dump
# Make sure no following rules override the above one.
# Now you can:
# [email protected] $ sudo -u postgres pg_dump mydb
# without needing to enter your password.
https://snipt.net/embed/b3eaa5687c0f900d5a349ba3e688ac2e/
/raw/b3eaa5687c0f900d5a349ba3e688ac2e/
b3eaa5687c0f900d5a349ba3e688ac2e
text
Text only
6
2019-08-15T07:31:46
True
False
False
/api/public/snipt/4455/
passwordless-postgresql-dumps
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a> <a href="#L-3">3</a> <a href="#L-4">4</a> <a href="#L-5">5</a> <a href="#L-6">6</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a># /etc/sudoers </span><span id="L-2"><a name="L-2"></a>username machine = (postgres) NOPASSWD: /usr/bin/pg_dump </span><span id="L-3"><a name="L-3"></a># Make sure no following rules override the above one. </span><span id="L-4"><a name="L-4"></a># Now you can: </span><span id="L-5"><a name="L-5"></a># [email protected] $ sudo -u postgres pg_dump mydb </span><span id="L-6"><a name="L-6"></a># without needing to enter your password. </span></pre></div> </td></tr></table>
database, pg_dump, postgresql, sudo

Bash

copy PostgreSQL database from remote machine

[email protected] $ sudo -u postgres dropdb mydb
[email protected] $ ssh remote sudo -u postgres pg_dump -C mydb | sudo -u postgres psql
https://snipt.net/embed/24ccfb7bbc25089656f0665e6fca6aef/
/raw/24ccfb7bbc25089656f0665e6fca6aef/
24ccfb7bbc25089656f0665e6fca6aef
bash
Bash
2
2019-08-13T12:42:33
True
False
False
/api/public/snipt/4454/
copy-postgresql-database-from-remote-machine
<table class="highlighttable"><tr><td class="linenos"><div class="linenodiv"><pre><a href="#L-1">1</a> <a href="#L-2">2</a></pre></div></td><td class="code"><div class="highlight"><pre><span></span><span id="L-1"><a name="L-1"></a>[email protected] $ sudo -u postgres dropdb mydb </span><span id="L-2"><a name="L-2"></a>[email protected] $ ssh remote sudo -u postgres pg_dump -C mydb <span class="p">|</span> sudo -u postgres psql </span></pre></div> </td></tr></table>
copy, database, postgresql, ssh, sudo