Re: [GENERAL] Access privileges /yyyy -- role that granted this privilege.
Ok thank you (and sorry i didn't read this line of documentation) 2017-02-17 15:18 GMT+01:00 Adrian Klaver : > On 02/17/2017 05:44 AM, Jean-Michel Scheiwiler wrote: > >> Hello, >> >> I delve into access privileges and I have a problem (or a >> miscomprehension) when i type \l, \dn+ or \dp with the / "role that >> granted this privilege" part. >> >> ( https://www.postgresql.org/docs/current/static/sql-grant.html ) >> >> \l for instance >> >> [postgres:~]$psql >> psql (9.6.2) >> Type "help" for help. >> >> postgres=# create role superman login superuser; >> CREATE ROLE >> postgres=# create role user01 login ; >> CREATE ROLE >> postgres=# create role user02 login ; >> CREATE ROLE >> postgres=# create database db001; >> CREATE DATABASE >> postgres=# \l db001 >> List of databases >> Name | Owner | Encoding | Collate |Ctype| Access >> privileges >> ---+--+--+-+-+-- >> - >> db001 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | >> (1 row) >> >> postgres=# grant connect on database db001 to user01 ; >> GRANT >> postgres=# \l db001 >> List of databases >> Name | Owner | Encoding | Collate |Ctype| Access >> privileges >> ---+--+--+-+-+-- >> - >> db001 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres >> + >>| | | | | >> postgres=CTc/postgres+ >>| | | | | >> user01=c/postgres >> (1 row) >> >> postgres=# \q >> [postgres:~]$psql -U superman postgres >> psql (9.6.2) >> Type "help" for help. >> >> postgres=# grant connect on database db001 to user02; >> GRANT >> postgres=# \l db001 >> List of databases >> Name | Owner | Encoding | Collate |Ctype| Access >> privileges >> ---+--+--+-+-+-- >> - >> db001 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres >> + >>| | | | | >> postgres=CTc/postgres+ >>| | | | | >> user01=c/postgres+ >>| | | | | >> user02=c/postgres >> (1 row) >> >> >> I thought i would get user02=c/superman but instead i >> get user02=c/postgres => I don't get the "role that granted this >> privilege" but i get the owner of the database. >> > > As a practical matter it does not matter as postgres and superman are both > superusers, still for an explanation of why it happens: > > https://www.postgresql.org/docs/9.6/static/sql-grant.html > > "If a superuser chooses to issue a GRANT or REVOKE command, the command is > performed as though it were issued by the owner of the affected object. In > particular, privileges granted via such a command will appear to have been > granted by the object owner. (For role membership, the membership appears > to have been granted by the containing role itself.)" > > >> The problem is the same with schemas or tables access privileges. >> >> Can you help me figure this out ? >> > > What are you trying to achieve? > > > >> Thank you in advance >> >> Jean-Michel Scheiwiler >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
[GENERAL] Access privileges /yyyy -- role that granted this privilege.
Hello, I delve into access privileges and I have a problem (or a miscomprehension) when i type \l, \dn+ or \dp with the / "role that granted this privilege" part. ( https://www.postgresql.org/docs/current/static/sql-grant.html ) \l for instance [postgres:~]$psql psql (9.6.2) Type "help" for help. postgres=# create role superman login superuser; CREATE ROLE postgres=# create role user01 login ; CREATE ROLE postgres=# create role user02 login ; CREATE ROLE postgres=# create database db001; CREATE DATABASE postgres=# \l db001 List of databases Name | Owner | Encoding | Collate |Ctype| Access privileges ---+--+--+-+-+--- db001 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | (1 row) postgres=# grant connect on database db001 to user01 ; GRANT postgres=# \l db001 List of databases Name | Owner | Encoding | Collate |Ctype| Access privileges ---+--+--+-+-+--- db001 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres + | | | | | postgres=CTc/postgres+ | | | | | user01=c/postgres (1 row) postgres=# \q [postgres:~]$psql -U superman postgres psql (9.6.2) Type "help" for help. postgres=# grant connect on database db001 to user02; GRANT postgres=# \l db001 List of databases Name | Owner | Encoding | Collate |Ctype| Access privileges ---+--+--+-+-+--- db001 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres + | | | | | postgres=CTc/postgres+ | | | | | user01=c/postgres+ | | | | | user02=c/postgres (1 row) I thought i would get user02=c/superman but instead i get user02=c/postgres => I don't get the "role that granted this privilege" but i get the owner of the database. The problem is the same with schemas or tables access privileges. Can you help me figure this out ? Thank you in advance Jean-Michel Scheiwiler
[GENERAL] Start/stop postgresql with pg_ctl or service without root access on RHEL
Hello We plan to use postgresql on RHEL 6. DBAs won't have root access but they will need to start/stop the databases. They'll be able to do so with pg_ctl as postgres. However databases should also start automatically when the server reboots and so we should use services (/etc/init.d/postgresql-9.x). When postgres is started with service (as root) and stopped with pg_ctl as postgres, the pid file in $PGDATA is deleted but not the specific pid and lock files (respectively in /var/run and /var/lock/subsys) created by the init.d script. It leads to an inconsistent state where service postgresql-9.x status throws"postgresql-9.x dead but pid file exists". So what is the best practice and solution for this situation? - ask sysadmin to give sudo /etc/init.d/postgresql-9.x to the DBAs and never use pg_ctl again? - remove the specific pid and lock files from the postgresql-9.x service script? - any other idea? Thank you in advance JM Scheiwiler
