PostgreSQL

Login to PSQL

# [email protected]:~ #
su - postgres
# Last login: Tue Jan 01 12:00:00 MYT 2013 on pts/0
# -bash-4.2$
psql
# psql (9.6.8)
# Type "help" for help.
# 
# postgres=#

Login Error

# [email protected]:~ #
su - postgres psql
# Last login: Tue Jan 01 12:00:00 MYT 2013 on pts/0
# /bin/createuser: /bin/createuser: cannot execute binary file

Creating User

# [email protected]:~ #
su - postgres
# Last login: Tue Jan 01 12:00:00 MYT 2013 on pts/0
# -bash-4.2$
createuser root
# createuser: creation of new role failed: ERROR:  role "root" already exists

Creating DB

Until creating root database it's always show error could not change directory to "/root": Permission denied. To overcome this error we should need to create a root database. only for avoid error while login using root user.

# [email protected]:~ #
su - postgres
# Last login: Tue Jan 01 12:00:00 MYT 2013 on pts/0
# -bash-4.2$
createdb root

Similarly you can create a database as same as your frequently used OS User. In this case no need password to login. It will authenticated from OS. That's means you can login to your PostgreSQL user using OS Authentication.

Grant Privileges

# postgres=#
grant all privileges on database root to root;
# GRANT

# postgres=#
grant all privileges on database postgres to root;
# GRANT

Logout PSQL

# postgres=#
\q
# -bash-4.2$

JSON Query

{
    "type": "Reseller",
    "commission": {
        "type": "Percentage",
        "value": "25"
    }
}

UPDATE seller
SET seller_properties = jsonb_set(seller_properties, '{type}', '"Retailer"')
WHERE id = 1000;
--
UPDATE seller
SET seller_properties = jsonb_set(seller_properties, '{commission, type}', '"Bonus"')
WHERE id = 1000 AND seller_properties ->> 'type' = 'Reseller';

Sequence

CREATE SEQUENCE public.policy_1000
    INCREMENT 1
    START     46656   -- 1,000
    MINVALUE  46656   -- 1,000
    MAXVALUE  1679615 -- Z,ZZZ
    CACHE     10;

ALTER SEQUENCE public.policy_1000 OWNER TO chorke_init_pro;
COMMENT ON SEQUENCE public.policy_1000 IS 'Academia Policy Sequence';

SELECT NEXTVAL('policy_1000')::INT AS policy_id;

Knowledge

-- psql
CREATE DATABASE chorke_init_pro;
CREATE USER chorke_init_pro WITH ENCRYPTED PASSWORD 'pa55w0rd';
GRANT ALL PRIVILEGES ON DATABASE chorke_init_pro TO chorke_init_pro;
-- \q

-- revoke public connect from specific database after creation
REVOKE CONNECT ON DATABASE academia_ebis_dev FROM PUBLIC;
-- revoke public connect for all future databases
REVOKE CONNECT ON DATABASE template1 FROM PUBLIC;

References

• PostgreSQL users can access all databases without any grants
• Creating user, database and adding access on PostgreSQL
• ASP.NET Core MVC Identity using PostgreSQL
• System Catalogs pg_class::relkind
• Query a JSON Column in PostgreSQL
• Fluent NHibernate with PostgreSQL
• Create sequence if not exists
• SQL Create Procedure
• PL/PgSQL Trigger 9.3
• PL/PgSQL Trigger 10