PostgreSQL add or create a user account and grant permission for database. ByVivek GiteonMarch 6, 2007 last updated October 24, 2007 Q. How do I create a user account called tom and grant permission for database called jerry? A. You need to use following commands. => adduser - UNIX/Linux adduser command to add a user to /etc/passwd file => psql => It is a terminal-based front-end to PostgreSQL. => CREATE USER - Adds a new user to a PostgreSQL database cluster.
=> CREATE DATABASE - create a new database => GRANT ALL PRIVILEGES - define access privileges Procedure to add a user to PostgreSQL database To create a normal user and an associated database you need to type the following commands. Step # 1: Add a Linux/UNIX user called tom Type the following commands to create a UNIX/Linux user called tom: # adduser tom # passwd tom Step # 2: Becoming a superuser You need to login as database super user under postgresql server.
Step #3: Now connect to database server Type the following command $ psql template1 OR $ psql -d template1 -U postgres Output: 15 Practical PostgreSQL Database Administration Commands. Earlier we discussed about how to install PostgreSQL database on Linux from source. In this article, let us review top 15 practical postgreSQL DBA command examples. If you are a mySQL administrator, check-out our 15 examples of mysqladmin command article that we discussed a while back. 1. How to change PostgreSQL root user password ? $ /usr/local/pgsql/bin/psql postgres postgres Password: (oldpassword) # ALTER USER postgres WITH PASSWORD 'tmppassword'; $ /usr/local/pgsql/bin/psql postgres postgres Password: (tmppassword) Changing the password for a normal postgres user is similar as changing the password of the root user. . # ALTER USER username WITH PASSWORD 'tmppassword'; 2. . $ su - root # tar xvfz postgresql-8.3.7.tar.gz # cd postgresql-8.3.7 # cp contrib/start-scripts/linux /etc/rc.d/init.d/postgresql # chmod a+x /etc/rc.d/init.d/postgresql 3. 4. 5.
. $ /usr/local/pgsql/bin/psql test Welcome to psql 8.3.7, the PostgreSQL interactive terminal. 5. 6. 7. 8. 9. . \? # \? 10. . # \d 11. 12. 13. PostgreSQL QuickStart/Reference Commands... | LinuxWebLog.com. Since I don't use PostgreSQL as often as MySQL, I tend to forget simple commands for administering the database. Hopefully these notes will help as reference when working with PostgreSQL: Related Reading: PostgreSQL Server Administration. Using PostgreSQL on Debian and Ubuntu. Summary PostgreSQL is the most capable Open Source database server available, with functionality that can complete or even exceed proprietary SQL database products. These instructions assume that you are using either Debian or Ubuntu, but should be adaptable to other operating systems. Ubuntu provides versions of the Debian PostgreSQL packages, and so has the same maintenance facilities as Debian.
The current releases of Ubuntu (16.04 and above) includes version 9.5 of PostgreSQL, rather than PostgreSQL 9.3, so amend the commands below accordingly. Privileged Commands: Commands that require root (administrative) privileges are shown with the prefix #. The setup process installs the PostgreSQL server software and configures a cluster - a group of databases managed by a common service. The main cluster initially has three databases: postgres (for management purposes), template0 (a template database without any customizations) and template1 (a template database that you may customize). \q. How to use Postgres - Introduction to Information Systems Project. Basics Let's start with a few basic things that are essential to know about Postgres. The links lead you to the corresponding section of the official PostgreSQL documentation, where you can find all the details. You may also want to read the (short) official tutorial.
Every Postgres installation represents a so-called database cluster. We have prepared such a cluster for every team, so you don't need to care about this. We will usually use the words cluster and server synonymously. Each cluster contains an arbitrary number of databases. Please make sure you are familiar with the concepts above before you attack the rest of this section. Creating databases Before you start using your database cluster, you have to create at least one database. User01@lsir-cis-pc1:~$ createdb CREATE DATABASE user01@lsir-cis-pc1:~$ That's it!
If you specify no name like we did above, the name of the newly created database is the same as your login name (in our case user01. Logging in and out Creating tables. Detailed installation guides. Any UNIX-Like Platform General Linux Debian/Ubuntu Linux Debian based installs have a somewhat unique design that allows multiple database clusters to be managed independently. This allows running both multiple database instances as well as multiple versions more easily than other packaging schemes.
RedHat/Fedora/CentOS FreeBSD MacOS Solaris Windows Running & Installing PostgreSQL On Native Windows Alternative to manual installation Some users may prefer to skip manual installation by using a pre-integrated PostgreSQL software appliance. PostgreSql. Translation(s): English - Italiano - Русский PostgreSQL Overview PostgreSQL has extensive and good help that should be the first source of information regarding this database product. This page outlines main differences to generic PostgreSQL installation used by Debian.
Installation Required packages: postgresql postgresql-client # apt-get install postgresql postgresql-client Recommended packages: postgresql-doc - PostgreSQL documentation. pgadmin3 - PostgreSQL administration GUI. phppgadmin - PostgreSQL web-based administration tool. Please note that the procedural languages are installed separately (plpgsql comes by default) . # aptitude search postgresql User access Both the default database user and default database are called postgres. You may connect to the database using the following command: # su - postgres $ psql New User and database Create a regular system user account using adduser (skip this step to use an existing account): # adduser mypguser #from regular shell Connect to database or.