- 06 Aug, 2024
- 0 Comments
- 7 Mins Read
Schema management in Postgresql
postgres=# \du
List of roles
Role name | Attributes | Member of
-------------+-----------------------------------------------------------+-----------
learnomate | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS| {}
u2 | | {}
tom | | {}
postgres=# create user rajesh with password 'test123';
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-------------+-----------------------------------------------------------+-----------
learnomate | Superuser | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS| {}
u1 | | {}
u2 | | {}
tom | | {}
postgres=#
postgres=#
postgres=#
postgres=# alter user learnomate nosuperuser;
ALTER ROLE
postgres=# \du
postgres=# create user rajesh with password 'test123';
CREATE ROLE
postgres=# \du
List of roles
Role name | Attributes | Member of
-------------+-----------------------------------------------------------+-----------
learnomate | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS| {}
u1 | | {}
u2 | | {}
tom | | {}
postgres=#
postgres=# \c prod learnomate
connection to server on socket "/run/postgresql/.s.PGSQL.5002" failed: FATAL: no pg_hba.conf entry for host "[local]", user "learnomate", database "prod", no encryption
Previous connection kept
postgres=# \q
-bash-4.2$ pwd
/app01/test_data/pg_tblspc
-bash-4.2$ cd ..
-bash-4.2$ vi pg_hba.conf
-bash-4.2$ pg_ctl -D /app01/test_data/ reload server signaled
-bash-4.2$ psql -p 5002 -U postgres
psql (15.7)
Type "help" for help.
postgres=# \c prod learnomate
Password for user learnomate:
connection to server on socket "/run/postgresql/.s.PGSQL.5002" failed: FATAL: permission denied for database "prod"
DETAIL: User does not have CONNECT privilege. Previous connection kept
Previous connection kept
postgres=# \l+ prod
postgres=# GRANT CONNECT ON DATABASE prod TO learnomate, u1;
GRANT
postgres=# \c prod learnomate
Password for user learnomate:
You are now connected to database "prod" as user "learnomate".
prod=> CREATE SCHEMA learnomate AUTHORIZATION learnomate;
ERROR: permission denied for database prod
prod=> \c prod postgres
You are now connected to database "prod" as user "postgres".
prod=# GRANT CREATE ON DATABASE prod TO learnomate, u1;
GRANT
prod=# \c prod learnomate
Password for user learnomate:
You are now connected to database "prod" as user "learnomate".
prod=> CREATE SCHEMA learnomate AUTHORIZATION learnomate;
GRANT
prod=# \c prod learnomate
Password for user learnomate:
You are now connected to database "prod" as user "learnomate".
prod=> CREATE SCHEMA learnomate AUTHORIZATION learnomate;
CREATE SCHEMA
prod=> \dn
List of schemas
Name | Owner
-------------+-------------------
learnomate | learnomate
public | pg_database_owner
(2 rows)
prod=> show search_path;
search_path
-----------------
"$user", public
(1 row)
prod=> SET search_path TO learnomate;
SET
prod=> SHOW search_path;
search_path
-------------
learnomate
(1 row)
prod=> CREATE TABLE tb1(id integer, name varchar(20));
CREATE TABLE
prod=> CREATE TABLE tb2(id integer, name varchar(20));
CREATE TABLE
prod=> CREATE TABLE tb3(id integer, name varchar(20));
CREATE TABLE
Listing tables:
prod=> \dt
List of relations
Schema | Name | Type | Owner
------------+------+-------+-----------
learnomate | tb1 | table| learnomate
learnomate | tb2 | table| learnomate
learnomate | tb3 | table| learnomate
(3 rows)
prod=> show search_path;
search_path
-----------------
"$user", public (1 row)
Setting the search_path to u1:
prod=> SET search_path TO u1;
SET
Creating Tables in the u1 Schema:
prod=> CREATE TABLE p1(id integer, name varchar(20));
CREATE TABLE
prod=> CREATE TABLE p2(id integer, name varchar(20));
CREATE TABLE
prod=> CREATE TABLE p3(id integer, name varchar(20));
CREATE TABLE
Inserting Data into Tables:
prod=> INSERT INTO p1 VALUES(generate_series(1,10), 'learnomate');
INSERT 0 10
prod=> INSERT INTO p2 VALUES(generate_series(1,10), 'learnomate');
INSERT 0 10
prod=> INSERT INTO p3 VALUES(generate_series(1,10), 'learnomate');
INSERT 0 10
Listing Tables in the u1 Schema:
prod=> \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
u1 | p1 | table | u1
u1 | p2 | table | u1
u1 | p3 | table | u1
(3 rows)
prod=> select current_user; current_user -------------- u1 (1 row)
prod=> set search_path to learnomate; SET prod=> \dt Did not find any relations.
Switching to User learnomate:
prod=> \c prod learnomate Password for user learnomate: You are now connected to database "prod" as user "learnomate".
Granting Usage on the learnomate Schema to User u1:
prod=> GRANT USAGE ON SCHEMA learnomate TO u1; GRANT
Connecting as User u1:
prod=> \c prod u1 Password for user u1: You are now connected to database "prod" as user "u1".
Setting the search_path:
prod=> SET search_path TO learnomate;
SET
prod=> \dt
List of relations
Schema | Name | Type | Owner
-----------+------+-------+-----------
learnomate | tb1 | table | learnomate
learnomate | tb2 | table | learnomate
learnomate | tb3 | table | learnomate
(3 rows)
prod=> select * from tb1; ERROR: permission denied for table tb1 prod=> create table tb4(id int); ERROR: permission denied for schema learnomate LINE 1: create table tb4(id int);
Granting CREATE Privilege:
prod=> \c prod learnomate
Password for user learnomate:
You are now connected to database "prod" as user "learnomate".
prod=> GRANT CREATE ON SCHEMA learnomate TO u1;
GRANT
Granting SELECT Privilege:
prod=> GRANT SELECT ON ALL TABLES IN SCHEMA learnomate TO u1;
GRANT
prod=> \dn+ learnomate
List of schemas
Name | Owner | Access privileges | Description
-----------+------------+--------------------------+-------------
learnomate | learnomate | learnomate=UC/learnomate+|
| | u1=UC/learnomate |
(1 row)
prod=> \z+ learnomate.tab1 invalid command \z+ Try \? for help.
prod=> \z learnomate.tb1
Access privileges
Schema | Name | Type | Access privileges | Column | Policies
privileges
-----------+------+-------+-------------------+------------+----------
learnomate | tb1 | table |learnomate=arwdDxt/| |
learnomate+
| | | u1=r/learnomate | |
(1 row)
prod=> grant select,insert on all tables in schema learnomate to u1;
GRANT
prod=> \z learnomate.tb1
Access privileges
Schema | Name | Type | Access privileges | Column | Policies
privileges
-----------+------+-------+--------------------+-----------+----------
learnomate | tb1 | table |learnomate=arwdDxt/ | |
learnomate+
| | | u1=ar/learnomate | |
(1 row)
Switching to User u1:
prod=> \c prod u1
Password for user u1:
You are now connected to database "prod" as user "u1".
prod=> set search_path to learnomate
prod-> ;
SET
prod=> select * from tb1;
id | name
---+------------
1 | learnomate
2 | learnomate
3 | learnomate
4 | learnomate
5 | learnomate
6 | learnomate
7 | learnomate
8 | learnomate
9 | learnomate
10| learnomate
(10 rows)
prod=> insert into tb1 values(generate_series(11,15),'learnomate'); INSERT 0 5 prod=> select * from tb1;
id | name
---+------------
1 | learnomate
2 | learnomate
3 | learnomate
4 | learnomate
5 | learnomate
6 | learnomate
7 | learnomate
8 | learnomate
9 | learnomate
10| learnomate
11| learnomate
12| learnomate
13| learnomate
14| learnomate
15| learnomate
(15 rows)
prod=> create table tb4(id integer,name varchar(20)); CREATE TABLE prod=> \dt List of relations Schema | Name| Type | Owner -----------+-----+-------+-------- learnomate | tb1 | table | learnomate learnomate | tb2 | table | learnomate learnomate | tb3 | table | learnomate learnomate | tb4 | table | u1 (4 rows)
prod=> \c prod learnomate
Password for user learnomate:
You are now connected to database "prod" as user "learnomate".
prod=> set search_path to learnomate;
SET
prod=> select * from tb4;
ERROR: permission denied for table tb4
prod=> \dn+ learnomate
List of schemas
Name | Owner | Access privileges | Description
----------+------------+--------------------------+-------------
learnomate| learnomate | learnomate=UC/learnomate+|
| | u1=UC/learnomate |
(1 row)
prod=> \z tb1
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
-----------+------+-------+--------------------+-------------------+----------
learnomate | tb1 | table | learnomate=arwdDxt
/learnomate+| |
| | | u1=ar/learnomate | |
(1 row)
prod=> \c prod u1 Password for user u1: You are now connected to database "prod" as user "u1". prod=> show search_path; search_path ----------------- "$user", public (1 row)
prod=> \c prod postgres You are now connected to database "prod" as user "postgres". prod=# alter user learnomate set search_path to learnomate; ALTER ROLE prod=# alter user u1 set search_path to u1; ALTER ROLE prod=# select pg_reload_conf(); pg_reload_conf ---------------- t (1 row)
prod=# \c prod learnomate Password for user learnomate: You are now connected to database "prod" as user "learnomate". prod=> show search_path; search_path ------------- learnomate (1 row) prod=> \dt List of relations Schema | Name | Type | Owner -----------+------+-------+-------- learnomate | tb1 | table | learnomate learnomate | tb2 | table | learnomate learnomate | tb3 | table | learnomate learnomate | tb4 | table | u1 (4 rows)
prod=> \c prod u1 Password for user u1: You are now connected to database "prod" as user "u1". prod=> \dt List of relations Schema | Name | Type | Owner --------+------+-------+-------- u1 | p1 | table | u1 u1 | p2 | table | u1 u3 | p3 | table | u1 (3 rows)
prod=> \c prod learnomate Password for user learnomate: You are now connected to database "prod" as user "learnomate". prod=> create schema dept; CREATE SCHEMA prod=> \dn Name | Owner --------------+------------------- dept | learnomate learnomate | learnomate public | pg_database_owner u1 | u1 (4 rows)
prod=> set search_path to dept; SET prod=> create table dep1(id integer,name varchar(20)); CREATE TABLE prod=> create table dep2(id integer,name varchar(20)); CREATE TABLE prod=> create table dep3(id integer,name varchar(20)); CREATE TABLE prod=> set search_path to dept,learnomate; SET prod=> \dt List of relations Schema | Name | Type | Owner -----------+------+-------+------------ dept | dep1 | table | learnomate dept | dep2 | table | learnomate dept | dep3 | table | learnomate learnomate | tb1 | table | learnomate learnomate | tb2 | table | learnomate learnomate | tb3 | table | learnomate learnomate | tb4 | table | u1 (7 rows)
prod=> create table test(id integer,name varchar(20)); CREATE TABLE prod=> \dt List of relations Schema | Name | Type | Owner -----------+------+-------+-------- dept | dep1 | table | learnomate dept | dep2 | table | learnomate dept | dep3 | table | learnomate dept | test | table | learnomate learnomate | tb1 | table | learnomate learnomate | tb2 | table | learnomate learnomate | tb3 | table | learnomate learnomate | tb4 | table | u1 (8 rows)