Title:
PostgreSQL
|
edited by
Youngrok Pak
at
10 years, 1 month ago.
<h2>기본 명령어</h2>
<h3>사용자 관련</h3>
<h4>사용자 생성</h4>
<pre>create role <username> with login password <password>;</pre>
<h4>Super User</h4>
<div class="post-text">
<pre class="lang-sql prettyprint prettyprinted"><code><span class="kwd">ALTER</span><span class="kwd">USER</span><span class="pln"> myuser </span><span class="kwd">WITH</span><span class="pln"> SUPERUSER</span><span class="pun">;</span></code></pre>
</div>
<h4>권한 지정</h4>
<pre>grant usage on schema public to <username>;<br>grant all on schema public to <username>;<br>grant all privileges on database <dbname> to <username>;<br>grant all on all tables in schema public to <username><br>grant select on <table> to <username>;</pre>
<p>읽기 전용 계정 만들기 <a href="http://stackoverflow.com/questions/760210/how-do-you-create-a-read-only-user-in-postgresql">http://stackoverflow.com/questions/760210/how-do-you-create-a-read-only-user-in-postgresql</a></p>
<pre><code><span class="kwd">GRANT CONNECT ON DATABASE mydb TO xxx;<br>GRANT USAGE ON SCHEMA public TO xxx;<br>GRANT SELECT ON mytable TO xxx;<br>GRANT SELECT ON ALL TABLES IN SCHEMA public TO xxx;</span></code></pre>
<p>커넥션 현황 보기</p>
<pre>SELECT count(*) from pg_stat_activity;</pre>
<h2><a href="파이썬">파이썬</a>에서 사용하기</h2>
<pre>import psycopg2<br>conn = psycopg2.connect('host=db.com user=youngrok password=blahblah dbname=mydb<br>cursor = conn.cursor()<br>cursor.execute('SELECT * FROM auth_user')<br>print cursor.fetchall()</pre>
<p>