PostgreSQL create user
Corporate applications are being built with the idea of being able to control the access to the content, to structure and manage the information easily. As in most databases, the user's policy plays an important role in PgSQL as well. Used correctly, it allows you to have all-round control over the access to the objects in the database
Table of contents :
Postgre saves user information in its own catalogue different from that of the operation system where PgSQL is installed. This means that all connections to PgSQL have to be performed by the user of the respective database who gives authorization rights via a username and a password. PostgreSQL
offers two methods of creation of database users, both of which demand a superuser authorization, because only superusers can create new user accounts.
Create a user with a web interface
The first method is by using a web based interface. There are several web based interfaces for PostgreSQL - pSQL, pgAdmin, phpPgAdmin. Here, at NTC Hosting, we use the stable and proven phpPgAdmin
, which is very similar to the phpMyAdmin
interface, used for managing MySQL
In order to access the phpPgAdmin, simply go to the "PostgreSQL Databases" menu in the Web Hosting Control Panel
. In there, you will see a list of all the databases created so far. To go into the phpPgAdmin interface, simply click on the respective icon next to the database.
Create a user with the command line
You can also create a user by using the command-line wrapper called createuser - this method is more convenient for programmers and administrators who have access to the console of the respective PgSQL server and only have to execute a single command instead of logging in and using the interface of the respective PostgreSQL client.
An example of how to Create User in PostgreSQL
create user george with password 'password';
Add privileges to a user
Just like other SQL
languages, in PostgreSQL you will have to grant the user privileges to manage a database. Without them, he will not be able to do anything. Possible privileges, which can be given to a user, are SELECT, INSERT, UPDATE, DELETE, RULE or ALL PRIVILEGES. This way, if you have several users working on the same database, you can define their access level. The syntax for granting privileges is the following one: GRANT [the privileges you want to grant] ON [the name of the database] TO [the user].
An example of how to Grant Privileges in PostgreSQL
grant all privileges on database money to cashier;
Revoke privileges from a user
Once you have added privileges to a certain user, you can also revoke them. Again, just like with adding privileges, you can revoke just certain privileges from a user, or you can also revoke all privileges. The syntax for the REVOKE command is the same as for the GRANT one.
An example of how to Revoke Privileges in PostgreSQL
REVOKE ALL PRIVILEGES ON money FROM cashier;
Delete a user
You can also delete the whole user, instead of revoking his privileges. Deleting the users will not affect the actual database in any way. To delete a user, you must first make sure he is now the owner of the database, otherwise you will receive an error message. The command for deleting the user is: DROP USER. The syntax for it is: DROP USER [user]
An example of how to Delete a User in PostgreSQL
PostgreSQL users with NTC Hosting
To facilitate its clients, with each database NTC Hosting automatically creates a username with all privileges, once the database is created. The user will have the same name as the database. This way, you can create a new database in less than 10 seconds, entering just a username and password.