DEV Community

Andres Chaparro
Andres Chaparro

Posted on • Updated on

Create an user in postgreSQL and deliver the database for Development

#postgres #client #adminuser

After installing you must know that there are basic concepts to deliver DB users to a programmer to keep the database secure even if there are no production environments, maybe you don't want to lose any data or you want to keep running the development without issues.

Check instalation (Client on Ubuntu-Linux)

Let´s check if there is any installation in our system.

    command -v psql
Enter fullscreen mode Exit fullscreen mode

It must show something like the following path and must have psql at the end.

    /usr/bin/psql
Enter fullscreen mode Exit fullscreen mode

If there is no installation we need to proceed to install the client.

    sudo su -
    adduser postgres
    touch /etc/apt/sources.list.d/pgdg.list
    chmod 644 /etc/apt/sources.list.d/pgdg.list
    echo "deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main" >> /etc/apt/sources.list.d/pgdg.list
    cat /etc/apt/sources.list.d/pgdg.list
Enter fullscreen mode Exit fullscreen mode

The exit of the last command have to be like:

    deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main
Enter fullscreen mode Exit fullscreen mode

Now get the keys from postgres site:

    wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
Enter fullscreen mode Exit fullscreen mode

OK so far? That´s cool, so let´s install it:

    sudo apt-get install -y postgresql-client-common
    sudo apt-get install -y postgresql-client-11
Enter fullscreen mode Exit fullscreen mode

Now we have a client of

psql

installed.

    /usr/bin/psql
Enter fullscreen mode Exit fullscreen mode

Requirements to login 

1. Database Service: host_name  
2. Port: 5432
3. User: user
4. Password: _**********_
5. Database: database_name
Enter fullscreen mode Exit fullscreen mode

Database Service: There is where your database server is located.

    psql -U <user> -h <host_name> -p <port> -d <database_name>
Enter fullscreen mode Exit fullscreen mode

For our first connection we must do it with the user postgres. Note that where are using 5432 ass a default port. Let's try.

    psql -U postgres -h db_host -p 5432 -d postgres
Enter fullscreen mode Exit fullscreen mode

Get connected? the databases and schemas with \l and \dn command

    \l
    \dn
Enter fullscreen mode Exit fullscreen mode

List users and tables

\du

,

\du+

,

\dt

.

    \du 
    \du+
    \dt
Enter fullscreen mode Exit fullscreen mode

So good so far.....?

Now, we want to create a database and an user to connect to it and we can start develop with that user, it is very important to asign the user to its data base, there is were teh development its going to happen and has to be under its domain:

    CREATE DATABASE <new_database_name>;
    CREATE user <new_user> WITH ENCRYPTED PASSWORD '****************';
    ALTER DATABASE <new_database_name> OWNER TO <new_user>;
Enter fullscreen mode Exit fullscreen mode

Give him privileges to connect and secure the user:

    GRANT CONNECT ON DATABASE <new_database_name> TO <new_user>;
    CREATE SCHEMA <new_schema_name>;
    GRANT usage ON SCHEMA <new_schema_name> TO <new_user>;
Enter fullscreen mode Exit fullscreen mode

Its time to lean the exit command, so you can disconnect form the database:

    \q
Enter fullscreen mode Exit fullscreen mode

Connect with he new user:

    psql -U new_user -h aws.rds.url.endpoint.anyregion.amazon.com -p 5432 -d new_user
Enter fullscreen mode Exit fullscreen mode

Create some starting tables

    CREATE TABLE <new_schema_name>.<table1> (...);
    CREATE TABLE <new_schema_name>.<table2> (...);
    CREATE TABLE <new_schema_name>.<table3> (...);
    ---
    ---
Enter fullscreen mode Exit fullscreen mode

Nice! Now create developing users for our team: You must be postgres here,

    CREATE user <dev_user1> WITH ENCRYPTED PASSWORD '****************';
    CREATE user <dev_user2> WITH ENCRYPTED PASSWORD '****************';
    ---
    ---
    GRANT CONNECT ON DATABASE <new_database_name> TO <dev_user1>;
    GRANT CONNECT ON DATABASE <new_database_name> TO <dev_user2>;
    ---
    ---
    GRANT usage ON SCHEMA <new_schema_name> TO <dev_user1>;
    GRANT usage ON SCHEMA <new_schema_name> TO <dev_user2>;
    ---
    ---
Enter fullscreen mode Exit fullscreen mode

Cool, now we need to give them permision to our first tables;

    GRANT SELECT, INSERT, UPDATE, DELETE ON <new_schema_name>.<table1> TO <dev_user1>;
    GRANT SELECT, INSERT, UPDATE, DELETE ON <new_schema_name>.<table1> TO <dev_user2>;
    ---
    ---
    GRANT SELECT, INSERT, UPDATE, DELETE ON <new_schema_name>.<table2> TO <dev_user1>;
    GRANT SELECT, INSERT, UPDATE, DELETE ON <new_schema_name>.<table2> TO <dev_user2>;
    ---
    ---
Enter fullscreen mode Exit fullscreen mode

Wow, we are all set, let me share you some commands, it depends also of how you want to manage permission to development team:

    --- Grant SELECT, INSERT, UPDATE, DELETE on all tables
    GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA <new_schema_name> TO <dev_user1>;
    ---
    --- Grant all privileges on all tables
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA <new_schema_name> TO <dev_user1>;
    --- 
    --- Grant all sequences
    GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA <new_schema_name> TO <dev_user1>;
    ---
    --- Grant all privileges
    GRANT ALL PRIVILEGES ON DATABASE <new_database_name> TO <dev_user1>;
    --- 
    --- Revoke specific privileges on objects
    REVOKE DELETE, UPDATE ON <new_database_name>.<table1> FROM <dev_user1>;
    --- 
    --- Revoke all privileges
    REVOKE ALL ON <new_database_name>.<table1> FROM <dev_user1>;
    --- 
    --- Revoke public privileges
    REVOKE SELECT ON <new_database_name>.<table1> FROM PUBLIC;
    --- 
    --- Revoke create database
    ALTER USER <new_user> WITH NOCREATEDB;
Enter fullscreen mode Exit fullscreen mode

Top comments (0)

Read next

lundjrl profile image

Reflecting On My Programming Career

James Robert Lund III -

danielmabadeje profile image

Automating Laravel CI/CD with GitHub Actions on Namecheap

Daniel Mabadeje -

suciptoid profile image

Build Link Shortener with Supabase and Nuxt

Sucipto -

spronin profile image

Deploy MongoDB on Kubernetes with ArgoCD and Helm charts

Sergey Pronin -