{"id":85,"date":"2014-11-20T15:31:06","date_gmt":"2014-11-20T10:01:06","guid":{"rendered":"http:\/\/bhuvan.wordpress.com\/?p=85"},"modified":"2025-07-21T14:17:48","modified_gmt":"2025-07-21T08:47:48","slug":"adding-postgresql-user-and-db-quick-steps","status":"publish","type":"post","link":"https:\/\/www.bhuvankrishna.in\/blog\/2014\/11\/20\/adding-postgresql-user-and-db-quick-steps\/","title":{"rendered":"Creating postgresql Database and user in a nutshell."},"content":{"rendered":"<p>I will not write much but just the code required to create a user and let the user access the db.<\/p>\n<p><strong>Create User<\/strong><br \/><code>CREATE USER tom WITH PASSWORD 'myPassword';<\/code><\/p>\n<p><strong>Create a DB<\/strong><br \/><code>CREATE DATABASE jerry;<\/code><\/p>\n<p><strong>Grant Privileges to user on the DB<\/strong><br \/><code>GRANT ALL PRIVILEGES ON DATABASE jerry to tom;<\/code><\/p>\n<p><strong>Grant Privileges to table<\/strong><\/p>\n<pre class=\"lang-sql s-code-block\"><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">GRANT<\/span> <span class=\"hljs-keyword\">ALL<\/span> PRIVILEGES <span class=\"hljs-keyword\">ON<\/span> <span class=\"hljs-keyword\">TABLE<\/span> side_adzone <span class=\"hljs-keyword\">TO<\/span> tom;<\/code><\/pre>\n<p><strong>Grant Privileges to user for copying files (especially import csv to DB)<br \/><\/strong><\/p>\n<pre class=\"lang-sql s-code-block\"><code class=\"hljs language-sql\">GRANT pg_read_server_files TO tom;<\/code><\/pre>\n<p><strong>Change ownership of the DB<\/strong><br \/><code>ALTER DATABASE jerry OWNER TO tom;<\/code><\/p>\n<p><strong>Delete user<\/strong><br \/><code>DROP USER tom;<\/code><\/p>\n<p><strong>Update user password<br \/><\/strong><\/p>\n<pre class=\"lang-sql s-code-block\"><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">ALTER<\/span> <span class=\"hljs-keyword\">USER<\/span> user_name <span class=\"hljs-keyword\">WITH<\/span> PASSWORD <span class=\"hljs-string\">'new_password'<\/span>;<\/code><\/pre>\n<p><strong>Drop DB<\/strong><br \/><code>DROP DATABASE jerry;<\/code><\/p>\n\n\n<p class=\"wp-block-paragraph\"><strong>Backup and Restore databases<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To take a backup use this command. The following command works only with postgres user so first change the user to postgres <code>su postgres<\/code> and then dump. make sure the directory is writable by postgres user.<br><code>pg_dump db_name &gt; db_name.sql<\/code><\/p>\n\n\n\n<p class=\"wp-block-paragraph\">To restore run the command as postgres user<br><code>psql db_name &lt; db_name.sql<\/code><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Shell script to create a user, database, grant privileges and change ownership in one go.<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>#!\/bin\/bash\n\n# PostgreSQL Database Setup Script\n# This script creates a user, database, and grants privileges\n\n# Exit on any error\nset -e\n\n# Database connection parameters (modify as needed)\nPOSTGRES_HOST=${POSTGRES_HOST:-localhost}\nPOSTGRES_PORT=${POSTGRES_PORT:-5432}\nPOSTGRES_ADMIN_USER=${POSTGRES_ADMIN_USER:-postgres}\n\n# New user and database details\nDB_USER=\"tom\"\nDB_PASSWORD=\"myPassword\"\nDB_NAME=\"jerry\"\n\necho \"Setting up PostgreSQL database and user...\"\n\n# Create user and database\npsql -h \"$POSTGRES_HOST\" -p \"$POSTGRES_PORT\" -U \"$POSTGRES_ADMIN_USER\" -c \"\n-- Create user\nCREATE USER $DB_USER WITH PASSWORD '$DB_PASSWORD';\n\n-- Create database\nCREATE DATABASE $DB_NAME;\n\n-- Grant all privileges on database to user\nGRANT ALL PRIVILEGES ON DATABASE $DB_NAME TO $DB_USER;\n\n-- Change database owner to the user\nALTER DATABASE $DB_NAME OWNER TO $DB_USER;\n\"\n\necho \"Database setup completed successfully!\"\necho \"User: $DB_USER\"\necho \"Database: $DB_NAME\"\necho \"Connection string: postgresql:\/\/$DB_USER:$DB_PASSWORD@$POSTGRES_HOST:$POSTGRES_PORT\/$DB_NAME\"%<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>I will not write much but just the code required to create a user and let the user access the db. Create UserCREATE USER tom WITH PASSWORD &#8216;myPassword&#8217;; Create a DBCREATE DATABASE jerry; Grant Privileges to user on the DBGRANT ALL PRIVILEGES ON DATABASE jerry to tom; Grant Privileges to table GRANT ALL PRIVILEGES ON\u2026 <span class=\"read-more\"><a href=\"https:\/\/www.bhuvankrishna.in\/blog\/2014\/11\/20\/adding-postgresql-user-and-db-quick-steps\/\">Read More &raquo;<\/a><\/span><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[],"class_list":["post-85","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/www.bhuvankrishna.in\/blog\/wp-json\/wp\/v2\/posts\/85","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bhuvankrishna.in\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bhuvankrishna.in\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bhuvankrishna.in\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bhuvankrishna.in\/blog\/wp-json\/wp\/v2\/comments?post=85"}],"version-history":[{"count":8,"href":"https:\/\/www.bhuvankrishna.in\/blog\/wp-json\/wp\/v2\/posts\/85\/revisions"}],"predecessor-version":[{"id":604,"href":"https:\/\/www.bhuvankrishna.in\/blog\/wp-json\/wp\/v2\/posts\/85\/revisions\/604"}],"wp:attachment":[{"href":"https:\/\/www.bhuvankrishna.in\/blog\/wp-json\/wp\/v2\/media?parent=85"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bhuvankrishna.in\/blog\/wp-json\/wp\/v2\/categories?post=85"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bhuvankrishna.in\/blog\/wp-json\/wp\/v2\/tags?post=85"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}