Create a user with some permissions to stop using root

Why you want to make a new user instead using the user postgres (root user)?

  1. The root user has all rights which is not very secure, with a new user you can give right more precisely
  2. The root user is not personnal so you can’t know who made the changes.
  3. If someone no longer works on this database just delete its user rather than change the password of the root user and give it back to everyone who used it.

Prerequisite

On you have properly installed postgresql you can create the required user.

Create a user

Warning

The default database always available when you install postgresql is: postgres. It is in this database that all commands can be executed, before you have created another database.

There are several ways to create a user but a simple way is to log in as postgres user and create your user.

sudo -u postgres psql
postgres=#CREATE user <user_name> with encrypted password '<dummy_password>';
postgres=#\password <user_name>
Enter new password for user "<user_name>":
Enter it again:
  • sudo -u postgres psql launch a psql terminal by logging in as the ‘postgres’ user.
  • CREATE user <user_name> with encrypted password '<dummy_password>'; create a user name with a dummy encrypted password . After that you need to change your password with a password prompt.

You can also create your user with a simple command line without launching the psql shell

psql -h <host> -U postgres -d postgres -c "CREATE user <user_name> with encrypted password <your_password>"

Documentation:

Grant acces to your user

To grant a user you can must use the comand GRANT of postgresql.

With grant you can give some specific rights to your user.

Some examples:

Grant connect to the database:

GRANT CONNECT ON DATABASE <database_name> TO <user_name>;

Grant on all table for SELECT and INSERT:

Warning

This works only on table already create.

GRANT SELECT, INSERT ON ALL TABLES IN SCHEMA public TO <user_name>;

Grant all privileges on the database:

GRANT ALL PRIVILEGES ON DATABASE <database_name> TO <user_name>;

You can run all this command in the psql shell with the root user

sudo -u postgres psql
postgres=SQL COMMAND

or on command line:

psql -h <host> -U postgres -d psotgres -c "SQL COMMAND"

Make your user a super user

If you want to use your user like the root user you can give it to him the super user role.

In a psql shell:

sudo -u postgres psql
postgres=ALTER USER <user_name> WITH SUPERUSER;

Or in command line:

psql -h <host> -U postgres -d postgres -c "ALTER USER <user_name> WITH SUPERUSER"

You can remove this super user role at any time this comamnd:

sudo -u postgres psql
postgres=ALTER USER <user_name> WITH NOSUPERUSER;

Or in command line:

psql -h <host> -U postgres -d postgres -c "ALTER USER <user_name> WITH NOSUPERUSER"

Documentation:

Use your user

Now that you have a personnal user you can execute all command (for which you have the rights) with this user.

To do that you can launch the psql terminal with your user:

psql -U <user_name> -d <database_name>
<user_name>=SQL COMMAND

Or in command line:

psql -h <host> -U <user_name> -d <database_name> -c "SQL COMMAND"

Execute an sql file in command line

If you have an SQL file and you don’t want copy past each command in the terminal you can execute directly the file in one command line.

psql -h <hsot> -U <user_name> -d <database> -f /home/user/path/file.sql

Examples of commands:

This examples delete a database name exo_example and recreate it with a sqlfiel for the schema and file it with an other sql file. We use the the user toto.

psql -h localhost -U toto -d postgres -c "DROP DATABASE exo_examples"
psql -h localhost -U toto -d postgres -c "CREATE DATABASE exo_examples"
psql -h localhost -U toto -d exo_example -f /home/toto/Documents/create_db.sql
psql -h localhost -U toto -d exo_example -f /home/toto/Documents/load_data.sql
Note

You can’t delete a database if you are connected to it. So we delete the database exo_example when we are connected to the default database postgres.

Avoid entering your password each time

To avoid entering your password each time you want to connect to the db you need to use a .pgpass file. This file is the easiest and most secure way to connect to a database. When you try to connect to a database postregsql try to read this file and get the password to connect you. The file can contains several connection lines for the same user or not.

Syntax of the .pgpass file

Each line is for only one connection and must be written this way;

host:port:database:user:password

Note

By default the connection port is 5432.

After creating the file, you must set its rights with the command:

chmod 0600 ~/.pgpass
Warning

From postgresql documentation:

On Unix systems, the permissions on a password file must disallow any access to world or group; achieve this by a command such as chmod 0600 ~/.pgpass. If the permissions are less strict than this, the file will be ignored. On Microsoft Windows, it is assumed that the file is stored in a directory that is secure, so no special permissions check is made.

Examples of .pgpass file:

localhost:5432:exo_examples:toto:totopassword
localhost:5432:postgres:toto:totopassword

Documentation: