Message-ID: <1511709533.2396.5.camel@cybertec.at>
Subject: Re: Roles and security
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: nikhil raj <nikhilraj474@gmail.com>, pgsql-jobs@postgresql.org,
	pgsql-sql@postgresql.org, pgsql-admin@postgresql.com,
	pgsql-novice@postgresql.org, pgsql-general@postgresql.org
Date: Sun, 26 Nov 2017 16:18:53 +0100
In-Reply-To: 
 <CAG1ps1xNAQVEWp6vs0bKtAxkK+Ut1V+GLPHHuEQM9j_uhyWbcQ@mail.gmail.com>
References: 
	<CAG1ps1zxvGoDAahyVqs44drEyUsB-6BkSHQ=-w9MOwEAHm=YGg@mail.gmail.com>
	 <CAG1ps1yk8SaTU+=woBixi8Ec6dnMkKZ2qhF6wMsReXr5Een4Eg@mail.gmail.com>
	 <CAG1ps1xsYPa_2x6bBGvJ3890CdaTpJAa=YGtY=h8YZmKdWraxA@mail.gmail.com>
	 <CAG1ps1z9JCP4pp4b4LLjxXd3Z88hWcq0jxx_at0UqQY_O+P7Sg@mail.gmail.com>
	 <CAG1ps1wV8aNoG4YrKiQZEcX0SsWDZLkNdSe8ERLdgbKBmFOYbg@mail.gmail.com>
	 <CAG1ps1w+kRn-MaXKQs9Gafy7vci58sxxwzJH2VwRSgw=LaYQaw@mail.gmail.com>
	 <CAG1ps1wN1AGaUzL2AAgxFaPPmtS8M0sRa+j0CgUATze7HJay_w@mail.gmail.com>
	 <CAG1ps1wuLHbqfqKUgsZEbTKKju9kiqDFQvFmy8no2kAp5ib2Ag@mail.gmail.com>
	 <CAG1ps1w7rCz7XvPsk3O6vj+Ez7SzXHnCEODS9_RXxrvCcSv4GQ@mail.gmail.com>
	 <CAG1ps1zmKE=iRAA5Kg1-+r_DGLyFCEjMJB81FCBShOnr2+2Uaw@mail.gmail.com>
	 <CAG1ps1yDbq_xCwTEdZAsPG_m8pG0ecP7LTKJK_u9S=FZF5UFXw@mail.gmail.com>
	 <CAG1ps1wvt26wk6Tdd2VW-Nu6uc0od8vH8bmSoAXmbtcU3t_afg@mail.gmail.com>
	 <CAG1ps1weSWkdkJX1MEja1G1sV5qEznnPp3+BKPdD8+KWFjCuxQ@mail.gmail.com>
	 <CAG1ps1wxp-yfRwv1rGaEQG9R-YycXCMTw5d6QKa_3qxKdw3iKw@mail.gmail.com>
	 <CAG1ps1wYPZCbuhcT+z6yJuQccWRpDq0ShJPGFd6goCnTvgTcvg@mail.gmail.com>
	 <CAG1ps1wh11dgvDMjQaaHdaDd4XHcAD6kwmA=9qywfy-xdbU4gQ@mail.gmail.com>
	 <CAG1ps1zZ0-L_LUhxk__HF=DXL_7bhud4G8qFp3DrbDQAZwKyHw@mail.gmail.com>
	 <CAG1ps1xp0Hx=wL7m42VC19S=vjyo89X-8_+WzVB7qYrYUDh1Dw@mail.gmail.com>
	 <CAG1ps1zjPxeRkzh4gnLO3arTj_4AVT6d98Yt0OpzS2Suc+madA@mail.gmail.com>
	 <CAG1ps1ziTJcoPLnKbi1j2jFH2jmEc08hZFthCxza=qurSSnDzg@mail.gmail.com>
	 <CAG1ps1y=Gv4O1WpbXE7C=Y5snAY-cXafBdtX1opSAuEqkxoeSA@mail.gmail.com>
	 <CAG1ps1xw-kEqXEvf9D2zdB7fu3GuH66+wp122ZN+mMdW-eZcvw@mail.gmail.com>
	 <CAG1ps1wyzEyBu_snQf4Y9_VXtvsFZNH1=s9PCGPHzw2fxHY0GA@mail.gmail.com>
	 <CAG1ps1x+D5BBwQRKi6irMWU0qS0Yn-W70qVX0HniD6=hnZ-vqQ@mail.gmail.com>
	 <CAG1ps1xZ1vhd9oM+xTi+M6WfQtHA25sAdkRsrypvKh_QGxQyXQ@mail.gmail.com>
	 <CAG1ps1w0fZ9ykNjJhp90HqNP3NC9fotoLY_T8SXgU4qdt5MhsA@mail.gmail.com>
	 <CAG1ps1wihb0XsLVK1DNa7qTJrzazqoNudR7ncx5Go2s-zWbYhg@mail.gmail.com>
	 <CAG1ps1y_e9r0hjNF2RGBin1C5tQZrocvpPRbnpfNNw13QL5Tgw@mail.gmail.com>
	 <CAG1ps1y4bqk-iZ6GvcEorDTH5jhs5XjNbY4d8fxST_S7683YFg@mail.gmail.com>
	 <CAG1ps1yxTD84QLBw38LPh2nmj+Kn4j7F0T=-EN7tnEzuU1c6vQ@mail.gmail.com>
	 <CAG1ps1w2J2aiK5v6dotLhEnt4OH_-1rnsmDHqZnzsVwb941wLg@mail.gmail.com>
	 <CAG1ps1wEfeZMYB3BROVS+wY_r7r2esvnnzMsGGt0sc8OTB6CZA@mail.gmail.com>
	 <CAG1ps1xNAQVEWp6vs0bKtAxkK+Ut1V+GLPHHuEQM9j_uhyWbcQ@mail.gmail.com>
Organization: Cybertec
Content-Type: text/plain; charset="UTF-8"
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit
Precedence: bulk

nikhil raj wrote:
> Currently my company is shifting from MS SQL to postgres
> 
>  check the roles of user I want to give user roles only select, insert, update, execute and create database to all users Permission
> 
> 
> The users should not have these permission
>  drop database , delete table permission

If you want to allow a user (or role) CREATE DATABASE, give them the
CREATEDB privilege with ALTER ROLE.

If you mean "schema" when you say "database", give them the CREATE
privilege on the database instead.

SELECT, INSERT, UPDATE and EXECUTE are granted on individual objects,
not in general.

You can, however, use ALTER DEFUALT PRIVILEGES to automatically grant
privieges on any new object created.

Note that by default, everybody (the special role PUBLIC) has EXECUTE
privilege on all functions.

Only superusers and object owners are allowed DROP and ALTER on objects,
and there is no way to prevent that.

Yours,
Laurenz Albe