CREATE USER app_user WITH PASSWORD 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON tablename TO app_user;
GRANT USAGE, SELECT ON SEQUENCE tablename_id_seq TO app_user;
GRANT UPDATE ON SEQUENCE tablename_id_seq TO app_user;
Find all Users and there rights/roles
SELECT u.usename AS "Role name",
CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('superuser, create
database' AS pg_catalog.text)
WHEN u.usesuper THEN CAST('superuser' AS pg_catalog.text)
WHEN u.usecreatedb THEN CAST('create database' AS
pg_catalog.text)
ELSE CAST('' AS pg_catalog.text)
END AS "Attributes"
FROM pg_catalog.pg_user u
ORDER BY 1;
access right
SELECT privilege_type FROM information_schema.table_privileges WHERE table_name = 'file_info';
Insert Data
create Table
CREATE TABLE sw_comics (
Band smallint NOT NULL,
Title VARCHAR(255),
Notes VARCHAR(255),
Owned BOOLEAN DEFAULT FALSE,
Wishlist BOOLEAN DEFAULT FALSE
);
Insert into table (no space between table name and columns)
INSERT INTO sw_comics(Band, Title, Notes, Owned, Wishlist)
VALUES
('10', 'Dark Times - Blutige Ernte', 'Checkout Kurosawas \"Yojimbo - Der Leibwaechter\" and \"Dir verborgene Festung\"', 'true', 'false'),
('28', 'Blutsband II: Boba Fett ist tot', 'kein toller Zeichenstil', 'true','false'),
('35', 'Crimson Empire II - Das Blutsgericht', 'Nom Anor tritt auf, schoener Zeichenstil','true','false');
create new table and user for that table
CREATE TABLE table_data (
first_column VARCHAR(255) NOT NULL,
some_number INT,
second_column VARCHAR(255) NOT NULL,
a_boolean BOOLEAN DEFAULT FALSE
);
CREATE USER table_user WITH PASSWORD 'user-password';
Add column to table
add column deletedBefore with timezone aware timestamp
ALTER TABLE directory_info
ALTER COLUMN deletedBefore TYPE TIMESTAMP WITH TIME ZONE;
Verify changes where made
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'directory_info' AND column_name = 'deletedbefore';