postgres=# CREATE USER db_user; CREATE ROLE -- Set a password for the user postgres=# ALTER USER db_user WITH PASSWORD 'db_user'; ALTER ROLE -- Grant CREATEROLE and CREATEDB permissions to the user postgres=# ALTER USER db_user WITH CREATEROLE CREATEDB; ALTER ROLE postgres=#
postgres=# create database dbdocs with owner db_user; CREATE DATABASE
psql -h HostName -p Port -d DatabaseName -U UserName
[postgres@dbdocs ~]$ psql -U db_user -h 127.0.0.1 -d dbdocs Password for user db_user: psql (15.2) Type "help" for help. dbdocs=>
dbdocs=> \l List of databases Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges -----------+----------+----------+-------------+-------------+------------+-----------------+----------------------- dbdocs | db_user | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres + | | | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | | libc | =c/postgres + | | | | | | | postgres=CTc/postgres (4 rows)
dbdocs=> SELECT datname,encoding,datctype FROM pg_database; datname | encoding | datctype -----------+----------+------------- postgres | 6 | en_US.UTF-8 dbdocs | 6 | en_US.UTF-8 template1 | 6 | en_US.UTF-8 template0 | 6 | en_US.UTF-8 (4 rows) dbdocs=>
\c dbname username
postgres-> \c dbdocs db_user You are now connected to database "dbdocs" as user "db_user". dbdocs->
dbdocs=> \dn List of schemas Name | Owner --------+------------------- dbdocs | db_user public | pg_database_owner (2 rows) dbdocs=>
dbdocs=> SELECT schema_name,schema_owner FROM information_schema.schemata; schema_name | schema_owner --------------------+------------------- public | pg_database_owner dbdocs | db_user information_schema | postgres pg_catalog | postgres (4 rows) dbdocs=>
dbdocs=> \dt List of relations Schema | Name | Type | Owner --------+-------------+-------+--------- public | dbdocssales | table | db_user public | users | table | db_user (2 rows) dbdocs=>
dbdocs=> SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'; table_name ------------- dbdocssales users (2 rows) dbdocs=>
\d table_name
dbdocs=> \d dbdocssales Table "public.dbdocssales" Column | Type | Collation | Nullable | Default --------------+-------------------+-----------+----------+--------- sales_id | uuid | | not null | sales_number | integer | | not null | first_name | character varying | | not null | last_name | character varying | | not null | email | character varying | | not null | phone | character varying | | | Indexes: "dbdocssales_pkey" PRIMARY KEY, btree (sales_id) dbdocs=>
dbdocs=> SELECT column_name, data_type, character_maximum_length, is_nullable FROM information_schema.columns WHERE table_name = 'dbdocssales'; column_name | data_type | character_maximum_length | is_nullable --------------+-------------------+--------------------------+------------- sales_id | uuid | | NO sales_number | integer | | NO first_name | character varying | | NO last_name | character varying | | NO email | character varying | | NO phone | character varying | | YES (6 rows) dbdocs=>
dbdocs=> \dv List of relations Schema | Name | Type | Owner --------+-------------------+------+--------- public | v_confirmed_orders | view | db_user public | v_projected_sales | view | db_user public | v_sales | view | db_user (3 rows) dbdocs=>
dbdocs=> select table_catalog,table_schema,table_name from information_schema.views WHERE table_schema = 'public'; table_catalog | table_schema | table_name ---------------+--------------+------------------- dbdocs | public | v_sales dbdocs | public | v_projected_sales dbdocs | public | v_confirmed_orders (3 rows) dbdocs=>
dbdocs=> \df List of functions Schema | Name | Result data type | Argument data types | Type --------+-------------------------------------------+------------------+---------------------------------------------------------------------------+------ public | gtrgm_options | void | internal | func public | gtrgm_out | cstring | gtrgm | func public | gtrgm_penalty | internal | internal, internal, internal | func public | gtrgm_picksplit | internal | internal, internal | func public | gtrgm_same | internal | gtrgm, gtrgm, internal | func public | gtrgm_union | gtrgm | internal, internal | func public | sales_total_price | numeric | quantity integer, unit_price numeric | func ---- --- dbdocs=>
dbdocs=> select proname,proowner,protrftypes FROM pg_proc WHERE pronamespace = 'public'::regnamespace; proname | proowner | protrftypes -------------------------------------------+----------+------------- gtrgm_consistent | 10 | gtrgm_distance | 10 | gtrgm_compress | 10 | gtrgm_decompress | 10 | gtrgm_penalty | 10 | gtrgm_picksplit | 10 | ---- ---
postgres=> \du List of roles Role name | Attributes | Member of -----------+------------------------------------------------------------+----------- db_user | Create role, Create DB | {} postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {} postgres=>
postgres=> SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcanlogin FROM pg_roles; rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin ---------------------------+----------+------------+---------------+-------------+------------- db_user | f | t | t | t | t postgres | t | t | t | t | t
postgres=> \g version ------------------------------------------------------------------------------------------------------------- PostgreSQL 15.2 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit (1 row) postgres=>
postgres=> SELECT version(); version ------------------------------------------------------------------------------------------------------------- PostgreSQL 15.2 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-18), 64-bit (1 row)
postgres=>\? --To get help for creating a table postgres=> \h CREATE TABLE
postgres=>\q