Skip to main content

PostgreSQL Grant Privileges To a Role

Hi all,

 If you have lots of user and table/schema on your database you can not give permission per table but you can follow this steps. It's gonna be manual process but sometimes you can not give whole privileges on entire schema in your databases to users/roles. So, in that case you should generate your scripts.

 GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO your_role;
GRANT USAGE ON SCHEMA schema_name TO your_role;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA schema_name TO your_role;

 If you don't want to give all privileges and make specific for users also you can generate a sql script for whole schema for user or your all users like;

 Generate GRANT script for granting privileges whole schemas for a role;
SELECT DISTINCT 'GRANT SELECT ON ALL TABLES IN SCHEMA '|| schemaname || 'TO user_role;' FROM pg_stat_user_tables;

Generate USAGE script for whole schemas;
SELECT DISTINCT 'GRANT USAGE ON SCHEMA '|| schemaname || 'TO user_role;' FROM pg_stat_user_tables;

You may want to executing privileges on whole functions;
SELECT DISTINCT 'GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA '|| schemaname || 'TO user_role;' FROM pg_stat_user_tables;

 Or you can type script bellow for functions, it's gonna be fine.

SELECT DISTINCT 'GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA '|| schemaname ||' TO '|| rolname || ';' from pg_stat_user_tables, pg_roles;

Other way, you prefer to grant all privileges based on database, so you can use bellow script;

 GRANT ALL PRIVILEGES ON DATABASE databasename TO your_role;


 Loves,
Labels:

Comments

Post a Comment

Popular posts from this blog

pg_ctl: command not found

Hi, If you can not reach pg_ctl command in bash also you are sure this command exists in your server, check $PATH. -bash-4.2$ echo $PATH /usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin So, the directory of pg_ctl is not seem in PATH. Input directory of pg_ctl and check it then run it.
Post a Comment
Read more..

PostgreSQL Foreign Data Wrappers

Hi all, I would like to mention about generating Foreign Data Wrappers(FDW) on PostgreSQL 9.6. FDW is used for remote access to tables from external data storage. If needed to use remote table in a query, you can use FDW tables. For instance you can get a table from remote database, but there is a condition. The condition is that user should have proper privileges on FDW table. There is two extension for FDW on PostgreSQL. First one is used for accessing PostgreSQL database to PostgreSQL database, called postgres_fdw . Second one is used for accessing PostgreSQL database to different databases (SQL Server, Sysbase) called tds_fdw .  Foreign Data Wrappers feature lets you to cross-query between remote database tables. Postgres_fdw and tds_fdw has different structure. Tds_fdw usign Tabular Data Stream application layer protocol to transfer data between database server and client. Generating Tds_fdw and Postgres_fdw is similar. I share an example for generating FDW between tw...
Post a Comment
Read more..

PostgreSQL High Availability - Patroni 2

Patroni kurulumuyla ilgili oldukça fazla kaynak bulunmakta fakat kurulum ve yönetimini beraber barındıran kaynağa denk gelmedim. Bu yazıda hem Patroni kurulumu hem de kurulum sonrası yönetimiyle alakalı detaylara ulaşabilirsiniz. PostgreSQL cluster'larının yönetimi için kullanılan Patroni ile ilgili temel bilgilerin yer aldığı Autofailover üzerine hazırladığım yazı serisine aşağıdaki linklerden erişebilirsiniz. PostgreSQL ve Autofailover PostgreSQL'de Autofailover ve Patroni 1 (Giriş) PostgreSQL'de Autofailover ve Patroni 2 (Kurulum, Konfigürasyon ve Yönetim) PostgreSQL'de Autofailover ve Patroni 3 (Mevcut PostgreSQL Cluster'inin Patroni'ye Geçirilmesi) Patroni, PostgreSQL veritabanlarının kurulumundan ve konfigürasyonundan sorumludur. Yani Patroni'yi kurduğumuz sunucular aynı zamanda Patroni ile kurulmuş PostgreSQL'leri barındıracak. Üç node'lu PostgreSQL ve üç node'lu ETCD cluster'larını oluşturacağım. Kuruluma önce üç no...
Post a Comment
Read more..